Recording and Playing Back Macros

The macro recorder built into Excel enables you to capture command sequences from the time you turn on the recorder until the moment you turn it off. When you turn on the macro recorder, it records all your actions in the active worksheet or chart sheet as you make them.

Note that the macro recorder doesn't record the actual keystrokes or mouse actions that you take to accomplish an action, only the VBA code required to perform the action itself. This means that, if you make mistakes while following a command sequence, all the actions you take to rectify them won't be recorded as part of the macro. For example, if you make a typing error and then edit it while the macro recorder is on, only the corrected entry is recorded as part of the macro's VBA instructions.

Whenever you record a macro, Excel adds a special module sheet to the current Excel workbook that contains the VBA code for playing back the sequence. To open the module sheet in Visual Basic Editor to review or edit the macro's VBA commands, choose Tools → Macro → Visual Basic Editor or simply press Alt+F11.

You can store the macros you record as part of the current workbook, in a new workbook, or in a special, globally available Personal Macro Workbook named personal.xls. When you record a macro in your Personal Macro Workbook, you can run that macro from any workbook that you have open. (That's because the personal.xls workbook is secretly opened whenever you launch Excel, and although it's immediately hidden, the macros it contains are always available.) When you record macros as part of the current workbook or a new workbook, you can run those macros only when the workbook in which they were recorded is open in Excel.

When you create a macro with the macro recorder, you decide not only the workbook in which to store the macro but also what name and shortcut keystrokes to assign it. When creating a name for your macro, use the same guidelines as when you assign a standard range name to a cell range in your worksheet. When assigning a shortcut keystroke to run the macro, you can assign the Ctrl key plus a lowercase letter, as in Ctrl+Q, or the Ctrl key plus an uppercase letter (the equivalent of Ctrl+Shift), as in Ctrl+Shift+Q. You can't, however, assign the Ctrl key plus a punctuation or number key (such as Ctrl+1 or Ctrl+/) to your macro.