Part VI: Automating Excel
- Enter a new, single-word name for the macro to replace the default Macro1
name. A good name is TimeStamp.
- Assign this macro to the shortcut key Ctrl+Shift+T by entering an uppercase T
in the Shortcut Key field.
- Make sure that This Workbook is selected in the Store Macro In field.
- Click OK to close the Record Macro dialog box.
- Enter this formula into the selected cell:
=NOW()
- With the date cell selected, click the Copy button (or press Ctrl+C) to copy the
cell to the Clipboard.
- Choose Home ➪ Clipboard ➪ Paste ➪ Values (V). This step replaces the formula
with static text so that the date and time do not update when the worksheet is
calculated.
- Press Esc to cancel Copy mode.
- Choose Developer ➪ Code ➪ Stop Recording (or click the Stop Recording button
on the status bar).
Running the macro
Activate an empty cell, and press Ctrl+Shift+T to execute the macro. There’s a pretty good
chance that the macro won’t work!
The VBA code that is recorded in this macro depends on a setting on the Advanced tab of
the Excel Options dialog box, namely, After Pressing Enter, Move Selection. If this setting
is enabled (which is the default), the recorded macro won’t work as intended because the
active cell was changed when you pressed Enter. Even if you reactivated the date cell while
recording (in step 7), the macro still fails.
Examining the macro
Activate the VBE and take a look at the recorded code. Figure 42.9 shows the recorded
macro, as displayed in the Code window.
The procedure has five statements. The first inserts the NOW() formula into the active
cell. The second statement selects cell F6—if the active cell moved to the next cell in step
7, then you had to reselect the active cell in step 8. The exact cell address depends on the
location of the active cell when the macro was recorded.
The third statement copies the cell. The fourth statement, which is displayed on two lines
(the underscore character means that the statement continues on the next line), pastes the
Clipboard contents (as a value) to the current selection. The fifth statement cancels the
dashed border around the copied cell.
