Troubleshooting Recorded Macros

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.

Stepping Through and Editing Recorded Macros

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):

  1. 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.

  2. 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.

    Figure 38.3. When you step into a macro for trouble shooting, the line that's about to be run appears highlighted.

→ For details on the programming environment, see "Using the VBA Editor".

  1. 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.

    Figure 38.4. By default, recorded VBA/Word macros show up as subroutines (beginning with the word Sub) in the Normal project's NewMacros module.

  2. 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.

  3. Press F8 again, and watch carefully as the macro performs the next actions; repeat this process, one command at a time.

  4. When you run the End Sub line, the VBA Editor stops. You can start all over again, if you like, beginning with step 4.

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.

Common Recorded Macro Mistakes

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.

Table 38.1. Common Macro Problems
Macro Error Troubleshooting Suggestion
A key combination doesn't work the way you thought it would. Many navigation keys have easy-to-understand descriptions (select next word, or move down one paragraph), but they behave oddly in unusual circumstances—inside a Word table, cell, or at the end of a document, for example. Find a different key combination that accomplishes the same task in a slightly different way.
Formatting commands overwrite existing formatting. When you apply formatting using the Format menu, the application might replace all formatting with the new format. If you want to add the new formatting—for example, boldfacing a word while leaving intact other attributes, such as italic—use shortcut keys to apply formatting (Ctrl+B to apply bold).
A repeating macro doesn't do the entire job. Recorded macros rarely incorporate the kind of repetition you anticipate. To create a macro that loops properly, you almost always have to edit it manually. (One exception—Replace All will loop through an entire document, worksheet, or presentation.)

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.

Testing and Bullet-Proofing Macros

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.

Figure 38.5. Compare this code to the contents of Figure 38.4; two small changes had to be made before the recorded macro would work properly.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.16.130.201