Part VI: Programming Excel with VBA
806
Another example
This example demonstrates how to record a time-stamp macro that inserts the current date and
time into the active cell.To create the macro, follow these steps:- Activate an empty cell.
- Choose Developer ➪ Code ➪ Record Macro. Excel displays the Record Macro
dialog box. - 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. - 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 data and time do not update when the worksheet is calculated. - Press Esc(ape) 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 upon a setting on the Advanced tab of the
Excel Options dialog box: namely, after Pressing Enter, Move Selection. If this setting is enabled,
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 VB Editor and take a look at the recorded code. Figure 39.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 C4 — an action I performed because the cell pointer moved to the
next cell after I entered the formula.