Macros rarely work right the first time. Recorded macros, in particular, frequently require some tweaking before they work as intended. If the macro you recorded doesn't work, re-record it and see whether you can use a different method for accomplishing the same result. Edit a recorded macro only when it works most of the time, but occasionally fails to work the way you expect, or triggers an error message.
Fortunately, Office makes it relatively easy to edit a recorded macro. It even supports you in your bug-extermination efforts by allowing you to run the macro program one line at a time, and see what the effect of each command might be. Here's how to use the VBA Editor to step through a macro recorded in Word (the steps in Excel, PowerPoint, Outlook, Publisher, and FrontPage are virtually identical):
Create a new document or open an existing document and position the insertion point as necessary. For example, to test a macro that italicizes the first word in a paragraph, be sure to click inside a paragraph in the current document.
Choose Tools, Macro, Macros. Select the name of the macro you want to troubleshoot, and click Step Into. The VBA Editor opens, with your macro visible in the right pane (see Figure 38.3). You'll see a large yellow arrow appear to the left of the Sub line, and the Sub line will be highlighted.
→ For details on the programming environment, see "Using the VBA Editor".
Arrange the windows on your desktop so you can see both the application (in this case, Word) and the VBA Editor at the same time (see Figure 38.4). Click the window holding the VBA Editor.
To begin executing the VBA code one step at a time, press F8, or choose Debug, Step Into. The first line of the macro—the Sub line—executes.
Press F8 again, and watch carefully as the macro performs the next actions; repeat this process, one command at a time.
Frequently, you'll be able to identify the location of the problem (or problems) in a macro by stepping through it in this way. Although the solution might not be at all clear—there are lots of VBA commands, and each one behaves in a different way—being able to narrow the problem down to a line or two can make a huge difference.
After you isolate the line that you suspect is causing the problem, position the insertion point within that line and press F1. That action brings up context-sensitive VBA Help, which might present a possible solution.
Follow the same procedures to step through VBA/Excel and VBA/PowerPoint macros; you'll find recorded macros in the current workbook or presentation, in a module called Module1. In Access, open the database that contains the VBA program, select the Modules pane, and double-click the name of the VBA program. Press F8 to step through the macro.
When a recorded macro doesn't work as you expect, chances are the problem is one of several common errors. Table 38.1 lists common mistakes and suggested troubleshooting steps.
In addition, any number of unusual circumstances can trigger errors in recorded macros. For example, if you search for the word "widget" in a document where that word is in a footer and not in the body of the document, the search will succeed. When you record that action in a VBA/Word macro, everything appears to work just fine. But when you play back the recorded macro in the same document, Word won't find the word you're looking for no matter how many times you run it—in fact, it will trigger a Run Time Error. The recorded version of the Find operation works differently from the interactive version when it comes to footers.
Will your recorded macro work properly every time you run it? Frankly, there's no way to know for sure—VBA macros hardly fall into the category of "provably correct" computer programs—but you can improve the odds of a macro working correctly by employing two time-honored testing techniques:
Trace through the logic. In most cases, that means stepping through the macro, as explained earlier in this section. Watch for behavior or settings that you don't understand.
Test it in a wide variety of circumstances. Try to think of odd situations that might make the macro fail, and then see whether it does. Enlist the aid of fellow workers to test a macro, if possible, because testers will think of situations that just don't occur to you.
For example, the ItalicizeFirstWord macro example (in the "Recording a Macro" section earlier in this chapter) should italicize the first word in the current paragraph, but it doesn't. Instead, when the insertion point is at the beginning of a paragraph, this macro italicizes the first word of the preceding paragraph. Running through the macro a step at a time reveals that the culprit is the MoveUp command; when you point to that command and press F1, the context-sensitive help suggests several examples. The first example contains the solution to the problem: It shows how you have to MoveRight once before performing a MoveUp, to stay in the original paragraph.
The recorded ItalicizeFirstWord macro contains a second problem as well. When you run the macro, and then leave the insertion point in the same paragraph and run the macro again, it removes the italic formatting from the first word. Stepping through the macro again lets us see the problem: The Selection.Font.Italic line toggles the italic attribute on and off. According to the Help file, the Italic property "can be set to True, False, or wdToggle." Changing the value from wdToggle to True causes it to work properly.
When you modify the ItalicizeFirstWord macro so that it looks like the one in Figure 38.5, you'll find that both problems have disappeared.
3.142.250.203