VBA Primer
- 4/15/2011
- 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.
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.
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.
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.
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.
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).
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.
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)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.
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
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.
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 astSpecifying 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 WithIn 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 WithNotice 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 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 WithSay 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.
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.
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 aprThe 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 aprWhat 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 aprIn 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.
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 IfThe 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.
)
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
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.
){kind=link}
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.
){kind=link}
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
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.
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.)
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.
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.
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.