In this example, we shall demonstrate how to record a time-stamp macro that inserts the current date and time into the active cell.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
Table of Contents
Recording the Macro
To create the Macro, follow these steps:
- Activate an empty cell.
- Choose Developer ➪ Code ➪ Record Macro. The Record Macro dialog box will appear.
- Enter a unique, single-word (no space between letters or numbers) name for the macro, to replace the default Macro1 name. We have named our macro TimeStamp.
- We’ve entered an uppercase T in the Shortcut Key field of the Record Macro dialog box to assign this macro to a shortcut key Ctrl+Shift+T.
- Click OK, the Record Macro dialog box will close.
- Now, 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 will replace the formula (NOW()) with static text so that the date and time do not update automatically. Worksheet shows refresh data when you do something in any cell and press Enter.
- Press Esc to cancel Copy mode.
- Choose Developer ➪ Code ➪ Stop Recording to stop recording your macro. Or, you can 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. It is almost sure that the macro will not work.
The VBA code that is recorded in this macro depends upon a setting on the Advanced tab of the Excel Options dialog box. The option is: After Pressing Enter, Move Selection. By default, this setting is enabled. When this setting is enabled, the recorded macro won’t work as intended because the active cell has been changed when you have pressed Enter.
Examining the macro
Activate the VB Editor (press ALT + F11) and take a look at the recorded code. The following figure shows the recorded macro displayed in the Code window.
This procedure has five statements:
- The first statement inserts the NOW() formula into the active cell.
- The second statement selects cell A1— an action I performed. When I pressed Enter after inserting the formula in cell A1, the cell pointer moved to cell A2. I reselected cell A1 using mouse pointer (or you can use navigation keys ).
- The third statement copies the cell.
- The fourth statement is displayed on two lines. The underscore character means that the statement continues on the next line. This statement pastes the Clipboard contents (as a value) to the current selection.
- The fifth statement cancels the moving border around the selected range.
From analyzing this code, we find a problem. The problem is that the macro is hard-coded to select cell A1. If you execute the macro when a different cell is active, at first the code selects cell A1 and then copies the cell. This is not what we intended, and it causes the macro to fail.
Note: You’ll also notice that the macro recorded some actions that you didn’t make. For example, see this code line: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False. It has specified several options for the PasteSpecial operation. These options are just by-products of the method that Excel uses to translate actions into code while recording a macro.
Rerecording the Macro
You can fix the macro in several ways. If you’re an expert in VBA coding, you can edit the code so it works properly. Or you can rerecord the macro using relative references.
You’ve to delete the existing TimeStamp procedure and rerecord it. Before you start your recording again, click the Use Relative References command in the Code group of the Developer tab. This control is a toggle. By default, this control is turned off.
The following figure shows the new macro, recorded with relative references in effect.
Testing the Macro
When an Excel workbook is active, activate a worksheet. You can run this macro in the workbook that contains the VBA module. You can also open an existing workbook or create a new one and run this macro. It’s really awesome, right? Select a cell of the worksheet and press Ctrl+Shift+T. The macro will immediately enter the current date and time into the cell. You may need to widen the column to see the date and time.
If the column is not widened automatically, just add this statement to the end of the macro (before the End Sub statement): ActiveCell.EntireColumn.AutoFit
Happy Excelling 🙂
Download Working File
Download the working file from the link below: