Using VBA to Save Time on Document Production and Troubleshooting | 2007 MicrosoftВ® Office System Inside Out (Bpg-Inside Out)

Once you understand the elements addressed in the preceding section of this chapter, you’re ready to start using VBA. As mentioned earlier in this chapter, find a file named Sample Macros.dotm in the practice files you can find on this book’s CD. That file contains two modules. One of those modules (named SampleMacros) includes a few of my favorite simple document production and troubleshooting macros. The other module (named AutomateUserForms) includes a couple of basic dialog boxes with related automation. (Note that dialog boxes are known in VBA as UserForms and are introduced later in this chapter.) Check out these macros and UserForms as examples, or use them as jumping off points for creating your own automation.

Before you go to it, however, there is one important tool in the Visual Basic Editor that can sometimes save you even more time than writing a macro. As mentioned earlier, when you just need to execute a single line of code, you don’t need to write a macro at all-you just need the Immediate window.

Using the Immediate Window

To open the Immediate window in the Visual Basic Editor, press Ctrl+G. Note that this is not a toggle command. So, to close the Immediate window, click the X at the top-right corner of the window.

To execute a command in the Immediate window, type the command and then press Enter. Note that, because code executed from this window can only be one line long, you can press Enter from anywhere in the line, and it will not push text to a new line.

I use the Immediate window frequently for a few different purposes. Take a look at some examples.

Use the Immediate window to get information about your document when troubleshooting problems, or to interact with your document in ways you can’t from the user interface. For example, if Open And Repair tells you that a floating object is causing an error in the document, but you see no floating objects, you might want to go ahead and delete them all-as demonstrated in “Loops” on page 1106-or you might want to check first to see how many you’re dealing with and then try to select them to know what they are before removing them.

To ask a question in the Immediate window, start with a question mark. For example, to see how many shapes exist in the main document body, type the following.
```
 ?ActiveDocument.Shapes.Count
```
When you press Enter from this line, you’ll see the answer in the Immediate window. If any shapes exist, you might want to use the Immediate window to select one at a time and see what each is. To execute an action in the Immediate window, no leading character is necessary. Just type the statement and press Enter. In this case, type the following:
```
 ActiveDocument.Shapes(1).Select
```

Though you can only execute one line at a time in the Immediate window, you can type several lines if you need them, so that you don’t have to keep retyping the same thing. You can’t run individual lines of the same macro consecutively-the Immediate window doesn’t work like running a procedure. Each time you execute a line, it’s an entire procedure. So, things like loops or conditional statements that require more than one line can’t be done here. But, you could leave the line ?ActiveDocument.Shapes.Count and type ActiveDocument.Shapes(1).Select below it. You can continue to type lines as you need to in that window, placing your insertion point in the statement you want to execute before you press Enter.

Note

When you press Enter from the line ActiveDocument.Shapes(1).Select, the first floating object in the document is selected. But, because it might be off the page or hidden, you might not see it immediately. So, just remember what you already know. That is, VBA isn’t a foreign program-it’s an extension of the program you’re using (Word, Excel, or PowerPoint).

What does that mean? In this example, it means to use the methods you already know for getting to that object once you’ve used VBA to select it for you. For example, after telling VBA to select the shape, switch back to the document window (Alt+F11), press Ctrl+X to cut the selection from the document (if you’re not able to see it), create a new document, and then paste (Ctrl+V) to see what the object is.

You can use the Immediate window to execute tasks that aren’t possible from the document window, as follows.
- To reset the used range for the active worksheet (that is, the last used cell that is selected when you press Ctrl+End in a worksheet), use the following statement.
```
 ActiveSheet.UsedRange
```
- To apply the setting to connect chart lines where empty cells exist in the data, you can use the Hidden And Empty Cells dialog box available from Select Data Source. However, if your chart is a combination chart that contains one or more series that aren’t line series, that option is unavailable. In that case, use the Immediate window and type the following statement.
```
 ActiveChart.DisplayBlanksAs = xlInterpolated
```

The Immediate window is also a good place to execute simple or repetitive actions that aren’t available from the user interface. For example, say that you want to add a few hidden bookmarks to the document. Create hidden bookmarks by starting the bookmark name with an underscore, which can’t be done from the Bookmark dialog box. Instead of writing a procedure that you have to run to add each bookmark after you select the range where you want the bookmark to appear, use the Immediate window to do this. Type the following statement.

```
 Selection.Bookmarks.Add(".name")
```
In the above statement, substitute the name of your bookmark for name. Just place your insertion point where you want that bookmark to appear, then use Alt+F11 to switch back to the Visual Basic Editor and press Enter from this statement to add the hidden bookmark.

Note
Hidden bookmarks are often used in documents or templates as markers for where you want a macro to insert specified content (such as a user’s reply in a dialog box).

When you’re not sure of the phrasing for an action that only requires a single statement, the Immediate window is a great way to test the code you think you need. You get the same Auto Lists and ScreenTips in the Immediate window that you get in a code module, so it’s a good way to try out your syntax. Note, however, that the Immediate window is automatically cleared whenever you exit Word. So, if you want to reuse anything you figure out using the Immediate window, copy it into a module and save the module before ending your Word session.