This is the third article in a series about creating macros in Microsoft Excel and/or Microsoft Word. The first article, What is a macro? Defined what a macro is and how it can save you from having to do hours of tedious, repetitive work. The second article, Plan your macro of else… discussed the importance of planning. It also introduced the five steps needed to properly create a macro. Those five steps are:

  1. Know what you want
  2. Know how to do it without a macro
  3. Record a macro
  4. Test the recorded macro and edit as needed
  5. Share the macro with others

This article discusses step three, how to record the macro.

Display the Developer Tab

To record a macro in Microsoft Excel and/or Word you will need the ribbon’s developer tab. By default, this is hidden. To display it do the following:

First, on the ribbon click the File button.
Second, from the menu on the left, choose Options.
Third, from the menu on the left, choose Customize Ribbon.
Fourth, in the Main Tabs list, check the Developer box.
Fifth, click OK.

The Developer Tab, as shown below, should now be visible on the ribbon.

Excel’s Developer Tab

Word’s developer tab

Turn on the recorder

Now that you have the developer tab, you can click the Record Macro button to begin your recording.

Name the macro

In the Record Macro dialog box, enter a name for the macro. The macro name should be descriptive, and the name must start with a letter or an underscore. The macro name however cannot have spaces, nor dashes, but it can have underscores.

Additional options in Excel

When recording a macro in Excel you have a few options:

First, you can assign a shortcut key, by typing in a digit, character, or upper- or lower-case letter in the box next to control. This will allow you to execute the macro by pressing the control key plus the selected digit, character, or letter.

Second, you can choose where to store the macro.

Choosing Personal Macro Workbook will store the macro in a hidden workbook that belongs to this computer.
Choosing This Workbook will store the macro’s code inside the Excel file in which you are working.
Choosing New Workbook will cause Excel to create a new Excel workbook to store the macro’s code.

If you choose This Workbook or New Workbook, then you will be able to run the macro on any machine in which the selected workbook is open. But, when the selected workbook is closed, then the machine cannot run the macro.

If you choose Personal Macro Workbook, then the macro will always be available for running on this machine, but it will not be available for running on any other machine.

Third, you can enter an optional description to document information about the macro and its use.

Additional options in Word

Microsoft Word also has a few options for recording macros.

First, you can assign the macro to a button on the quick access toolbar.

Second, you can assign a macro to a keyboard shortcut.

Third, you can choose where to store the macro.
Choosing All Documents (Normal.dotm) will place the macro code in a Microsoft Word template that is always available on this PC but will not be available on other machines.

The other option is to store the macro in the current file. This will allow you to run the macro on any machine where this file is open.

Fourth, you can enter an optional description to document information about the macro and its use.

Record the macro

After filling in the macro name and the various optional parameters, click OK. This turns on the recorder. Contrary to popular belief the recorder will not record what you do. Instead, it will record the results of what you do. To understand what that means, consider the following questions and answers:

Q: If I start the record and then do nothing for 5 minutes because I got interrupted by a phone call, will the recorder record idle time for 5 minutes?

A: No. It only records the results of what you do, and since you did nothing, on the computer, the recorder will record nothing.

Q: If I press Ctrl C to copy some data, will that be different than if I copy the data by clicking the copy button on the ribbon?

A: No, it will not be different. The recorder does not record how you performed an action, it just records the end results of that action.

Q: Suppose I record a macro where I click on cell A1, and then I realize I meant A2, so I click on A2. Does the recorder record that I went to both cells?

A: Yes, it will record that you went to both cells. The recorder, for better or worse, records the results of everything you do. So even though you did not do anything in cell A1, the recorder still recognizes that you did go to cell A1. End results: it records one action of select A1, and it records a second action of selecting A2.

Stop the recording

Once you have finished the actions that need recording, click the Stop Recording button on the developer tab.

What’s next?

The next step is to test and edit your macro, which is step four in our five-step process. We will cover that step in the next article in this series: Testing Macros.

Creating macros for Microsoft Office products like Excel and Word can be quite involved. That is why TechMentors provides both training and consulting services. If you would like to get some help creating or editing your macros, or if you would like to take a class, please contact us for more information.