VBA Programming Series...6

Using the macro recorder

Another way you can get code into a VBA module is by recording your actions, using the Excel macro recorder. If you worked through the hands-on exercise in Chapter 2, you already have some experience with this technique.

By the way, there is absolutely no way you can record the GuessName pro-cedure shown in the preceding section. You can record only things that you can do directly in Excel. Displaying a message box is not in Excel’s normal repertoire. (It’s a VBA thing.) The macro recorder is useful, but in many cases, you’ll probably need to enter at least some code manually.

Here’s a step-by-step example that shows you how to record a macro that turns off the cell gridlines in a worksheet. If you want to try this example, start with a new, blank workbook and follow these steps:

1. Activate a worksheet in the workbook.

Any worksheet will do. If the worksheet is not displaying gridlines, add a new worksheet that does. You need to start with a worksheet that has gridlines.

2. Choose Developer➪Code➪Record Macro. Or you can click the icon with a small red dot in the left side of the status bar.

Excel displays its Record Macro dialog box.

3. In the Record Macro dialog box, name the macro Gridlines, and use Ctrl+Shift+G for the shortcut key.

4. Click OK to start recording.

Excel automatically inserts a new VBA module into the project that cor-responds to the active workbook. From this point on, Excel converts your actions into VBA code. While recording, the icon in the status bar turns into a small blue square. This is a reminder that the macro recorder is running. You can also click that blue square to stop the macro recorder.

5. Choose View➪Show - Gridlines.

The gridlines in the worksheet disappear.

6. Choose Developer➪Code➪Stop Recording. Or click the Stop

Recording button in the status bar (the blue square). Excel stops recording your actions.

To view this newly recorded macro, press Alt+F11 to activate the VBE. Locate the workbook’s name in the Project window. You see that the project has a new module listed. The name of the module depends on whether you had any other modules in the workbook when you started recording the macro. If you didn’t, the module will be named Module1. You can double-click the module to view the Code window for the module.

Here’s the code generated by your actions:

Sub Gridlines()

‘

‘ Gridlines Macro

‘

‘ Keyboard Shortcut: Ctrl+Shift+G

‘

ActiveWindow.DisplayGridlines = False End Sub

To try out this macro, activate a worksheet that has gridlines displayed and then press the shortcut key that you assigned in Step 3: Ctrl+Shift+G.

If you didn’t assign a shortcut key to the macro, don’t worry. Here’s how to display a list of all macros available and run the one you want.

1. Choose Developer➪Code➪Macros.

Keyboard fans can press Alt+F8. Either of these methods displays a dialog box that lists all the available macros.

2. Select the macro in the list (in this case, Gridlines).

3. Click the Run button.

Excel executes the macro, and the gridlines magically disappear.

Of course, you can execute any number of commands and perform any number of actions while the macro recorder is running. Excel dutifully trans-lates your mouse actions and keystrokes to VBA code. It works similarly to a tape recorder, but Excel never runs out of tape.

This recorded macro isn’t really all that useful. After all, it’s easy enough to turn off gridlines without a macro. It would be more useful if it would toggle gridlines on and off. To make this change, activate the module and change the statement to this:

ActiveWindow.DisplayGridlines = _

Not ActiveWindow.DisplayGridlines

This modification makes the macro serve as a toggle. If gridlines are dis-played, the macro turns them off. If gridlines are not displayed, the macro turns them on. Oops, I’m getting ahead of myself — sorry, but I couldn’t resist that simple enhancement. By the way, this is another example of a macro that can’t be recorded. You can record a macro to turn gridlines on or turn them off — but you can’t record one that will toggle the gridlines.

Copying VBA code

The final method for getting code into a VBA module is to copy it from another module or from some other place (such as a Web site). For example, a Sub

or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal Clipboard copy-and-paste procedures (I’m rather fond of the keyboard shortcuts, Ctrl+C to copy and Ctrl+V to paste). After pasting it into a VBA module, you can modify the code if necessary.

You’ll also find lots of VBA code examples on the Web. If you’d like to try them, select the code in your browser and press Ctrl+C to copy it. Then, acti-vate a module and press Ctrl+V to paste it.