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 49.5. You should see the Visual Basic toolbar and three window panes:

• Project VBA Project

• Properties Module1

• Visual Basic Instructions

Figure 49.5. The Microsoft Visual Basic window.

At the far right end of the Visual Basic toolbar, notice the line and column indicator: Ln X, 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 following steps teach you how to edit the macro. You change the macro's font size from 22 point to 28 point.

1. Click anywhere in line 10 in the Visual Basic Instructions pane, where it 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 .

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 F1, 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!