1.5 Recording a New Macro

Before you can enhance a macro with Visual Basic for Applications, you must first record a macro to enhance. This section shows what you need to know, in general, to record a macro. A later section, "Putting it Together: Recording Your First Macro," takes you through a step-by-step example of recording a macro in Excel.

Typically, recording a macro involves four major steps:

1. Set up the starting conditions for the macro. Setting up the starting conditions for a macro means establishing the same conditions in your work environment that you expect to exist at the time you play back the recorded macro.

2. Start the Macro Recorder and name the macro. At the same time you start the Macro Recorder, you must give your macro a name and select where the recorded macro is to be stored. You can optionally choose to assign a shortcut key command to run the macro.

3. Perform the actions that you want recorded for later use. You can record into a macro any action that you can perform by using the keyboard or mouse, including executing previously recorded macros. The specific actions that you perform depend on the task that you want to record.

4. Stop the Macro Recorder. When you stop the Macro Recorder, Excel stops recording your actions. After you stop the Macro Recorder, the new recorded macro is immediately available for use.

The following sections describe the first two general steps for recording a macro in greater detail.

Set Up the Macro's Starting Conditions

Before recording any macro, you must set up the conditions under which you will later run the macro. (Running or executing a macro means playing back the recorded instructions in the macro.)

Suppose, for example, you want to create a macro that applies a particular font, font size, and font color to any selected cells in an Excel worksheet. The starting conditions for this macro would be an open worksheet with a selected cell or range of cells.

You need to set up starting conditions for a macro before you start the Macro Recorder because the Macro Recorder records all the actions you perform. If you start the Macro Recorder and then open a workbook, display a worksheet, and then select cells, those actions become part of the resulting recorded macro. Your completed macro is then overly specific: it always opens the same workbook and formats the same cells on the same worksheet. To create a general-purpose text-formatting macro that you can use for any selected cell or range of cells, you should start the Macro Recorder after opening a workbook, selecting a worksheet, and selecting the cells you want to format.

After you have established the starting conditions for your macro, you're ready to start the Macro Recorder.

Start the Macro Recorder and Select Options

To start recording a macro in Excel, display the Developer tab, and then click Record Macro. Excel displays the Record Macro dialog. You use the Record Macro dialog to give your new macro a name, and to select where you want Excel to store the new macro. You must specify a name for the macro and a location in which to store the macro before you can actually begin recording a macro. Excel's Record Macro dialog also allows you to optionally assign a shortcut key to run the new macro. You will learn more about this option in the specific step-by-step instructions in the next section.

Excel's Record Macro dialog contains the following four controls (the actual dialog box is shown in Figure 1.3):

• Macro name text box. The first Record Macro dialog option to fill in is the Macro Name. By default, VBA selects a macro name consisting of the word Macro followed by a number corresponding to the number of macros you've recorded in this work session. You should enter a name for the macro that conveys some meaning about what the macro does. For example, if you record a macro to generate a chart from sales data in a worksheet, you might enter the name MakeSalesChart in the Macro Name text box.

• Shortcut key text box. Use this text box to optionally assign a shortcut keystroke to execute your new macro. Enter the letter or symbol of the keyboard key you want to use as the shortcut command into this text box. Use this option only if you're certain that you'll use the macro you are about to record with great frequency. All Excel macro shortcut keys are the combination of the keystroke you enter plus the Ctrl key – if you type a in the Shortcut key text box, the actual shortcut keystroke is Ctrl+a. If you type A as the shortcut key, the resulting shortcut keystroke is Ctrl+Shift+A.

• Store Macro In drop-down list. This drop-down list lets you choose where the recorded macro should be stored. The available choices are: Personal Macro Workbook, New Workbook, and This Workbook.

  If you choose Personal Macro Workbook, Excel stores the new recorded macro in a special workbook, named Personal.xlsb, which is automatically loaded whenever Excel starts. Use the Personal Macro Workbook choice if you want your new macro to be available at all times.

  If you choose This Workbook, Excel stores the new recorded macro in the currently active workbook. Use the This Workbook choice if you want your new macro to be available only when the current workbook is open.

  Choose New Workbook to cause Excel to create a new workbook in which to store the macro – the workbook that was active when you started the Macro Recorder remains the active workbook; any actions you record are performed in that workbook and not in the new workbook created to store the macro.

  Regardless of which workbook you choose to store the new recorded macro in, the macro is attached to the workbook as a VBA module. Modules are described in more detail in the next chapter.

• Description text box. The information in the Description text box isn't directly used by the macro. The Description text box is just a place to keep some notes and comments about what the macro does. For each macro you record, you should add comments about what the macro does, and what its purpose is.