VBA Primer

4/15/2011

Contents

Understanding When and Why to Use VBA
Introducing the VBA Language and Code Structure
Using the Visual Basic Editor
Writing, Editing, and Sharing Simple Macros
Working with VBA: Next Steps

Writing, Editing, and Sharing Simple Macros

Companion Content All code samples shown throughout this section are available in procedures in a module named PrimerSamples.bas, available in the Chapter23 sample files folder online at http://aka.ms/651999/files.

See Also For help importing a module into your Visual Basic Editor, see the section “Saving and Sharing Macros,” later in this chapter.

One of the most important differences between macros you record and macros you write is that, when you record a macro, you need to select an object to act on it. But when you write macros, you can usually identify items to act on instead of selecting them. That apparently simple difference gives you tremendous power and flexibility. For example, you can write a macro to act on all tables in your document automatically, rather than recording a macro that you run from each table.

We’ve now reached the core of this primer. From creating a macro to reading and understanding essential VBA language constructs, the sections that follow progress in a logical order to help you learn in such a way that you can immediately put your knowledge into practice. Review the content under each heading and try the examples for yourself in the Visual Basic Editor. Be sure that you understand the content covered under each heading before progressing, and you’ll be using VBA comfortably before you know it.

Creating Modules and Starting Procedures

To create a module:

Select the project (in Project Explorer) to which you want to add the module.

You can click any element contained in the project to select the project, such as the project name or the Modules folder (if one exists).
On the Insert menu, click Module.

You can also insert a module from the Insert icon on the Standard toolbar. Notice that this icon defaults to what you last inserted (such as a module or a UserForm). Click the arrow beside the icon to select a different item from the available options, as you see in Figure 23-4.

Figure 23-4 Quickly insert a module from the Standard toolbar in any Visual Basic Editor.
To rename the module, click into the name field in the Properties window, as mentioned earlier. Type the new module name and then press Enter.

Once you have a module in which to create your macros, you can just click in the code window and begin typing to create a macro. As you saw in the sample recorded macro, every macro begins with the term Sub, followed by the name of the macro, and then followed by a pair of parentheses. Those parentheses can be used to hold instructions for the macro or information about references in the macro, but it’s rarely necessary to type anything between them for basic document production macros. Even if you type nothing between the parentheses, however, note that they are required.

Notice as well that every macro ends with the line End Sub. Many types of instructions you’ll learn throughout this section are paired (such as With and End With, demonstrated under the upcoming heading “Grouping Statements.”).

When you type Sub plus a procedure name and then press Enter, VBA automatically adds the parentheses at the end of the first line and adds the End Sub line. However, with most paired terms, the end term isn’t added for you. It’s good practice to always type both ends of a paired structure at the same time so that you don’t forget to do so later. When macros become longer or more complex, finding the missing end portion of a paired structure can be a frustrating use of your time.

So, to start a macro in your new module, type the following:

Sub MacroName

After you press Enter, the procedure will look like this:

Sub MacroName()
End Sub

The statements that your macro comprises will go between these two lines.

Learning the Language of Objects, Properties, and Methods

Just as the languages you speak comprise nouns, verbs, adjectives, and other parts of speech, VBA comprises objects, properties, and methods. Think of objects as nouns, properties as adjectives, and methods as verbs.

An object is just that—it’s a thing that can be acted on.
A property is a characteristic of an object—something that describes the object, such as its size or style.
A method is an action you can perform on an object. For example, Save and Close are both available methods for the ActiveDocument object.

The only difference between the sentence structure in a spoken language and in VBA is that, though a sentence always requires a noun and a verb, a VBA statement requires an object and either a property or a method. Let’s take a look at a few examples.

In the following statement, ActiveDocument is an object and Save is a method.
```
ActiveDocument.Save
```
In the following statement, Selection is the object (referring to the location of the insertion point—the actively selected content) and Style is a property of that selection. Heading 1, in this case, is the value (the setting) for the indicated property.
```
Selection.Style = "Heading 1"
```
Objects are often used as both objects and as properties of other objects, depending on where they’re placed in a statement. In the following statement, Tables(1) refers to the first table in the active document. Though a table is an object, it’s also used here as a property of the active document. Style, in this statement, is a property of the specified table.
```
ActiveDocument.Tables(1).Style = "Table Normal"
```
Even though Tables(1) in this case is a property of ActiveDocument, it’s still an object. Notice that the style being set is a property of the specified table.

You don’t typically need to think about whether an object is being used as an object or a property, similar to distinguishing whether an -ing word (such as creating, editing, or dancing) is being used in a given sentence as a noun or a verb. What’s important to note is that many objects, such as a table, require a higher-level object to make the reference specific enough for VBA to understand. For example, you can’t write simply Tables(1).Style to indicate the style of the first table, because VBA needs to know what range you’re referring to when you tell it to act on the first table. Otherwise, you might be referring to the first table in the document, the first table in the selection, or a number of other possible ranges. Just keep in mind that many objects can also be used as properties of other objects, because this will come in handy when you reach the “Getting Help” section later in this chapter.

Looking at the preceding list of examples, you might be wondering how you’re supposed to memorize every possible object, property, and method name in each program for which you need to learn VBA. Well, relax. You hardly need to memorize anything at all when it comes to program-specific terms. When you understand the concept of using objects, properties, and methods to create statements, and you remember what you already know (the features of the program you’re automating), you’ll learn the names of the particular objects, properties, and methods the same way you learn vocabulary in a spoken language—simply by using it.

Introducing Object Models

The set of VBA vocabulary that’s specific to a given program is known as the program’s object model. The Visual Basic Editor in each program also contains a “dictionary” of sorts for that object model, known as the Object Browser. You can use the Object Browser (available from the View menu) to search for the correct terminology to use for a given feature, or to see what properties or methods are available to a given object. For example, Figure 23-5 shows the range of results you get when you use the Object Browser in the Word Visual Basic Editor to search for the term table.

Figure 23-5 Despite different organization, the Object Browsers in Office 2010 and Office 2011 look and function very much the same.

Notice in Figure 23-5 that the selected item in the search results is the table object. The heading Classes refers to items in an object model that can have an available set of members—such as objects or modules. Properties and methods are members of a specified class. Notice the headings Classes and Member Of ‘Table’ in the bottom panes of the Object Browser.

Navigating the Object Browser

When searching for terms in the Object Browser, remember that multiple-word terms don’t get spaces in VBA. Separate words in a single term are instead denoted by initial capital letters, such as the ActiveDocument object or the PageSetup property. Searching in the Object Browser isn’t case-sensitive, but the Object Browser won’t recognize multiple words with spaces as a single term. For example, searching for pagesetup in the Object Browser will return no results, but searching for pagesetup will return several.

Note that the Object Browser is also available from the Standard toolbar. Or, to access the Object Browser by keyboard shortcut, use F2 in Office 2010 and Ctrl+Command+B in Office 2011.

In the following list, also notice the icons used in the Object Browser to denote objects, properties, methods, or library. These will also be displayed while you’re writing code, as explained under the next heading. (All of these icons are the same in Office 2010 and Office 2011.)

Object
Property
Method
Library

(An object model is a type of library. For example, results shown in the Object Browser in Figure 23-5 are members of the Word library, which is the same as saying the Word object model.)

Why Do I Get an Error When I Try to Set Some Properties?

The key to this question is to remember that you sometimes need to use VBA statements to get information about the document as well as to apply settings or execute actions. Many properties are read-only, meaning that you can use them only to return information, not to apply a setting.

For example, ActiveDocument.Name is a read-only property that tells you the name of the active document. You can’t set the name using this property, but that doesn’t mean you can’t name a document using VBA. For example, to change the name of the document, you’d use the SaveAs method (that is, ActiveDocument.SaveAs). With this method, you can specify several settings for how you want the document saved, including its name.

To learn whether a property is read-only, select that property in the Object Browser. At the bottom of the Object Browser is a pane where you see the hierarchy for the selected item (what class and library it belongs to). This pane also indicates when a property is read-only, as you see in Figure 23-6.

Figure 23-6 Definition for a member of the Word object model, shown in the Object Browser.

In the example shown in Figure 23-6, Word is the library and Document is the object to which the read-only property Name belongs. You’ll learn more about ways to use read-only properties as this primer progresses.

Using Auto Lists

One of the main reasons you don’t have to memorize the object model for the program you’re automating is that the Visual Basic Editor often gives you the available options as you write. When you type an object, for example, followed by a period, you automatically see a list of properties and methods available to that object, as shown in Figure 23-7.

Figure 23-7 An Auto List in the Word 2010 Visual Basic Editor.

Notice the icons, shown earlier, that appear in this Auto List to indicate properties or methods. All the members of a given object (that is, all properties and methods available to that object) appear in the list.

To scroll through an Auto List, you can use the up or down arrows as well as the Page Up and Page Down keys. You can also begin to type the item you need, if you know at least the first characters, to move to that position in the list. For example, if you type t immediately after the period that follows ActiveDocument, the list would move to the image you see in Figure 23-8.

Figure 23-8 Scroll quickly through Auto Lists in Office 2010 or Office 2011 VBA.

When you select the item you need in the Auto List, press the Tab key to add the item to your statement. (You can also press the Spacebar instead of using the Tab key. However, doing so will add a space in your code after the selected item.) Note that, if you press Enter once an item is selected in an Auto List, you’ll get an error unless the selected item was the last required term in the statement.

Understanding Variables

In addition to objects, properties, and methods, most macros use other types of terms as well, including variables and constants (the latter of which is discussed in the section “Using Constants,” later in this chapter).

Variables are types of data that represent objects, statements, or other elements required in your code. They’re often used to save time and make code more efficient, such as using a single term in place of a statement that you have to reference several times. They are also handy when you need to refer to any instance of a given object type, rather than specifying an instance of an object. Consider the following examples.

If you need to refer to the full name (the FullName property includes the file path) of the active document in a few places within your macro, you might want to declare a variable to represent it, as shown in the following statement:
```
myName = ActiveDocument.FullName
```
The name of the variable in this case is myName. Once you’ve typed this statement in your macro, you can use the term myName in place of ActiveDocument.FullName wherever you need to use the full name of the document.
When you use loops (discussed in the section “Looping Code,” later in this chapter) to execute a command for several instances of an object, you might use a variable as a counter to help you accomplish that. For example, say you want to apply a specific table style to all tables in the document, as shown in the following code.
```
Dim myI as Integer
For myInt = 1 To ActiveDocument.Tables.Count
   ActiveDocument.Tables(myI).Style = "Table Contemporary"
Next
```
The preceding code uses a For…Next loop, explained in the section “Using For Each…Next and For…Next Loops,” later in this chapter. However, notice how the variable myI is used here.
- First, you declare the variable as an integer. (Declaring variable data types is discussed in the upcoming section “Declaring Variables.”)
- Then, the start of the loop (the line that begins with the word For) tells the code to begin executing with the variable equal to the number 1 and run until the variable equals the number of tables in the document. Each time the loop executes, the number is automatically increased by 1.
- Next, notice that the variable is used to denote the table number in the statement that applies the style to the table.

Using variables in place of a complete statement, or as counters, is a common, useful tool. Other uses of variables are demonstrated under applicable headings later in this chapter, including “Using Conditional Structures” as well as “Looping Code.”

Introducing Variable Data Types

As you saw in the preceding examples, variables can be used to represent different types of information, such as numbers, text strings, or objects. Several variable data types are available, and you can even create your own. However, to help you keep things simple as you begin using variables, Table 23-1 lists commonly used variable data types.

Note For a complete list of data types supported in VBA and their definitions, search the topic “Data Type Summary” in Visual Basic Help, available from the menu bar in any Visual Basic Editor.

Table 23-1 Commonly used variable data types

Data type	Possible values
Boolean	True or False
Integer	An integer ranging from –32,768 to 32,767
Long	A long integer ranging from –2,147,483,648 to 2,147,483,647
Currency	A scaled integer ranging from –922,337,203,685,477.5808 to 922,337,203,685,477.5807
String	A text string, such as a VBA statement (text strings are relatively unlimited—they can reach up to approximately two billion characters in length)
Variant	A number or a text string (if you don’t specify the data type for a variable, it is a variant by default)

You can also declare variables as specific types of objects (such as a table, a style, or a document). Variables declared as a specific object type are called object variables, and they offer additional benefits, discussed next.

Declaring Variables

When you specify a variable type, which is called declaring the variable, you can save time and reduce errors. For more complex macros, declaring variables is also important because undeclared variables default to the variant data type, which uses more storage space than other data types and thus creates more work for the program running your macro.

Additionally, when you require that variables be declared in your modules, VBA lets you know while you’re still working on your code if variables contain spelling errors that could cause an error when users run your macro.

See Also For more on this subject, see the section “Running Macros and Compiling Projects,” later in this chapter.

When you declare an object variable—that is, a variable declared as a specific type of object—VBA recognizes the object so that you get Auto Lists for completing statements that include the variable.

CAUTION

When you declare a variable as a particular data type, you must use it as that data type. For example, if you declare myI as a string, VBA won’t understand if you use it in a statement as if it were a number (such as For myI = 1 to ActiveDocument.Tables.Count, as demonstrated earlier). Variables you want to use as numbers must be declared with an appropriate numeric data type (see the preceding table for the possible values available to different numeric data types). Similarly, to use a variable as a text string, you must set the value of that variable (the information after the equal sign) as either a VBA statement or a text string enclosed in quotation marks.

To declare a variable, use a Dim statement. For example:

Dim myI as Integer
Dim myName as String

Once you type the word as in a Dim statement, you get an Auto List of available options to help you complete the statement, as shown in Figure 23-9.

Figure 23-9 The Auto List shown here provides options for specifying a variable data type.

Declare Multiple Variables in One Statement

You can declare multiple variables on the same line; just be sure that you specify a data type for each. For example, the following statement does not declare all three variables as strings:

Dim myName, myPath, myStyle as String

The preceding code will seem to work, and it won’t generate any errors, as long as myStyle is used as a string data type. That’s because myName and myPath are declared as variants—no data type is specified for them. The correct statement to declare all three variables as strings would read as follows:

Dim myName as String, myPath as String, myStyle as String

To require variable declaration in a module, click in the very top of the module, type the words Option Explicit, and then press Enter. This statement is one of several that you can place at the top of a module to apply to all procedures in your module. Notice that, when you press Enter after typing this statement, a line appears beneath it, just as a line automatically appears between macros. This part of the module is known as the General Declarations section.

NOTE

You can set the Visual Basic Editor to require variable declaration automatically whenever you create a new module, through the Options dialog box in Office 2010 or the Preferences dialog box in Office 2011. On the Editor tab of that dialog box, check Require Variable Declaration.

For Mac users, as mentioned earlier, the Preferences dialog box did not hold these kinds of customizations at the time of publication. If you experience this, just get into the habit of typing Option Explicit each time you create a module.

Sharing Variables Throughout a Project

If you have multiple macros that need to refer to the same variables, you can declare them publicly for the entire project so that you don’t need to type out the declarations in each applicable macro.

To do this, type your variable declarations in the General Declarations section of any module in the project, and use the word Public instead of the word Dim to begin the statement. For example, the following statement makes myName a string variable, and myI an integer variable, available to all procedures in the project:

Public myName as String, myI as Integer

Note, however, that you must be in a procedure to assign a value to a variable. For example, you can declare myI as an integer variable for use throughout the project, but the statement myI = 1 must appear inside a procedure. To use one set of variable values for multiple macros across all modules in your project, put all value assignments for public variables in one macro, and then access that macro from any procedure where you need to use those values.

See Also To learn how to do this, see the section “Running One Macro from Another,” later in this chapter.

Never Write the Same Code Twice

One of the best pieces of advice I received when I first started learning VBA was this: if you have to type the same statement twice, ask yourself if there’s a faster way. For example, consider the steps discussed previously for declaring public variables to use the same set of declarations in all procedures throughout your project. Using grouping structures and loops (both discussed later in this chapter) is another way to avoid doing the same work twice.

Keep in mind that writing efficient code isn’t just about typing less. As with documents, the less work you do, the better your results will be every time—and the easier job you’ll have when that content needs editing. What’s more, efficient code also makes it easier for the program to run your macros, so you get macros that are easier to write, easier to edit, and easier to run.

What Do I Do When the Variable Type Doesn’t Work?

If you don’t know which variable type you need, you can’t find the variable type you think you need in the Auto List that appears in your Dim statement, or you get a “Type Mismatch” error (which means the variable type declared doesn’t match the way you’ve used the variable), there is an easy way out.

Though it’s not good practice to do this regularly, particularly in long or complex code, you can simply type Dim <variable name> and not specify it as a particular type. When you do this, VBA classifies the variable as a variant data type, so you won’t get Auto Lists when using the variable in statements. However, even if you have Option Explicit set for the module, as I hope you do, you can declare a variable in this way and continue on. (Alternatively, to avoid other editors of the code thinking that you’ve accidentally omitted the data type, you can declare the variable as a variant.)

It’s a bit of a sloppy workaround—what programmers refer to as a hack—but if you’re writing simple macros just for your own use, there’s really no harm in doing it occasionally, and it can save you time while you’re still learning about variable types. Just try not to make it a habit.

Understanding Document Variables and Data Storage Options

In addition to the variables that you use in your macros, there is an object type named Variable in the Word object model. These are known as document variables, because you use them to store information in the document that’s collected or created by your macros, rather than as a place to store data just while the macro is running—such as when you need the document to remember information from one use of a given macro to the next.

For example, in template automation projects I do for clients, I sometimes add document variables to store user preferences that are specific to the individual document, such as which of a selection of design choices the user wants for the active document. The document needs to store that information after the macro runs so that the user’s preferences are remembered the next time the design macros are used.

In Word, this type of information can be stored using either a document variable or a custom document property (which you’re most likely familiar with from the Document Properties dialog box). However, Excel and PowerPoint don’t offer a document variable object, so custom document properties are the way to go for storing document-level data in your workbooks and presentations.

In addition to document-level data, there are several ways to store data on the system level—that is, so that data can be accessed by your macros for use by more than an individual document. One of the easiest and most common methods is storing data in the Windows Registry (or, for Mac Users, in Library>Preferences).

As you can imagine, there are many uses for storing data in variables, document properties, or system-level resources such as the Registry. To explore this topic, use the Object Browser in your Visual Basic Editor to look up the Variable object, the property named CustomDocumentProperties, and the GetSetting and SaveSetting functions (the last two are functions used for storing data on the system level).

Storing Data on the System

The SaveSetting function warrants an additional mention here, particularly for Mac users, because this information isn’t easily accessible in Help on the Mac. When you use the SaveSetting function on either platform, you essentially create a named location on the system where the value you indicate is stored. You can then access that value using the GetSetting function from VBA in any Microsoft Office program.

On Windows, the locations you create are added as keys in the Windows Registry under the key: HKEY_CURRENT_USER\Software\VB and VBA Program Settings\.
On Mac, the locations you create are added as files (with the file extension .plist) in Library>Preferences (located in your Home folder). Note that earlier versions created these files in the Microsoft subfolder within Preferences, but that is no longer the case.

The structure of this function is as follows:

SaveSetting "AppName", "Section", "Key", "Setting"

Each of the four arguments is a value you define. For example, if you are automating the creation of documents for a company with multiple brands, you might create an AppName for the company name, such as NorthwindTraders, and then define a section for storing the user’s brand preferences, as follows:

SaveSetting "NorthwindTraders", "BrandPrefs", "Department", "Marketing"
SaveSetting "NorthwindTraders", "BrandPrefs", "Title", "Marketing Manager"
SaveSetting "NorthwindTraders", "BrandPrefs", "Location", "London"
SaveSetting "NorthwindTraders", "BrandPrefs", "Paper Size", "A4"
SaveSetting "NorthwindTraders", "BrandPrefs", "LetterStyle", "Personal Letterhead"

Typically, you would collect this information from the user via a UserForm (dialog box) or from another data source. If you ran the preceding SaveSetting functions, the data you collected would be stored as follows:

In the Windows Registry, you’d then see a key named NorthwindTraders with a subkey BrandPrefs. Within BrandPrefs, you would see each of the five values specified along with their data.
In your Mac Home folder Library>Preferences, you’d see one file named NorthwindTraders.plist that includes all of the data for any sections and values within those sections.

The most important difference between using this function on Windows and on Mac is that you can edit the Windows Registry values you create directly in the Registry if desired. On Mac, you can view the .plist file in Text Edit, but trying to edit that file directly in Text Edit will destroy your data. There are tools available for editing .plist files on Mac, or you can just replace stored values by using the SaveSetting function again to resave the value you need. On both platforms, you can delete the setting from the Registry or Preferences folder if they are no longer needed without affecting any other data or functionality on the system.

See Also Pref Setter is a user-friendly third-party tool for editing .plist files on the Mac. Find it at www.nightproductions.net.

Working with Object Model Member Arguments

In addition to the variables that you can declare for use in your procedures, many items in the VBA object models include elements that use the same data types as variables to specify settings for that item. The elements, known as arguments (similar to arguments in an Excel formula), can be required or optional.

An argument might be as simple as the index number of an object to specify it within the collection, such as the third table in the active document, written as ActiveDocument.Tables(3). Or, it might be a series of parameters that define how an action is to be executed, as is commonly used for VBA methods. Take a look at a few examples.

When you use the FollowHyperlink method of the Document object in a statement, you get the options shown in Figure 23-10 in the Quick Info ScreenTip that appears after you type the open parenthesis following FollowHyperlink.

Figure 23-10 The FollowHyperlink method is available in Word, PowerPoint, and Excel.

Most of the arguments shown in Figure 23-10 are optional. Typically, optional arguments appear in brackets, but as you see here, that’s not always the case. You can’t follow a hyperlink without an address. This example is an exception.

In most cases, if an argument appears in parentheses but seems to be key information, a default value is used when the parameter is omitted. For example, Selection.Move has parameters to define the unit and count by which to move the active selection. If those parameters are omitted, the default for unit is a character and for count is 1. So the insertion point is moved one character forward.
When you use the Add method for a Table object, you get the arguments shown in Figure 23-11.

Figure 23-11 Parameters for adding a table to a Word document.

The Add method is used for many objects in Word, PowerPoint, and Excel. It has different arguments, of course, for each, depending on the type of object being added. For the Table object, the range argument (that is, the location where you want the new table to appear), number of rows, and number of columns are required.

Notice that the required parameters here (those not inside brackets) specify particular data types. The range is an object variable (referring to the Range object), and the number of rows and columns both use the long data type (as noted in the Quick Info). Note that the optional AutoFit behavior setting is a variant (default) data type, but it requires a value from an available set of constants. Learn about constants in the upcoming section “Using Constants.”
The HomeKey method, shown in Figure 23-12, is used with the Selection object. It’s the VBA equivalent of using the Home key on your keyboard.

Figure 23-12 The HomeKey method displaying optional parameters.

The two available arguments used here—both of which are optional and use the variant data type—determine how far your insertion point moves (Unit) and whether the selection is extended (equivalent to holding the Shift key when you press the Home key) or your insertion point is simply moved to the new location. Both arguments require selections from a set of available constants, as we’ll discuss shortly in the “Using Constants” section.

There are two ways to specify most arguments in statements such as those in the preceding list of examples. The first approach is to type the values for the arguments between parentheses immediately following the method (as you saw in the Quick Info ScreenTips for the three sample methods shown in FigureS 23-10 through 23-12). When you use this approach, type a comma after each value you add. You’ll see that the active argument (the one for which you can add a value at your insertion point) is shown as bold in the ScreenTip. If you don’t intend to include a value for each argument, type consecutive commas until the argument you want to specify is bolded. If you place an argument in the wrong position between parentheses, the method won’t work correctly. Notice, however, that this approach can be confusing and difficult to read when you need to edit or troubleshoot a macro.

Instead, for methods that take more than a single argument, specify each by typing the argument name, followed by a colon and an equal sign, followed by the value you want to assign. Separate each argument you specify with a single comma, and note that argument order doesn’t matter when you use this approach. Take a look at the following two examples:

ActiveDocument.FollowHyperlink Address:="http://office.com", NewWindow:=True
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend

Using the explicit approach shown here helps to keep your code easy to read, edit, and troubleshoot.

How Much Do You Really Need to Know About Arguments?

It’s important to know the syntax for specifying arguments or parameters, but in most cases, you don’t need to worry about the data type or whether the argument is required.

Of course, you typically see the data type for required arguments in the Quick Info. But you’ll often know the type for optional arguments as well, simply from using the program. Because you’re an experienced user of the program you’re automating, you’re more likely to actively look for an argument that you know should be available for a given method (such as specifying a file type or setting a password for the file when you save a new document) than to need help understanding the ones you happen to find.

In the case of the SaveAs method to save a file with a new name or file type (SaveAs2 in Word 2010), not even the file name is a required argument because the Save As dialog box, as you’ve surely seen, always provides a default name. Word and PowerPoint default either to the first phrase in the document or, for blank documents, to the document or presentation number assigned when a new document was generated. In Excel, the Save As dialog box always defaults to the book number assigned when the workbook was generated.

Remember that one of the most important tools you have for working in VBA is your knowledge of the program you’re automating. As an advanced user, you’ll likely find that VBA is easier for you to learn than it is for a professional developer who doesn’t use the programs. Think of it this way: if you’re already a pretty good skier, you’ll probably learn how to snowboard much faster than someone who designs snowboards for a living but has never set foot on a mountain.

However, there will still be times when you’ll need help determining what data type to use for an argument’s value or knowing when an argument is required. You can find a description of each argument for most applicable object model members, along with its data type and whether it’s optional or required, in the help topic for that item.

See Also Learn how to find the help you need in the section “Getting Help”, later in this chapter.

Using Constants

As mentioned previously, many items in VBA require the use of another data type, known as a constant. Unlike variables that can change as needed, constants are used when a defined set of options exists for the feature. Most constants in VBA are either specific to the individual program object model or are available in VBA for any Microsoft Office program.

Constants specific to the Word object model start with the letters wd; those specific to the Excel object model start with the letters xl; those specific to PowerPoint start with pp; and those for use across the Microsoft Office programs start with mso. There are also sets of constants that are specific to the Visual Basic language and available to VBA in all of the Microsoft Office programs—these constants begin with the letters vb.

Because constants are defined members of an object model, you can search for them in the Object Browser. For the purposes of searching the Object Browser, note that a set of constants is considered an enumeration class, and the constants within that enumeration are the members of that class. Sets of available constants for a given argument are also usually easy to find through VBA help. Additionally, Auto Lists are available for many constant sets, particularly object and property constants. Take a look at a few examples.

The Type property of the Field object is available as a set of constants, provided in an Auto List when you type a valid statement for using this property. The example shown in Figure 23-13 is the beginning of a conditional statement.

Figure 23-13 Field type constants, shown in the Word 2010 Visual Basic Editor.

See Also Learn about conditional statements in the section “Using Conditional Structures”, later in this chapter.

Note that available constants might differ by platform or application. For example, the field type list in Figure 23-13 shows Word 2010 field types. Most, but not all, field types are available in both Word 2010 and Word 2011.
Because different header or footer types are available in each section, the Header and Footer objects have a set of constants from which to select when you use those objects, as you see in Figure 23-14.

Figure 23-14 Available header and footer constants are the same in Word 2010 and Word 2011.
The first macro you saw in this primer (in the section “Recording Macros”) recorded four consecutive statements for adding four paragraphs to the document. If you had written that macro instead, you could have used the constant vbCr, which is the VBA constant to indicate a carriage return. In that case, that first macro could have been written with the following code, in just two statements instead of six:
```
Selection.Style = ActiveDocument.Styles("Heading 1")
Selection.TypeText("Company Overview-" & vbCr & vbCr & vbCr & vbCr)
```
NOTE

The ampersand (&) is used to combine the text and constant portions of the text string, just as you can do to combine text, functions, and cell references into a text string in Excel. Learn more about using operators in VBA in the section “Using Operators”, later in this chapter.
Many arguments for different methods use the same sets of constants, which often are not available in Auto Lists, but are still easy enough to find. For example, the HomeKey method shown earlier uses constants for both of its arguments. The Unit argument uses the wdUnits set of constants; the Extend argument uses the wdMovementType set of constants.

The easiest way to learn which constant set you need is to search VBA help for the applicable method. This is because, in some cases, not all members of a constant set are available to all methods that use those constants. For example, wdUnits includes 16 constants, but only 4 are available when used with the HomeKey method. (The four available in this case are wdLine [the default if you don’t specify the argument], wdStory, wdRow, and wdColumn—the last two of which apply only when your selection is in a table.) If you searched for the HomeKey method in VBA help, you’d see information about the available constants for both arguments.

See Also Note that the upcoming “Getting Help” section shows you how to use the Object Browser and VBA help reference together to save time. Comprehensive VBA help might not appear to be available in Office 2011, but Mac users, take heart—the “Getting Help” section provides an easy solution.

VBA by the Numbers: Using Numeric Values in Place of Constants

It doesn’t take a mathematical genius to know that a term comprising several alpha characters, such as wdLine or msoThemeColorAccent2, is not a number, right? Actually, it is.

A set of constants is referred to as an enumeration because each constant represents a numeric value. When writing your code, if you know the numeric value that corresponds to the constant you need, you can use that value instead. If you select a constant in the Object Browser, the definition you see in the browser (below the search results window) indicates the numeric equivalent. Similarly, when you use a constant in your macro, you can right-click the constant and then click Quick Info to see its value, as shown in Figure 23-15.

Figure 23-15 The numeric value of the wdLine constant.

Understanding Collection Objects

Objects for which there can be many instances of the object type within a given scope are available as both an object and a collection object. A collection comprises all instances of a given object type within the specified scope. This distinction is important because the object and its collection object can have very different members (that is, a very different set of available properties and methods). For example, compare the two statements that follow:

Documents(1).Tables.Count
Documents(1).Tables(1).AllowAutoFit = True

The first of the two preceding statements uses the Tables collection object. The second uses the Table object, specifying the first table in the collection. Both statements also use the Document object, specifying the first document in the Documents collection. (Note that the Documents collection in the Word object model refers to all currently open documents. The first document in the collection refers to the most recently opened document.)

The Table object has a very broad set of members, as you see in Figure 23-16. It’s used whenever a single object is being referenced from the collection. Notice that only a fraction of this object’s member list is visible in a single screen.

Figure 23-16 Members of the Table object, shown in the Object Browser.

In contrast, the Tables collection object has very few members (shown in Figure 23-17), including only those items that can apply to the entire collection at once.

Figure 23-17 Members of the Tables collection object, shown in the Object Browser.

To Toggle or Not to Toggle

When you record some formatting options, such as applying bold or italics, you see the constant wdToggle set as the property’s value. That’s because, as you’ve likely noticed when using some types of font formatting, these settings toggle on and off—so you could, for example, apply a style that contains bold formatting to unbold text that was previously bolded.

However, when you write VBA macros, you can set toggle commands to absolute values where you need them, rather than using the wdToggle constant. To do that, simply use either True or False as the value instead of wdToggle.

Writing the macro Selection.Bold = True, for example, will apply bold text regardless of whether the selection was bolded before you ran the macro.

I Can’t Find Properties or Methods That Should Clearly Be Available to My Object

If you can’t find a member of an object that you just know has to be there, the problem probably isn’t a limitation in VBA; it’s syntax. More often than not, the Range object is the solution.

For example, if you want to act on all cells in a specific table, you might be looking for the following:

Documents(1).Tables(1).Cells...

However, when you get the Auto List after you type the C for the word Cells, you see that Cell is an option, but the Cells collection is not. Does this mean that VBA can’t act on all cells in a table at once? Of course not. What VBA is looking for is the following:

Documents(1).Tables(1).Range.Cells...

The Range object is often used to specify that you’re identifying the preceding object as the active scope of your statement. So, before you decide that you can’t do what you need to do with a given object, try using Range in your statement, as shown in the preceding example, and see what options you get in the Auto List that follows that range.

The Range object is also important to know because it can give you added flexibility. People new to VBA often use the Selection object too frequently because they don’t know how to identify objects in the document without first selecting them, but that slows down your code and often limits what you can do. You can use the Range object to identify any element in your document, which lets you take action without having to first select that item in the document. So, it’s less code for you to write and less code for the program to execute.

For example, you could use the following statement to identify the page number on which the first table in the document ends, without having to first select that table:

ActiveDocument.Tables(1).Range.Information(wdActiveEndPageNumber)

Grouping Statements

Say that you’re in a restaurant and you need three things from the waiter. If you ask for some ketchup, then ask for a glass of wine when the waiter drops off your ketchup, and then ask for a glass for your friend when the waiter returns with your wine, that’s a lot of work for the waiter (not to mention, he might be tempted to sneeze in your soup).

Instead, if you say to the waiter, “I need some ketchup, please. I’d also like another glass of wine, and my friend will have one as well,” you’ve given the waiter three tasks that he can execute together. That is, you’ve just grouped a set of statements (and saved yourself from a possible cold).

Though VBA won’t sneeze in your soup, macros do run more slowly when you force the program to execute several related tasks independently. Grouping related statements together helps make your code more efficient (and saves you time writing code, because you’ll be writing less).

Statements can be grouped using the With…End With structure, as you saw in the recorded macro example in the earlier sidebar, “Why Does My Recorded Macro Have So Many Lines of Code, When I Did Only One Thing?” You can use With…End With anywhere that two or more statements apply to the same object, or the same combination of objects, properties, and methods. For example, the very first macro we looked at in this chapter contains six statements, all of which apply to the Selection object. So, if you had written that macro instead of recording it, you could have typed the following:

With Selection
   .Style = "Heading 1"
   .TypeText "Company Overview-" & vbCr & vbCr & vbCr & vbCr
End With

NOTE

Recorded macros will sometimes include lengthier statements than are necessary when you write the macro. This is typically because a recorded macro often explicitly states defaults that are assumed if omitted. For example, in the recorded version of this macro, the style was applied with the statement Selection.Style = ActiveDocument.Styles(“Heading 1”). However, a style is applied using the style definition in the active document by default. So when written, as you see in the preceding code, that information can be omitted.

Though you might not be saving much by grouping statements when the macro is just two lines long, imagine something a bit lengthier. For example, say that you wanted to do several things to the first table in the document. Instead of starting each line with ActiveDocument.Tables(1), you can group the statements using a With…End With structure, as follows:

With ActiveDocument.Tables(1)
   .Style = "Table Contemporary"
   .Range.Style = "Table text"
   .Columns(4).Shading.ForegroundPatternColor = wdColorLavender
   .Rows(1).Range.Style = "Table heading"
   .Rows(1).HeadingFormat = True
End With

In fact, you can take that grouping a step further. Notice that the first row of the table is referred to more than once. You can add a nested With…End With structure for those rows as follows:

With ActiveDocument.Tables(1)
   .Style = "Table Contemporary"
   .Range.Style = "Table text"
   .Columns(4).Shading.ForegroundPatternColor = wdColorLavender
   With .Rows(1)
         .Range.Style = "Table heading"
         .HeadingFormat = True
   End With
End With

With grouping structures, just remember that all items in the With statement must apply to all statements between With and End With, if the statement starts with a period (which indicates that it uses the object referred to in the With statement). For example, you can do some things directly to the Row object that you can’t do directly to the Column object, such as applying a style. In that case, you might want to first select the column for which you need to apply a paragraph style, as you see here:

With ActiveDocument.Tables(1)
   .Style = "Table Contemporary"
   .Range.Style = "Table text"
   With .Columns(4)
         .Shading.ForegroundPatternColor = wdColorLavender
         .Select
   End With
   Selection.Style = "Table Subheading"
   With .Rows(1)
         .Range.Style = "Table heading"
         .HeadingFormat = True
   End With
End With

In the preceding code, Selection.Style doesn’t have to refer to the object in the With statement, because it isn’t using that object.

How Can I Apply Theme Fonts and Colors with Word VBA?

In the preceding examples of grouping structures, you may have noticed that an intrinsic constant was used to apply a shading color. The set of constants you get when you apply colors in Word VBA code are from the standard Microsoft Office color palette that was available in versions prior to Office 2007 and Office 2008 for Mac. These are not theme colors.

However, the wdThemeColor… constants are available only to Office Art objects in Word 2010 and Word 2011, such as shapes. (Note that in Excel and PowerPoint VBA, the xlThemeColor… and msoThemeColor sets of constants, respectively, are available to most content types.) So, how do you apply colors that will update if the document theme changes?

If you record a macro in Word to apply a color from the Theme Colors palette (such as to a paragraph border), you’ll see a numeric value in the resulting code that appears to represent the color you selected. That value actually represents the position you selected in the Theme Colors palette. So, using that value in your own macros will consistently apply the theme color at that palette position.

For example, in the preceding grouping structure code sample, if you wanted to apply the Theme Color palette position for the Accent 1 color instead of the standard color lavender, the line of code to apply the color would read as follows:

.Shading.ForegroundPatternColor = -738131969

But you don’t have to take the time to discover the 60 values that comprise the 10 Theme colors in the palette and their variations. I’ve done it for you.

Companion Content Find a macro named ThemeColorReferenceTable in the file named Sample macros.dotm that’s available in the Chapter23 sample files folder online at http://aka.ms/651999/files. That macro will generate a new document with a table representing the Theme Colors palette. The color and associated value number for each position in the palette are applied to the table, so you can save that table and use it as a reference tool whenever you need it.

Applying theme fonts in Word VBA is much simpler. Wherever you would specify the font name, you simply specify that name as “+Headings” or “+Body” to apply the theme heading or body font, rather than using the name of a specific font. For example, the first line of code in the following example applies the font “Cambria.” The second line applies the active theme’s heading font.

ActiveDocument.Paragraphs(1).Range.Font.Name = "Cambria"
ActiveDocument.Paragraphs(1).Range.Font.Name = "+Headings"

Insider Tip: Get More Flexibility for Theme Colors When You Use VBA

The preceding sidebar provided a workaround for situations where you can’t use the theme color constants in Word. But in cases where you can use those constants in Word, PowerPoint, and Excel, you can also use VBA to give you more design flexibility than you get from within the program—without sacrificing the capability for colors to change automatically when you apply new themes.

Use the TintAndShade property with an ObjectThemeColor property to specify any percentage for a tint or shade of the applied theme color. For example, the following code applies the Accent 3 color to a shape and then sets it to be 35 percent lighter than the applied theme color (a tint that is not available in the color palette for mid-range colors):

With Documents(1).Shapes(1).Fill.ForeColor
   .ObjectThemeColor = wdThemeColorAccent3
....TintAndShade = 0.35
End With

When using the TintAndShade property, use positive percentage values for tints (to lighten the color) and negative percentage values for shades (to darken the color).

See Also You can also get more flexibility for customizing tints and shades of theme colors for all Microsoft Office content to which theme colors can be applied when you use Office Open XML. Learn more about using Office Open XML to customize document content in Chapter 24, “Office Open XML Essentials.”

Looping Code

If I had to pick one feature of VBA that’s the most useful on a daily basis for document production and document troubleshooting, it would be loops. Loops enable you to act on several instances of a given object within one macro. Fortunately, as much as loops can do for you, they’re also extremely easy to use.

In this primer, we’ll look at variations on two of the most common types of loops, For loops and Do loops.

Using For Each…Next and For…Next Loops

A For Each…Next loop enables you to act on all instances of a given object within a specified range. For example, you might use this type of loop to format all tables in your document at once or to change the fill color of all text boxes in your document to a particular theme color. Similarly, a For…Next loop enables you to specify a range of instances of the given object on which you want to act. For example, say that all tables in your document other than the first five need to have the same formatting. You can use a For…Next loop to specify that the formatting should apply to only those tables you want.

To use a For Each…Next loop, start by declaring a variable of the object type upon which to act and then use that variable in your loop. Take a look at the code for the two examples given in the preceding paragraph.

Apply the style Table Contemporary to all tables in your document.
```
Dim atb as Table
For Each atb in ActiveDocument.Tables
   atb.Style = "Table Contemporary"
Next atb
```
The use of atb as the variable name for the table object is just a personal choice. As mentioned earlier in this chapter, you can use any name for a variable that meets VBA naming requirements (no spaces and a letter for the first character) and isn’t the name of any member of an available object model.
Remove any user-defined styles from the active document.
```
Dim ast as Style
For Each ast in ActiveDocument.Styles
   If ast.BuiltIn = False Then
         ast.Delete
   End If
Next ast
```
Specifying the variable in the Next statement, as shown in both preceding examples, is optional. However, it’s good practice to do this to avoid confusing the statements you need to keep or alter when you edit a macro, particularly when you use multiple loops in the same procedure.

To use a For…Next loop, start by declaring a numeric variable data type to use for counting the instances upon which you want to act. Following is the code for the example given earlier (formatting all but the first five tables in the document).

Dim myI as Integer
For myI = 6 to ActiveDocument.Tables.Count
   ActiveDocument.Tables(myI).Style = "Table Contemporary"
Next myI

Notice that I could have used a With…End With structure instead of retyping ActiveDocument each time I needed it. Of course, that would be more helpful if I were doing more than just applying a table style, as you see in the following example:

Dim myI as Integer
With ActiveDocument
   For myI = 6 to .Tables.Count
      With .Tables(myI)
         .Style = "Table Contemporary"
         .AutoFitBehavior (wdAutoFitWindow)
      End With
   Next myI
End With

In the preceding code, notice that I use the For…Next loop with nested With…End With structures to make this macro as efficient as possible to write, and as efficient as possible for Word to execute.

Using Do Loops

A Do loop, aside from being fun to say, can be another useful way of creating a loop for specified instances of an object. (Note that this type of loop is usually referred to as a Do…Loop structure, which helps to clarify the fact that, like For…Next loops or With…End With structures, a Do…Loop actually requires a pair of statements.)

Do…Loop structures can either be executed while a qualification is true or until a qualification becomes true. Similar to For…Next loops, a Do While…Loop is usually used with a numeric variable. A Do Until…Loop may be used with a numeric variable or until a given condition is true. Take a look at a couple of examples.

Say that you’re troubleshooting a document. Using Open And Repair in Word 2010, you find that a floating object is causing the unstable document behavior. However, you don’t see any floating objects in the document (this would happen if floating objects were off the page, or hidden behind opaque document elements because of the Behind Text wrapping style). Using a Do…Loop, you can delete all floating objects in the body of the document, as follows:
```
With ActiveDocument
   Do Until .Shapes.Count = 0
         .Shapes(1).Delete
   Loop
End With
```
In the preceding code, notice that ActiveDocument.Shapes(1) refers to the first shape in the document. In this case, you wouldn’t use a For…Next loop with a counter, because each time a shape is deleted, the shape object reference .Shapes(myI) would refer to a different object. Instead, if you continually delete the first shape until there are no more shapes, you don’t need to be concerned with the way VBA counts the shapes in the document as their number is being reduced.

In the case of deleting all shapes in a document, you may wonder why a For Each…Next loop wasn’t used, since we want to act on all instances of shapes in the document. For Each…Next loops are an easy solution in most cases that require acting on all instances of an object type. However, there are two reasons why the Do…Loop was the better choice here. First, there’s less code with a Do…Loop in this case because you don’t need to declare the object variable before executing the loop. Second, there’s an anomaly when you use a For Each…Next loop specifically to delete floating graphics (that is, members of the Shapes collection object), and one or more shapes may be left behind. Using the Do…Loop structure instead ensures that all shapes are deleted.
The following code uses a Do While…Loop instead of a For…Next loop for formatting all tables other than the first five with the Table Contemporary style and AutoFit To Window behavior.
```
Dim myI as Integer
myI = 6
With ActiveDocument
   Do While myI <=.Tables.Count
      With .Tables(myI)
         .Style = "Table Contemporary"
         .AutoFitBehavior (wdAutoFitWindow)
      End With
      myI = myI + 1
   Loop
End With
```
Notice in the preceding code that the integer variable was set to start counting at six, so the first five tables in the document would be ignored. The Do While statement says to execute the code in the loop while the integer value is less than or equal to the number of tables in the active document. Then, at the bottom of the commands that fall within the loop, you see a counter for the integer variable to increase the number by one with each iteration of the loop.

In the first of the two preceding examples, a Do…Loop structure is a better choice than a For…Next loop (as explained in the text that follows that sample code). However, in the second example, a For…Next loop would have been the more efficient choice. Notice that, in the second example, if you use a For…Next loop, you don’t need a separate statement for the counter—the For statement is a built-in counter.

So, how do you decide whether to use a For…Next loop or a Do…Loop structure? You just need to ask yourself a few simple questions, as follows (and as summarized in Figure 23-18).

Do you know the number of repetitions you need in the loop?

As demonstrated by the preceding code samples in this section, if the answer is yes, use a For…Next loop. If the answer is no, use a Do…Loop.
If you’re using a Do…Loop structure, is the condition initially true?

If the condition is initially true, you need a Do While statement to begin your loop. If, on the other hand, the loop needs to execute until the condition becomes true, start your loop with a Do Until statement.

There’s one more factor to consider when deciding on the loop type you need. You can evaluate the condition specified in a Do…Loop structure either at the top of the loop (as shown in the earlier example of a Do While…Loop structure) or at the bottom of the loop (with a Do…Loop Until or Do…Loop While structure).

A top evaluation loop is structured as follows:

Do While <condition>
   <statements>
Loop

A bottom evaluation loop, on the other hand, looks like this:

Do
   <statements>
Loop While <condition>

(Remember, in the preceding structures, to substitute Until for While if you need to execute the code until the condition becomes true.)

So, to determine whether you need a top or bottom evaluation loop, ask the following question: must the code execute at least once?

If the code must run at least once for your macro to do what you need, use a bottom evaluation loop so that the condition isn’t evaluated until after the first time the code runs. If the code doesn’t have to run at least once, use a top evaluation loop so that the condition is evaluated before the first time the code runs. For example, in the sample Do…Loop structure shown earlier—in which the loop is used to delete all shapes from the active document—a top evaluation loop is appropriate, because the code doesn’t need to run if the document contains no shapes from the outset.

Figure 23-18 A summary of the decision process for selecting the best type of loop for your macro.

Using a Loop to Delete Objects from a Word Document Leaves Behind Objects in the Header and Footer

If you press Ctrl+A to select the entire Word document and then press Ctrl+Shift+N to apply Normal paragraph style to everything selected, content in headers, footers, footnotes, endnotes, comments, or floating text boxes remains unaffected. This is because Select All really means selecting everything in the active Word story.

See Also For an explanation of Word stories, see Chapter 6, “Building Easy-to-Manage, Robust Documents.”

Similarly, when you work in VBA, there are some commands that you must execute them separately for each story in which you want to take action. For example, deleting all floating objects from the active document, as discussed in the preceding section, “Using Do Loops”, won’t delete those objects in the header or footer stories. To do that, you need to access the particular story you need. To delete floating objects in the main header of section 1, for example, instead of ActiveDocument.Shapes, you would specify ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Shapes.

Keep in mind that you can nest multiple loops inside one another. For example, say that you want to apply a paragraph style to every header in the document. You need to loop through each header in each section, which requires two loops, as follows:

Dim asc As Section
Dim hft As HeaderFooter
For Each asc In ActiveDocument.Sections
   For Each hft In asc.Headers
         hft.Range.Style = "Heading 1"
   Next hft
Next asc

Note that some actions require that you access the story in which you want to act before executing actions, rather than just identifying it. Also, before you try to loop through all story ranges in a document, be sure that the type of object upon which you’re acting is accessible to all story ranges, or you’ll get an error. For example, floating objects aren’t allowed in footnotes, endnotes, comments, or other floating objects, so including those ranges in your loop to delete shapes will throw an error.

Also note that, although a Word document is defined by stories, programmatically accessing content in PowerPoint takes a similar approach. For example, taking action on all slides in a presentation will not affect content on slide masters and slide layouts. If you wanted to delete all shapes in the presentation, you would need to include code to delete shapes from the slides, masters, and layouts (and separately include notes pages and notes and handouts masters, if applicable). This is less applicable in Excel, since most content resides directly on worksheets. However, note that it’s often necessary to programmatically access different types of sheets or objects (such as worksheets and chart sheets) separately.

Using Conditional Structures

As demonstrated with For…Next and Do…Loop structures, there are several ways to apply conditions to the commands you want to execute with VBA. Frequently, however, the condition you need may be something other than the instances of an object. Conditional structures in VBA, other than loops, are formed using either the paired If and End If statement or the Select Case…End Select statement.

Creating If Statements

Much like the IF function in Excel and the IF field in Word, If…End If structures in VBA are used for executing actions when specified criteria are met. Take a look at the following examples:

Say that you’re creating automation to format new business presentation documents. Your branding specifies that any presentation of longer than three pages should use landscape orientation. If the user clicks the button to use your formatting macro, you may want the macro to first check the length of the document and then set the orientation to landscape if the document exceeds three pages.
```
With ActiveDocument
  If .RangeInformation(wdActiveEndPageNumber) > 3 Then
     .PageSetup.Orientation = wdOrientLandscape
  End If
End With
```
Say that you’re applying a template to a document that uses only built-in Word styles, such as Normal and Headings 1–9. Once you’ve reformatted the document content as needed, you may want to clean up the document styles to help ensure that the document continues to be formatted with the styles you want. The following code removes any styles from the document that are not built in:
```
Dim ast As Style
For Each ast In ActiveDocument.Styles
   If ast.BuiltIn = False Then
         ast.Delete
   End If
Next ast
```

If…End If structures are often used with multiple conditions, such as when you want to set one value if the condition is true and another if it’s false, as you see in the following example:

With ActiveDocument
  If .Range.Information(wdActiveEndPageNumber) > 3 Then
     .PageSetup.Orientation = wdOrientLandscape
  Else
     .PageSetup.Orientation = wdOrientPortrait
  End If
End With

The preceding example adds an extra qualifier to the similar code shown earlier, so that if the document is three pages or shorter, your macro ensures that the document uses portrait orientation.

If statements can also contain multiple conditions by including ElseIf statements. For example, say that you have many tables in your document with different layouts, but all financial tables have either four or six columns. Those financial tables with four columns should use the custom table style named Table Financial 4, those with six columns should use the style named Table Financial 6, and all other tables in the document should be formatted using Table Normal style.

Dim atb As Table
For Each atb In ActiveDocument.Tables
   With atb
         If .Columns.Count = 4 Then
               .Style = "Table Financial 4"
         ElseIf .Columns.Count = 6 Then
                .Style = "Table Financial 6"
         Else
                .Style = "Table Normal"
         End If
   End With
Next atb

Notice that both If and ElseIf statements require Then at the end of the line. Also notice that, regardless of the number of conditions in an If statement, End If is still required at the end of the complete structure.

NOTE

For simple If structures where there is a single condition, you can get an exception to the paired statement requirement. You can often add just a single statement on one line and leave off the End If statement. For example, the If…End If statements in the first two examples given earlier could have been written on a single line.

Dim ast As Style
For Each ast In ActiveDocument.Styles
   If ast.BuiltIn = False Then ast.Delete
Next ast

Creating Select Case Statements

Although If structures are the most common conditional structure used in VBA, Select Case can be an extremely efficient alternative in some situations, so it is definitely worth a look.

Here, Select Case is used for one of the previous If statement examples:

Dim atb As Table
For Each atb In ActiveDocument.Tables
   With atb
         Select Case .Columns.Count
            Case 4
               .Style = "Table Financial 4"
            Case 6
               .Style = "Table Financial 6"
            Case Else
               .Style = "Table Normal"
         End Select
   End With
Next atb

For this code, the If structure and Select Case structure are very similar and essentially equally good choices. Where Select Case can be more useful is when several options meet a given condition.

For example, say that a long report document has been through a lot of hands and had content copied and pasted from several sources, and now you want to go through it and quickly clean up the styles. Perhaps you want to replace the style for paragraphs formatted with a number of different body text styles with Normal style, and replace the use of a few custom heading styles with the Heading 1 style. Notice in the code that follows that multiple options for a given case are separated by commas.

Dim myI As Integer
myI = 1
With ActiveDocument
    For myI = 1 To .paragraphs.Count
        With .paragraphs(myI)
            Select Case .Style
                Case "Body Text", "Body Text 2", "Body Text 3"
                    .Style = "Normal"
                Case "Body Heading", "Document Heading", "Page Heading"
                    .Style = "Heading 1"
            End Select
        End With
    Next
End With

If you used an If statement instead of Select Case here, the code would be as follows:

Dim myI As Integer

myI = 1
With ActiveDocument
    For myI = 1 To .paragraphs.Count
        With .paragraphs(myI)
            If .Style = "Body Text" Or .Style = "Body Text 2" Or .Style = "Body Text 3" Then
                    .Style = "Normal"
             ElseIf .Style = "Body Heading" Or .Style = "Document Heading" Or _
                .Style = "Page Heading" Then
                    .Style = "Heading 1"
            End If
        End With
    Next
End With

The savings with Select Case is the ability to separate multiple options with just a comma rather than repeating the entire condition for each option. As you gain experience writing your own macros, you will run into many situations where If or Select Case is the better choice.

The Value of Indenting Code

As you can see throughout the code samples in this chapter, code is indented to indicate statements within a group, loop, or condition. Where multiple structures are nested, code is indented a bit further for each level of nesting.

Though VBA doesn’t require indenting code, it’s fairly standard practice to do so, because it makes the code and the logic of the macro’s progression much easier to read.

For example, consider that when structures are nested, it’s essential that the end statements of paired structures fall in the correct order. So, for example, if you nest an If…End If structure inside a For…Next loop, the End If statement needs to appear above Next (that is, if the If structure starts inside the loop, it has to end inside the loop). Indenting phrases can make it much easier to diagnose this type of hierarchy issue.

To indent a line of code, press the Tab key at the beginning of the line. Your indent will remain when you press Enter. Press Backspace or Shift+Tab to remove the indent. Note that you can customize the indent width on the Editor tab of the Options (or, for Office 2011, Preferences) dialog box. Tab width is set to four characters by default.

To indent several lines of existing text, select those lines and then press Tab. Similar to how outline numbered lists behave, using Tab and Shift+Tab with one or more paragraphs selected will demote or promote the text rather than deleting it. However, if only one statement is selected, you must be sure to select the entire statement, or pressing a keystroke will replace the existing text.

Using Operators

VBA uses both symbols (such as &, <, >, =, +, -, /, *) and terms (such as And, Or, and To) for operators, depending on the usage. In all cases, however, operators follow standard mathematical syntax rules. Take a look at a few examples.

When I finish writing a chapter of this book, I need to copy all of the Heading 1 and Heading 2 paragraphs to update the table of contents. To do that, I make a copy of the document, from which I delete any paragraphs that don’t have those two styles applied.
```
Dim apr as Paragraph
For each apr in ActiveDocument.Paragraphs
   If apr.Style <> "Heading 1" And apr.Style <> "Heading 2" Then
         apr.Range.Delete
   End If
Next apr
```
The less than and greater than operators are used together here to mean “is not equal to.” Note that I could also have written the If portion of that statement as follows:
```
 If Not apr.Style = "Heading 1" And Not apr.Style = "Heading 2" Then
```
Notice that the use of Not to mean “anything other than” is repeated for each option meeting the condition.

If, instead, I wanted to delete all paragraphs that match either of those criteria, I would have written the following code:

Dim apr as Paragraph
For each apr in ActiveDocument.Paragraphs
   If apr.Style = "Heading 1" Or apr.Style = "Heading 2" Then
         apr.Range.Delete
   End If
Next apr

What if I wanted to delete all paragraphs that use Heading 1 or Heading 2 style, but only if they don’t appear in a table?
```
Dim apr as Paragraph
For each apr in ActiveDocument.Paragraphs
   If (apr.Style = "Heading 1" Or apr.Style = "Heading 2") And _
   apr.Range.Information(wdWithinTable) = False Then
         apr.Range.Delete
   End If
Next apr
```
In the first line of the If structure, the space followed by an underscore at the end of the line breaks a single statement of code to a second line. Breaking the line is not required, but is used when the line of code is too wide to read in a single screen.

Notice in the preceding code that the conditions that use the logical operator Or are grouped in parentheses, with the And operator outside the parentheses. Just as in a mathematical equation, that phrasing ensures that the condition within the parentheses is evaluated first.

As you’ve seen in examples throughout the primer to this point, an ampersand combines arguments into a text string, and typical arithmetic operators can be used on numeric values as they are in Excel formulas, including +, - ,*, and /. The plus sign can be used in some cases to combine text strings, but when you want to mix different types of variables in a text string, the plus sign can cause a “Type Mismatch” error, because it tries to calculate a result rather than combine the strings. So, using the ampersand to combine arguments into a string is always a good practice.

Notice also throughout these examples that comparison operators can be used either individually or together, such as < to indicate “less than” or <= to mean “less than or equal to.”

See Also Although the operators mentioned in this section are likely to be all that you need, they’re not an exhaustive list of every operator available in VBA. To learn about others, search for the topic Operator Keyword Summary in VBA help.

NOTE

Although it’s not included in the aforementioned Operator Keyword Summary, the term To can be used to represent a range of values (such as a more efficient alternative to using a greater than and less than range combined), as you’ve already seen in some code examples in this chapter. Consider the following examples, which are individual lines of a code from a For…Next loop and a Select Case statement.

For myI = 1 to ActiveDocument.Shapes.Count
Case 2, 3 To 10, 14

Introducing Message Boxes and Input Boxes

When creating macros for others to use, you’ll likely need to either give the user information or have the user specify information. Use message boxes to share information and input boxes to collect it.

Using Message Boxes

A message box might simply provide information, or it might require a response, such as Yes, No, Cancel, Abort, Retry, or Ignore.

The MsgBox command is one of several in VBA that can be used both as a statement and as a function. Use a MsgBox statement to provide information; use MsgBox as a function when you need a response from the user.

To create a message box statement, type MsgBox with the string of text you want the user to see. For example, take a look at the following message box statement and the message box it produces when run in Word, shown in Figure 23-19.
```
MsgBox "You're an unstoppable VBA genius!"
```
Figure 23-19 Notice that message boxes and input boxes automatically coordinate with the visual aesthetic of Microsoft Office on their respective platforms.

Even if your message box doesn’t require a reply, however, you might want to get a bit more creative with it. The MsgBox command includes optional arguments that let you customize the title bar and add an information icon, as shown in Figure 23-20.
```
MsgBox "You're an unstoppable VBA genius!",vbInformation,"My Message Box"
```
Figure 23-20 A message box with custom title bar and information icon, shown in Office 2010 (left) and Office 2011 (right).

The intrinsic constant vbInformation is one of a set of options in the buttons argument that enables you to add both an icon (as you see here) and response buttons. The third argument customizes the title of the message box.

Notice that including a title in Office 2011 reduces the size of the message box body text and places the title within the body area of the message. Also notice that the custom icons are not provided in Office 2011. Instead, the information icon is read as the application icon (Word, in this case).
To use MsgBox as a function (that is, to require a response from the user), first declare an integer variable for your message box so that you can use the response in the macro, as you see in the following example:
```
Dim myRes As Integer
myRes = Msgbox("Are you an unstoppable VBA genius?", vbQuestion _
+ vbYesNo, "My Message Box")
If myRes = vbYes Then
   Msgbox "I knew it!", vbExclamation, "You're a genius!"
Else
   Msgbox "Hang in there.", vbCritical, "It will get easier!"
End If
```
The first message box in the preceding code is shown in Figure 23-21.

Figure 23-21 A message box providing options to the user, shown in Office 2010.

Depending upon the user’s response, one of the two message boxes shown in Figure 23-22 is returned.

Figure 23-22 Message boxes customized in reply to a user response.

See Also Both message box and input box functions also include optional arguments for adding context-sensitive help files to those boxes. For additional resources where you can find information on VBA tasks that are not covered in this primer, such as creating custom help files for your VBA projects, see the “Getting Help” section, later in this chapter.

Using Input Boxes

Input boxes are similar to messages boxes, except that they’re always used as a function because they always require a response. Take a look at the following example:

Dim myInp As String
myInp = InputBox("How would you score on a basic VBA exam?", _
"My Input Box", "Perfect")
Msgbox myInp & " is pretty good!", vbExclamation, "My Input Box"

The input box from this code sample is shown in Figure 23-23. Notice that the Office 2010 and Office 2011 input boxes are more similar than message boxes. For example, note that the title bar is utilized in Office 2011 message boxes.

Figure 23-23 An input box shown in Office 2010 (left) and Office 2011 (right).

The text of the message box shown in Figure 23-23 is referred to as the prompt, the title bar text is the title argument (as in a message box), and the value you see in this image is the default value of “Perfect” specified in the third argument. Note that input boxes also include optional arguments for vertical and horizontal position on the screen (not shown here) for cases where you don’t want the box to automatically appear in the center of the screen.

Because the input box was declared as a string variable, notice that the response is used as part of a text string in a message box, as shown in the preceding code sample and in Figure 23-24.

Figure 23-24 A message box constructed using a combination of a string variable and text string.

If, instead, you need to use a response as a numeric value, declare the variable accordingly. In the following example, the input box asks for the number of columns to include in a new table being created by the macro. The variable defined as the input box reply is declared as an integer. (Notice that the input box in this case has only a prompt and a title bar—no default value is set, so the text box within the input box appears blank to the user.)

Dim myInp As Integer
myInp = InputBox("How many columns would you like?", "My Input Box")
With Selection
.Tables.Add Range:=.Range, NumRows:=5, NumColumns:=myInp
End With

CAUTION

There is a possible problem, however, with the preceding code sample. If the response is not an integer (including if the user cancels the input box without adding a reply), the macro will end in an error. You can, however, add what’s known as an error handler to correct for any error that may occur. Error handlers are an important part of writing macros effectively.

Companion Content To learn how to work with code errors and create error handlers, see the article “Managing VBA Errors,” available in the Bonus Content folder as part of the online companion content for this book, at http://aka.ms/651999/files. You’ll find an example in that article of an error handler created specifically for the preceding macro.

Running One Macro from Another

When you create a solution, such as developing a set of document production macros for yourself or creating a set of macros to help users format a template, you’re likely to have some of the same commands repeat in multiple macros. When those duplicated commands run to more than a few lines of code, it can be helpful to put the duplicated code into its own macro and run it as part of each macro that needs it. That way, you don’t have to write that code out in every macro where you need it.

Running one macro from another is also commonly done when several macros use the same variable definitions. For example, say that you declare the following public variables in the General Declarations section of the module:

Public myName as String, myComp as String, myIn as Integer

If several macros need to use the same values for that information, create a procedure just to store the values of those variables. That entire macro might look something like this:

Public Sub VarDefs()
myName = Application.UserName
myComp = ActiveDocument.BuiltinDocumentProperties("Company").Value
myIn = 1
End Sub

To then use these variable definitions in any macro in the project, simply call the macro that includes the definitions. The statement to call a macro is just the word Call plus the macro name. If the macro exists in a different module from the macro where you’re calling it, also specify the module name.

For example, to call the preceding macro from a macro in the same module, type the following statement:

Call VarDefs

If the macro from which you want to call VarDefs is in a different module, the statement would look like the following (assuming that VarDefs is in a module named myMod):

Call myMod.VarDefs

Note that, as long as the variables are declared as public, you don’t actually have to specify Public in the Sub statement of the preceding macro to make the contents of that procedure available to other macros in the project. However, if you want to allow the contents of that procedure to be shared only by other macros in the same module (such as in cases where macros in a different module might need to share a different set of values for the same variables), use Private Sub <procedurename>() to start the macro. Keep in mind that private procedures don’t appear in the Macros dialog box available from the Developer tab, so identifying a procedure as private is also a good way to keep it hidden from the user.

NOTE

Your macros might share many types of variables in a given project. The preceding variable example is just intended to point out one place where the Microsoft Office programs may offer you a simpler solution than VBA, depending on your particular needs. For adding document property information to your documents and templates, you can use the built-in Document Property Quick Parts.

See Also For more information about Document Property Quick Parts that have information already bound to content controls, as well as for resources where you can learn about creating custom bound controls, see Chapter 12, “Dynamic Content.”

Setting Macros to Conditionally Stop Executing Commands

You can add a statement to end the macro under specified conditions or to exit just a part of the macro.

To end execution of a macro, use the term Exit Sub. For example, say that you want to stop a macro from running if no document is open. That code would look like this:

If Documents.Count = 0 Then
Exit Sub
End If

Exit Sub, however, exits the active procedure. If you have one procedure running from another, you might need to end code execution entirely instead of exiting the individual subroutine. In that case, just use the term End.

If Documents.Count = 0 Then
End
End If

To exit a loop when a condition is met, use an Exit statement specifically for the loop type, such as Exit For or Exit Do. Following is an example of an Exit For statement:

Dim ast as Style
For each ast in ActiveDocument.Styles
   If ast.NameLocal = "Sample" Then
      ast.Delete
      Exit For
   End If
Next

Running Macros and Compiling Projects

You can run a macro directly from the Visual Basic Editor or from the Macros dialog box available in the Microsoft Office programs, or you can customize the user interface to add the macro to either the Quick Access Toolbar or the Ribbon (or, in Office 2011, to a toolbar or menu). In Word 2010 or Word 2011, you can also assign a keyboard shortcut to a macro.

To add a macro to the Quick Access Toolbar or the Ribbon in an Office 2010 program, using the Customize Quick Access Toolbar and Customize Ribbon tabs of the Options dialog box in the applicable program.
To add a macro to a toolbar or menu in an Office 2011 program, on the View menu, point to Toolbars, and then click Customize Toolbars And Menus.
To assign a keyboard shortcut to a macro in Word 2010, on the Customize Ribbon tab of the Word Options dialog box, under the heading Keyboard Shortcuts, click Customize. To do this in Word 2011, on the Tools menu, click Customize Keyboard.

You can save keyboard shortcut assignments, Quick Access Toolbar customizations, and Office 2011 menu and toolbar customizations either for the application as a whole or in the individual document or template. When you customize the Ribbon through the options dialog box, you can customize it only for the application as a whole. To customize the Ribbon for an individual document, template, or add-in in Office 2010, you have to use Office Open XML.

See Also For more on Office Open XML, see Chapter 24. Additionally, see the article “Using VBA to Create Add-Ins,” available on the MSDN Office Developer Center at http://msdn.microsoft.com/en-us/library/gg597509.aspx. (This article applies to both Office 2010 and Office 2011. Office 2010 UI customization examples are presented in the article but the downloadable samples for the article also include an Office 2011 UI customization example.)

Do More Than You Might Imagine with One Line of Code

Depending on what you need to do with VBA, you might not need to write or run a macro at all. The Immediate Window in the Visual Basic Editor enables you to run just one line of code at a time. It’s simple to use and can be an incredibly powerful tool for troubleshooting document issues or executing actions that you can’t do from within the Microsoft Office programs.

Several chapters in this book include tips about using the Immediate Window and send you here for more information. Those tips include such things as resetting the used range in an Excel workbook, setting precise positioning for content on PowerPoint slide layouts and masters, or getting information about problem content that you don’t see in a Word document.

To use the Immediate Window, start in the Visual Basic Editor and then:

On the View menu, click Immediate Window. Or Press Ctrl+G (Ctrl+Command+G in Office 2011).

The Immediate Window opens at the bottom of the Visual Basic Editor by default.
With your insertion point in the Immediate Window, do one of the following:
- To execute an action, just type the line of code that you need and then press Enter (Return). For example, as shown in Chapter 18, “Working with Data,” to reset the used range on the active worksheet, type the following:
```
ActiveSheet.UsedRange
```
- To get information, type a question mark, immediately followed by the line of code you need (no spaces) and then press Enter (Return). For example, to count the number of tables in the active Word document, type the following:
```
?ActiveDocument.Tables.Count
```

For an article that offers more detail about using this tool, including many examples, see the additional resources provided in the last section of this chapter, “Working with VBA: Next Steps”.

Compiling Projects

As you’re writing lengthy macros, or when you’re ready to use your macros, compiling the project is an important step. Compilers are actually used to translate source code to executable code, which isn’t strictly necessary for typical VBA macros. But using the compiler in the Visual Basic Editor is an essential way to help ensure that your code works properly.

To compile a project, just select a module or click into a procedure in the project and then, on the Debug menu, click Compile <Project>. Depending on the size of the VBA project, compiling might be instantaneous or it might take a few moments. If VBA recognizes errors in your code, it will select the code containing the error and display a message box telling you the type of error.

Companion Content Learn about recognizing, fixing, and managing errors in the article “Managing VBA Errors,” available in the Bonus Content folder online at http://aka.ms/651999/files.

Getting Help

In Office 2010 VBA, you can easily search for help using the Type A Question For Help box that appears on the right side of the Visual Basic Editor menu bar. In Office 2011, VBA help takes you to an online index of VBA help topics.

But there are also often faster ways to get to exactly what you need:

In the case of error messages, the Help button in those message boxes takes you directly to a help article on that specific error message. If, however, you need information on an error message any time other than right when it occurs, search for the help topic Trappable Errors. You can then use the Find feature in the help or browser window (Ctrl+F on Windows and Command+F on Mac) to quickly locate the name or number of the particular error you need. The Trappable Errors article lists each error with a hyperlink to its article.
In the case of any object model member, right-click the name of the item where it appears in the code and then click Definition. This opens the Object Browser to the selected item—which might be enough information if you just need, for example, to see the available members of a selected object.

However, in the Object Browser in Office 2010, you can right-click any item and then click Help to open the help topic on that article. Note that some items, such as individual constants, might not have help articles—but articles are available for most members of the active object model.

For Mac Users

If you use the Help menu in the Visual Basic Editor, you get program help—not VBA help. If you click the Help button on the Standard toolbar in the Visual Basic Editor, you get a link to Visual Basic Editor help. This is an index of several useful help topics broken down by application. For example, you can see a handy list of what is different from the Office 2010 version of the object model for that Office 2011 application. But it’s not searchable and not nearly as comprehensive as Office 2010 VBA help. So, are you out of luck?

By now, you know when I ask a question like that, there’s usually an easy solution. The fact is that you have access to almost the exact VBA help that is available in Office 2010, because the help for Office 2010 VBA is hosted on Office.com.

To get to the developer home page for Word, go to the following link (yes, it’s long and messy but you only have to type it once and then bookmark it):

http://office.microsoft.com/client/helphome14.aspx?lcid=1033&NS=WINWORD%2EDEV&Version=14

To get to the developer home pages for PowerPoint and Excel, just substitute the text WINWORD in the preceding URL with POWERPNT or EXCEL. (Note that characters are missing from PowerPoint in the preceding text; this is intentional.)

Then, to get to the help you need, either search or (as you might find more effective in some cases) click the application Object Model Reference link and then drill down in the object model links for what you need.

When searching the Object Model help to get to what you need, keep in mind that many types of VBA elements (such as properties and methods) are members of an object, so you can find help for them under the applicable object. And if you’re not sure of the object to which an item belongs, remember that the Object Browser in the Visual Basic Editor gives you a definition for the item that includes its membership information.

Saving and Sharing Macros

You can export a module of code (as well as some other types of project elements), which is the equivalent of saving a copy of the file, by right-clicking the module in the Project Explorer pane and then clicking Export. Note that the file name you choose for the export doesn’t need to match the module name. Notice also that VBA modules have the file extension .bas.

To import a module of code, such as the samples available in the online bonus content for this book, right-click the project in Project Explorer and then click Import.

CAUTION

If you export or import modules, remember that some modules refer to code outside the module itself, such as when the project contains a UserForm (dialog box) or when one macro calls another from a different module in the project. Be sure that you’re aware of the project components that need to work together, so that you export or make note of everything you’ll need when you or someone else imports that content later.

See Also For additional resources, such as where you can learn to create and work with UserForms, see the section “Working with VBA: Next Steps” at the end of this chapter.

Because you can share an entire VBA project by sharing the Word, Excel, or PowerPoint file in which the project is stored, exporting is more often used as backup. This is usually a good idea, because if you lose a document or template, you of course lose any code it contained.

In particular, if you store a module of document production macros, for example, in Normal.dotm, exporting that module periodically for backup is an important safety measure. This is because you might solve some Word performance issues by deleting Normal.dotm and allowing Word to regenerate a new default template, in which case your macros would be lost.

Sharing Projects

To share an entire project, just compile the project, save the file, and share it as you would any file. Keep in mind that some networks block files that contain macros, so you might want to use a different method for safely sharing the content (such as saving a document or template containing a VBA project to a Windows Live SkyDrive folder or a Microsoft SharePoint library).

Some macro projects need to be saved as particular file types, such as for Excel and PowerPoint add-ins. Also, adding a digital signature to projects can help to avoid systems or programs blocking your macros.

See Also Learn about using VBA to create add-ins and signing your code in the MSDN Office Developer Center article “Using VBA to Create Add-Ins,” at http://msdn.microsoft.com/en-us/library/gg597509.aspx.

You can also protect your code when sharing projects—such as when you want others to be able to use the macros, but not to be able to see or copy your source code. To do this, select the project in Project Explorer. Then, on the Tools menu, click <Project> Properties.

In the <Project> Properties dialog box, you can rename the project (following VBA naming conventions), which does not affect the file name of the document, template, or add-in where the project resides. You can also click the Protection tab to require a password to view the code. For this feature to work, you must enable the Lock Project For Viewing option and provide a password. When you do, double-clicking the project in Project Explorer will display a box where you can type the password. Without the correct password, the macros can still be run from the user interface, but their code can’t be viewed. Note that, once you add a password, the password protection starts the next time the project is opened.

Save to your account