Recording Macro in Excel – Learn with an Example

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.

Recording the Macro

To create the Macro, follow these steps:

  1. Activate an empty cell.
  2. Choose Developer ➪ Code ➪ Record Macro. The Record Macro dialog box will appear.
  3. 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.
  4. 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.
  5. Click OK, the Record Macro dialog box will close.
  6. Now, enter this formula into the selected cell: =NOW()
  7. With the date cell selected, click the Copy button (or press Ctrl+C) to copy the cell to the Clipboard.
  8. 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.
  9. Press Esc to cancel Copy mode.
  10. Choose Developer ➪ Code ➪ Stop Recording to stop recording your macro. Or, you can click the Stop Recording button on the status bar.

Read More: Macro recording in Excel: Absolute vs Relative

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.

An example of recording a macro in Excel

This TimeStamp procedure is generated by the Excel macro recorder.

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 the 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.

Read More: How to create VBA Macros in Excel using Macro Recorder

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.

An example of recording a macro in Excel

Use Relative References control in Code group in Developer tab.

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.

An example of recording a macro in Excel

TimeStamp macro now works properly.

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 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:

Time-Stamp.xlsm


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment
  1. Reply
    Tanvi April 1, 2017 at 2:32 AM

    Your lessons are amazing.
    I am learning to record macros and always used to wonder what the recorded jargon’s meant.
    Reading through your lessons made my life easy.

    Thanks a loads for post such wonderful lesson.

    Leave a reply