Extending Word 2007, Excel 2007, and PowerPoint 2007 with a Single Line of VBA Code

Article
07/09/2014

Summary: Use the Immediate window in the Microsoft Visual Basic Editor to save time and accomplish tasks that cannot be done from within the user interface. (6 printed pages)

Stephanie Krieger, arouet.net, Microsoft Office MVP

Applies to: 2007 Microsoft Office System, Microsoft Office Word 2007, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007

Contents

Stephanie Krieger, arouet.nethttp://www.arouet.net/, Microsoft Office MVPApplies to: 2007 Microsoft Office System, Microsoft Office Word 2007, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007ContentsIntroductionThese One-Liners are No Joke!Introducing the Immediate windowUsing the Immediate Window to Get InformationTaking Action in the Immediate WindowUsing the Simplest Solution for Every TaskAdditional ResourcesAbout the Author
These One-Liners are No Joke!
Introducing the Immediate window
Using the Immediate Window to Get Information
Taking Action in the Immediate Window
Using the Simplest Solution for Every Task
Additional Resources
About the Author

These One-Liners are No Joke!

Watch the Video: Extending Word 2007, Excel 2007, and PowerPoint 2007 with a Single Line of VBA Code

If you are a Microsoft Office power user, you probably record macros and possibly even edit them. But, if you considered learning to write your own macros and thought you would have to learn a lot of code to make the effort worthwhile, think again.

You might be pleasantly surprised at just how much you can do with just one line of Microsoft Visual Basic for Applications (VBA) code. In fact, you can accomplish many tasks in Microsoft Office Word, Excel, and PowerPoint that you can’t do from within the user interface (UI).

For example, a single line of code can solve any of the following problems:

You want to add bookmarks in a Word document but do not want them to be visible, regardless of display settings. Well, you can view hidden bookmarks from the Bookmark dialog box, but you can't create them there.
You deleted content from the end of an Excel worksheet, but when you select the existing worksheet content, the previously used area is still included. No matter what you delete or change on the worksheet, nothing resets the used range of the worksheet.
You set objects on two consecutive PowerPoint slides or layouts to the exact same position, but they still appear slightly off. How can you get more precise when the Size and Position dialog box only allows you to enter numbers to two decimal places?

In this article, you’ll find all you need to know for the preceding examples and learn about other types of tasks that you can accomplish with a single line of VBA code in Word, Excel, or PowerPoint.

Note
This article assumes that you have some basic familiarity with VBA, even if your experience is limited to looking at the VBA code for the macros you record. If, as you read, you feel that you would like to fill in some gaps in your basic VBA knowledge (or get started with VBA for the first time), see Additional Resources.

Also note that, although this article refers to the 2007 Microsoft Office system, many of the examples provided here work in earlier versions of Microsoft Office as well.

Introducing the Immediate window

As you may know, code that you run from the main Code window in the Visual Basic Editor (VBE) is always written in a procedure, the most common type of which is a Sub (or, as you may know it, a macro). However, the VBE also includes an Immediate window that you can use to execute a single line of code on its own. You can use the Immediate window to get information from an active document or execute a single action in a document.

Note
Although this article addresses Word, Excel, and PowerPoint functionality, the Immediate window is available in the VBE for all Microsoft Office programs that support VBA.

To access the Immediate window, do the following:

Open the VBE for the Microsoft Office program of your choice. To do this in Word, Excel, or PowerPoint 2007, press Alt+F11 or, on the Developer tab, click Visual Basic.
In the VBE, on the View menu click Immediate Window (or press CTRL+G).

By default, the Immediate window appears docked at the bottom of the screen. You can drag the window to resize or move it, or click the X on the title bar of the window to close it.

Notes

If you do not see the Developer tab, click the Microsoft Office Button, and then click Excel Options, PowerPoint Options, or Word Options. Click Popular, and then select the Show Developer tab in the Ribbon check box. Doing this in any of the Ribbon-enabled programs in the 2007 Office system enables the Developer tab in all of them.
CTRL+G is not a toggle command. That is, it does not close the Immediate window if that window is open. If the Immediate Window is already open when you press CTRL+G, that shortcut activates the window (that is, it moves the insertion point into that window).

Using the Immediate Window to Get Information

Do you ever need information about your document that is tedious, cumbersome, or impossible to get from within the UI? Of course you do. Whether you manually counted hundreds of rows in a Word table or searched in vain for a picture that you weren’t certain was deleted from an Excel worksheet, nearly every Microsoft Office power user has wished for an easier way to get information from a document at one time or another. Well, now you have one. If you have a question about your document, ask it in the Immediate window.

For example, say that your active Word document consists of a very large table and you need to quickly find out how many rows are in that table. Just open the VBE and then type the following in the Immediate window:

?ActiveDocument.Tables(1).Rows.Count

If this is your first time using VBA, notice how much you automatically know just from your experience with the Microsoft Office programs. For example, look at this line of code from right to left—there is no developer-speak here—it clearly says that you need a count of the number of rows in the first table in the active document.

To get information from a document, always start by typing a question mark at the beginning of the line of code.
Then, with your insertion point in that line of text, press Enter to get the answer. The answer appears in the Immediate window, directly below the line where you asked the question, as shown here:
```
?ActiveDocument.Tables(1).Rows.Count
122
```

Note
In the Immediate window, the Enter key is used to execute the active line of code. It is never used to make space or move content to a new line (as you would use it in a document). So, after you type the line of code that you want to run, your insertion point can be anywhere in that line when you press Enter to execute that line.

For the Excel example mentioned earlier—determining if a picture remains on the active worksheet—type the following in the Excel immediate window and then press Enter:

?ActiveSheet.Shapes.Count

Similar to the first example shown in this section, this line of code asks for a count of the number of shapes on the active sheet.

Note
When you type the first period in your line of code, VBA automatically provides AutoComplete lists (known as IntelliSense) in most cases that can help you find the term you need (and can help you learn what options are available). If you don’t get an IntelliSense list after the period, it’s often because you misspelled a term, used incorrect syntax (such as forgetting parentheses where they are required), or used an object name that isn’t valid in the active program without external references (such as trying to use the Selection object from Word in Excel). However, note that references to an individual sheet in Excel (such as ActiveSheet, Worksheets(index), or Sheets(index)) are exceptions that do not offer IntelliSense lists. Exceptions like this typically happen when the name of the object or property that precedes the period can refer to more than one object type, so that VBA does not have enough information to know which list to provide. (There is a workaround for this limitation when writing macros, but not for use in the Immediate window.)

When you type the first period in your line of code, VBA automatically provides AutoComplete lists (known as IntelliSense) in most cases that can help you find the term you need (and can help you learn what options are available). If you don’t get an IntelliSense list after the period, it’s often because you misspelled a term, used incorrect syntax (such as forgetting parentheses where they are required), or used an object name that isn’t valid in the active program without external references (such as trying to use the Selection object from Word in Excel). However, note that references to an individual sheet in Excel (such as ActiveSheet, Worksheets(index), or Sheets(index)) are exceptions that do not offer IntelliSense lists. Exceptions like this typically happen when the name of the object or property that precedes the period can refer to more than one object type, so that VBA does not have enough information to know which list to provide. (There is a workaround for this limitation when writing macros, but not for use in the Immediate window.)

Taking Action in the Immediate Window

When something that you need to do in a Microsoft Office document seems impossible, there’s a reasonable chance that you can do it using VBA. And, if it’s just one action that you need to take (such as resetting the used range on an Excel worksheet or adding a hidden bookmark in a Word document) you can do it in the Immediate window.

Try the following for yourself:

A bookmark in a Word document is hidden when the bookmark name begins with an underscore. You can't start a bookmark name with an underscore in the Bookmark dialog box, but you can in the Immediate window. Just place your insertion point in the document where you want the hidden bookmark to appear (or select the document content to include in the bookmark range) and then type the following in the Immediate window (substituting for name the actual name that you want to give your bookmark) and press Enter:
```
Selection.Bookmarks.Add "_name"
```
Then, open the Bookmarks dialog box, click the Hidden bookmarks option twice (to refresh the bookmark list) and you’ll see your hidden bookmark.
You select all content on an Excel worksheet (such as by using CTRL+SHIFT+END) and it selects far beyond the part of your sheet that currently contains content. So, you delete the empty rows and columns at the end of the range, but the selection still includes that empty area. Just type the following in the Immediate window and then press Enter:
```
ActiveSheet.UsedRange
```
That simple line resets the used range on your worksheet. Try to select sheet content again and you’ll see that only the current worksheet content is included.
When formatting a PowerPoint presentation, you may want to position different objects identically on consecutive slides or layouts (so that the positioning is consistent across slides in a presentation). Of course, you can select the object on the first slide and use the Size and Position dialog box to check the position from the left and top edges of the slide…then apply the same position settings to the related object on the next slide. But, because you can only set position to two decimal places (in your default unit of measure, such as inches or centimeters), the result can be imperfect and content can appear to shift when your presentation proceeds from one slide to the next. This is one of my favorite examples of where you can use the Immediate window to improve on the results you get from within the document window. Do the following:
1. Select the object on the first slide for which you want to match position.
2. Then, in the Immediate window, type the following two lines and then press Enter from each to get a response for each. (Remember that you can only run one line at a time from the Immediate window):
```
?ActiveWindow.Selection.ShapeRange.Left
?ActiveWindow.Selection.ShapeRange.Top
```
  Notice that the resulting values are measured in points and may be to several decimal places in precision, such as you see here:
```
?ActiveWindow.Selection.ShapeRange.Left
50.62283
?ActiveWindow.Selection.ShapeRange.Top
163.1243
```
3. Select the object on the next slide for which you want the identical position.
4. Then, in the Immediate window, make just small changes in the lines you previously typed.
5. Remove the question marks from the start of each line and add an equal sign between the end of each line and the value.
6. When the result looks like the following, press Enter from each line to set that position for the selected object.
```
ActiveWindow.Selection.ShapeRange.Left=50.62283
ActiveWindow.Selection.ShapeRange.Top=163.1243
```
  Notice that it took just two lines in the Immediate window to get information for a task and then execute the task perfectly, without any trial and error.

Using the Simplest Solution for Every Task

So, you have now seen ways to use one line of VBA code to get information more easily, accomplish tasks more precisely, and even accomplish things that you can’t do from within the document windows of the Microsoft Office programs.

Of course, the simplest solutions for most everyday tasks are available without leaving the UI. But, now that you know how to get started with the Immediate window, think about that option whenever you need to do something in your document, workbook, or presentation that seems too cumbersome, does not seem to work correctly, or even that seems impossible. The more VBA you learn, the more time you can save. For more examples of ways to use the Immediate window to save time, simplify your work, and get better results, see the article Troubleshooting Word 2007 Documents More Easily Using VBA.

Additional Resources

About the Author

Stephanie Krieger is a Microsoft Office System MVP and the author of two books, Advanced Microsoft Office Documents 2007 Edition Inside Out andMicrosoft Office Document Designer: Your Easy-to-Use Toolkit and Complete How-To Source for Professional-Quality Documents (Bpg-Other). As a professional document consultant, Stephanie helps many global companies develop enterprise solutions for Microsoft Office on both platforms. She also frequently writes, presents, and creates content for Microsoft. You can reach Stephanie through her blog, arouet.net.