Macro Recording Basics - dummies

By Michael Alexander

To start recording your first macro in Excel, you need to find Macro Recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden — you may not see it on your version of Excel at first. If you plan to work with VBA macros, you’ll want to make sure that the Developer tab is visible. To display this tab:

  1. Choose File→Excel Options.

  2. In the Excel Options dialog box, select Customize Ribbon.

  3. In the list box on the right, click to place a check mark next to Developer.

  4. Click OK to return to Excel.

Now that the Developer tab appears in the Excel ribbon, you can start Macro Recorder. Select Record Macro from the Developer tab. The Record Macro dialog box appears.

The Record Macro dialog box.
The Record Macro dialog box.

Here are the four parts of the Record Macro dialog box:

  • Macro name: Excel gives a default name to your macro, such as Macro1, but you should give your macro a name more descriptive of what it does. For example, you might name a macro that formats a generic table FormatTable.

  • Shortcut key: Every macro needs an event, or something to happen, for it to run. This event can be a button press, a workbook opening, or in this example, a keystroke combination. When you assign a shortcut key to your macro, entering that of keys triggers your macro to run. Note that you don’t need a shortcut key to trigger a macro, so this field is optional.

  • Store macro in: The This Workbook option is the default. Storing your macro in This Workbook simply means that the macro is stored along with the active Excel file. The next time you open that particular workbook, the macro is available to run. Similarly, if you send the workbook to another user, that user can run the macro as well (provided the macro security is properly set by your user).

  • Description: This optional field can come in handy if you have numerous macros in a spreadsheet or you need to give a user a more detailed description about what the macro does.

With the Record Macro dialog box open, follow these steps to create a simple macro that enters your name in a worksheet cell:

  1. In the User Name field, enter a new single-word name for the macro to replace the default Macro1 name.

    A good name for this example is MyName.

  2. In the Shortcut Key field, enter an uppercase N.

    You’ve just assigned this macro to the shortcut key Ctrl+Shift+N.

  3. Click OK to close the Record Macro dialog box and begin recording your actions.

  4. Select a cell in your Excel spreadsheet, type your name in the selected cell, and then press Enter.

  5. Choose Developer→Code→Stop Recording (or click the Stop Recording button in the status bar).