Fixing Macro Errors

Macros don't always work perfectly . That is, you might make a mistake while recording the macro, or you might leave out a step. You don't need to worry about a macro that displays an error message because you can always fix those macro errors in Excel by editing, adding, and removing commands from the macro instructions.

A macro might need additional commands or actions, or you might want to delete some command or action from the macro. What if you want to make changes to existing macro commands and actions or correct errors in a macro that doesn't run properly? No problem. You can make any of these changes to a macro by editing the macro.

Looking at Macro Code

Macro instructions are written in Visual Basic, a fairly easy-to-use programming language. With the macro sheet in view onscreen, you can use Excel's editing commands to make changes to the Visual Basic instructions. You can remove macro commands, edit the specific contents of a cell in the macro worksheet, or even insert new commands into the middle of a macro. Of course, some changes require knowledge of Visual Basic. Specific commands that relate to actions that you want are described in the Microsoft Excel manual that comes with the software.

You can view macro code in the macro sheet by switching to that sheet. To open the macro sheet, choose Tools, Macro, Macros. In the Macro dialog box, select the Font_change macro. Click the Edit button.

Another way to look at macro code is to choose Tools, Macro, Visual Basic Editor, or press Alt+F11.

The Microsoft Visual Basic window appears, as shown in Figure 20.5. You should see the Visual Basic toolbar and three window panes:

• Project VBAProject

• Properties Sheet1

• Visual Basic Instructions

Figure 20.5. The Microsoft Visual Basic window.

At the far right end of the Visual Basic toolbar, notice the line and column indicator: Ln X and Col X. These indicators tell you the line and column where the insertion point is located in the active pane. To activate a pane, simply click the pane.

Editing the Macro

You can insert a command manually, remove a command, or edit a macro command on the macro sheet to make changes to the macro. You'll work with the Visual Basic Instructions pane on the right to make your changes. To get a better view of what you're doing in the Visual Basic Instructions pane, click the Maximize button in the upper-right corner of the pane. Excel enlarges the pane so that you see more macro instructions.

The To Do exercise coming up helps you edit the macro. You change the macro's font size from 22 point to 28 point.

To Do: Edit the Macro

1. Click anywhere in line 10 in the Visual Basic Instructions pane, which states Size = 22 . This step activates the pane and positions the insertion point where you want to make a change.

2. Click and drag over the number 22 to select it.

3. Type 28 .

   

   Remember, the line and column indicators on the Visual Basic toolbar can always tell you where the insertion point is in the active pane.

   
   

4. Click the Save button on the Visual Basic toolbar. This step saves the changes you made to the macro.

5. Click the Close ( x ) button in the upper-right corner of the Microsoft Visual Basic window. This step closes the window and returns you to the workbook. Now you can test the change you made to the macro.

6. Click the Detail sheet tab and click cell A1, which contains the title. Press Ctrl+Shift+F. The macro applies the 28-point Arial font to the text in the selected cell. Your macro works perfectly! You'll have to make column A wider and row 1 taller to accommodate the large entry in cell A1.

   

   When you want to delete a macro, choose Tools, Macro, Macros. Select the macro you want to get rid of. Click the Delete button and choose Yes to confirm the deletion. Excel removes the macro from the Macro name list.

   
   

Fixing a Macro with Step Mode

When a macro doesn't work, the process of trying to find the problem and fixing it is called debugging .

When you use Step Mode to debug a macro, Visual Basic displays a yellow arrow in the left border of the Visual Basic Instruction pane and highlights in yellow the macro instruction on the line it's pointing to. Read the instruction carefully to see whether it contains any errors, including typos.

To use Step Mode to debug a macro, in the Macros dialog box, select the macro you want to debug. Click Step Into. If you're already in the Visual Basic Editor, choose Debug in the Visual Basic menu bar. Then you have three Step Mode choices:

• Step Into Moves the Step Mode pointer into the instructions and executes code one statement at a time.

• Step Over Moves the Step Mode pointer into the Code window and executes code one procedure or statement at a time.

• Step Out Executes the remaining lines of a procedure in which the current execution point is located.

Use these Step Mode commands to step through the macro instructions and pinpoint the location of any errors.