Recording a Macro

Now that you know what actions you need to perform, it's time to turn on the macro recorder and create your first macro.

Before you follow the recording steps outlined below, be sure to remove the formatting from the example worksheet. To do this, press Ctrl+A to select the entire worksheet. Then choose Edit | Clear | Formats. Select cells A1:A3 and choose Edit | Delete. In the Delete dialog box, select the Entire row option button. Then click OK. To create your first macro, follow these steps:

1. Select a single cell.

Before you record a macro, you should decide whether or not you want to record the positioning of the active cell. If you want the macro to always start in a specific location on the worksheet, turn on the macro recorder first and then select the cell you want to start in. If the location of the active cell does not matter, select a single cell first and then turn on the macro recorder.

2. Choose Tools | Macro | Record New Macro. The Record Macro dialog box appears.

Record Macro

■ ?l.xl

Macro name:

| Macrol

Shortcut key: Ctrl+|

Store macro [n:

■[This Workbook

MMÜ t

Description:

J Macro recorded 5/31/2002 by Julitta Korol

OK 1

Cancel |

When you record a new macro, you must name it. In the Record Macro dialog box you can also supply a shortcut key, the storage location, and a description for your macro.

3. Enter the name WhatsInACell for the sample macro.

Tip 1-2: Macro Names

If you forget to enter a name for the macro, Excel assigns a default name such as Macrol, Macro2, and so on. Macro names can contain letters, numbers, and the underscore character, but the first character must be a letter. For example, Reportl is a valid macro name, while IReport is not. Spaces are not allowed. If you want a space between the words, use the underscore. For example, instead of WhatsInACell, enter Whats_In_A_Cell.

4. Select This Workbook in the Store macro in list box.

Tip 1-3: Storing Macros

Excel allows you to store macros in three locations:

■ Personal Macro Workbook -

Macros stored in this location will be available each time you work with Microsoft Excel. Personal Macro Workbook is located in the XLStart folder. If this workbook doesn't already exist, Excel creates it the first time you select this option. New Workbook - Excel will place the macro in a new workbook. This Workbook - The macro will be stored in the workbook you are currently using.

5. Enter the following text in the Description box: Indicates the contents of the underlying cells: text, numbers, formulas.

6. Choose OK to close the Record Macro dialog box and begin recording. The Stop Recording toolbar appears. The status bar at the bottom of the Excel application window displays "Ready Recording."

Figure 1-3:

The Stop Recording toolbar has buttons that allow you to stop the macro recorder or indicate how Excel should handle cell addressing when recording.

Figure 1-3:

The Stop Recording toolbar has buttons that allow you to stop the macro recorder or indicate how Excel should handle cell addressing when recording.

Tip 1-4: Cell Addressing in Macros: Relative or Absolute?

Absolute - If you want your macro to execute the recorded action in a specific cell, no matter what cell is selected during the execution of the macro, use absolute cell addressing. Absolute cell references have the following form: $A$1, $C$5, etc. By default, the Excel macro recorder uses absolute references. Before you begin to record, make sure the Stop Recording toolbar's second button is not depressed. When you point the mouse to this button, its tool tip should read "Relative reference."

Relative - If you want your macro to perform the action in any cell, turn on the relative references. Relative cell references have the following form: A1, C5, etc. Before you begin to record, make sure the Stop Recording toolbar's second button is depressed. Bear in mind, however, that Excel will continue recording using the relative cell references until you exit Microsoft Excel or click the Relative Reference button again.

During the process of recording your macro, you may use both methods of cell addressing. For example, you may select a specific cell (e.g., $A$4), perform an action, then choose another cell relative to the selected cell (e.g., C9, which is located five rows down and two columns to the right of the currently active cell $A$4).

Relative references automatically adjust when you copy them, and absolute references don't.

7. Perform the actions you have tried out manually in the previous section (see "Planning a Macro").

As you record your macro, only the actions finalized by pressing Enter or clicking OK are recorded. If you press the Esc key or click Cancel before completing the entry, the macro recorder does not record that action.

8. When you have performed all the actions, click the Stop Recording button on the Stop Recording toolbar or choose Tools | Macro | Stop Recording.

0 0

Post a comment