Recording Basics

You take the following basic steps when recording a macro. I describe these steps in more detail later in this chapter.

1. Determine what you want the macro to do.

2. Get things set up properly.

This step determines how well your macro works.

3. Determine whether you want cell references in your macro to be relative or absolute.

4. Choose ToolsOMacroORecord New Macro. Excel displays its Record Macro dialog box.

5. Enter a name, shortcut key, macro location, and description. Each of these items — with the exception of the name — is optional.

6. Click OK in the Record Macro dialog box.

Excel automatically inserts a VBA module. From this point, Excel converts your actions into VBA code. It also displays a miniature floating toolbar, which contains two toolbar buttons: Stop Recording and Relative Reference.

7. Perform the actions you want recorded using the mouse or the keyboard.

8. After you're finished, click the Stop Recording button on the miniature toolbar (or choose ToolsOMacroOStop Recording).

Excel stops recording your actions.

9. Test the macro to make sure it works correctly.

The macro recorder is best suited for simple, straightforward macros. For example, you might want a macro that applies formatting to a selected range of cells or that sets up row and column headings for a new worksheet.

The macro recorder is for Sub procedures only. You can't use the macro recorder to create Function procedures.

You may also find the macro recorder helpful for developing more-complex macros. Often, I record some actions and then copy the recorded code into another, more complex macro. In most cases, you need to edit the recorded code and add some new VBA statements.

The macro recorder cannot generate code for any of the following tasks, which I describe later in the book:

^ Performing any type of repetitive looping

^ Performing any type of conditional actions (using an If-Then statement) ^ Assigning values to variables ^ Specifying data types ^ Displaying pop-up messages ^ Displaying custom dialog boxes

The macro recorder's limited capability certainly doesn't diminish its importance. I make this point throughout the book: Recording your actions is perhaps the best way to learn VBA. When in doubt, try recording. Although the result may not be exactly what you want, viewing the recorded code might steer you in the right direction.

0 0

Post a comment