VBA Recording Options in Excel 2016 - dummies

By John Walkenbach

When recording your actions to create VBA code, you have several options. Recall that the Developer  →  Code   →  Record Macro command displays the Record Macro dialog box before recording begins.

The Record Macro dialog box provides several options.
The Record Macro dialog box provides several options.

The Record Macro dialog box lets you specify a few aspects of your macro.

Macro name

You can enter a name for the Sub procedure that you are recording. By default, Excel uses the names Macro1, Macro2, and so on for each macro you record. Most programmers usually just accept the default name. If the macro works correctly, and you want to save it, you can give it a more descriptive name later by editing the recorded code in the VBE. You, however, may prefer to name the macro up front; the choice is yours.

Shortcut key

The Shortcut key option lets you execute the macro by pressing a shortcut key combination. For example, if you enter w (lowercase), you can execute the macro by pressing Ctrl+W. If you enter W (uppercase), the macro comes alive when you press Ctrl+Shift+W.

You can add or change a shortcut key at any time, so there’s no real reason to set this option when recording a macro.

Store Macro In option

The Store Macro In option tells Excel where to store the macro that it is recording. By default, Excel puts the recorded macro in a module in the active workbook. If you prefer, you can record it in a new workbook (Excel opens a blank workbook) or in your Personal Macro Workbook.

Your Personal Macro Workbook is a hidden workbook that opens automatically when Excel starts. This is a good place to store macros that you’ll use with multiple workbooks. The Personal Macro Workbook is named PERSONAL.XLSB. This file doesn’t exist until you specify it as the location for a recorded macro. If you’ve made any changes to this file, Excel prompts you to save it when you exit.


If you’d like to add some descriptive comments to your macro, use the Description box. You can put anything you like here, or nothing at all. As far as many programmers are concerned, the Description option is a waste of time because it’s just as easy to add comments to the recorded macro in the VBE.