Part VI: Automating Excel
FIGURE 42.10
This TimeStamp macro works correctly.
Note that the second line now says ActiveCell.Select instead of using a specific cell
address. This solves the problem of copying and pasting the wrong cell, but why would it
select a cell that’s already selected? It’s just one of the curiosities of recording macros. As
you select cells, such as when you press Enter after keying a formula, the recorder dutifully
registers every selection. In this example, when you pressed Enter in step 7, the recorder
recorded this line:
ActiveCell.Offset(1, 0).Range("A1").Select
Since you didn’t do anything while in that cell, however, it didn’t save it. When you rese-
lected the cell with the date in it, it replaced the line above with the one you see in the
macro.
Testing the macro
When Excel is active, activate a worksheet. (The worksheet can be in the workbook that
contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+T.
The macro immediately enters the current date and time into the cell. You may need to
widen the column to see the date and time.
When the result of a macro requires additional manual intervention, it’s a sign that the
macro could be improved. To widen the column automatically, just add this statement to
the end of the macro (before the End Sub statement):
ActiveCell.EntireColumn.AutoFit
