To create a module, start by selecting the project (in Project Explorer) to which you want to add the module. Note that you can click any element contained in the project, such as the project name or the Modules folder (if one exists). Then, on the Insert menu, click Module.

Note

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

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 the parentheses for basic document production macros. Even if you type nothing between the parentheses, however, you must include the parentheses in this line.

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 in "Grouping Statements"). When you type the first line of a macro (beginning with Sub and ending with the pair of parentheses) and then press Enter, VBA adds the End Sub line automatically. (If you prefer, you can omit the parentheses when you type the first line and VBA will add them as well.) But, 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 later. When macros become longer or more complex, finding the missing end portion of a paired structure can be a frustrating use of time.

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

Sub MacroName()
End Sub

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

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

The only difference between the sentence structure in a spoken language and in VBA is that, though you need a noun and a verb in any sentence, you need an object in every statement, but either a property or a method might be used to complete the statement. Let’s take a look at a few examples.

Looking at the preceding list of examples, you might be wondering how you’re possibly supposed to memorize every possible object, property, and method name for each program in 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.

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 or by pressing F2) 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, take a look at the range of results you get when you use the Object Browser in the Word Visual Basic Editor to search for the term table.

Notice in the preceding image 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 Members 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 seen while you’re writing code, as explained under the next heading.

Troubleshooting: 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 only use them to return information and not to apply a setting.

For example, ActiveDocument.Name is a read-only property to tell 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. Using this example, to change the name of the document, you’d use the SaveAs method (that is, ActiveDocument.SaveAs). With this method, it’s possible to 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 the example that follows.

In the example shown here, Word is the library, and Document is the object to which the read-only property Name belongs. Learn more about ways to use read-only properties as this primer progresses.

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 the following image.

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 Auto 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 character, to move to that position in the Auto List. For example, if you type the letter t immediately after the period that follows ActiveDocument, the Auto List would move to the image you see here.

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.

In addition to objects, properties, and methods, most macros use other types of terms as well, including variables and constants (the latter of which are discussed under the next heading in this section).

Variables are types of data used to represent objects, statements, or other elements required in your code. They’re often used to save time and make code more efficient, such as by using a single term in place of a statement that you have to reference several times. They are also often used 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.

The preceding code uses a For...Next loop, explained in "Loops". However, notice how the variable myInt is used here.

Using variables in place of a complete statement, or as counters, are common, useful tools. Other uses of variables are demonstrated under applicable headings later in this chapter, including "Conditional Structures" as well as "Loops."

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, creating 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 "Running Macros and Compiling Projects" for more on this subject.

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 I declare myInt as a string, VBA won’t understand if I use it in a statement as if it was a number (such as For myInt = 2 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, the information after the equal sign when you set the value of that variable must either be a VBA statement or a text string enclosed in quotation marks.

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

Dim myInt 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, such as you see in the following image.

Inside Out: 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 statement that follows does not declare all three variables as strings.

Dim myName, myPath, myStyle as String

The preceding code will seem to work, and you’ll not get any errors as a result, 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. That statement is one of several 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 section 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 available on the Tools menu. On the Editor tab of that dialog box, select Require Variable Declaration.

Public myName as String, myIn as Integer

ActiveDocument.SaveAs FileName:="Sample.docx", WritePassword:="example"
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend

As mentioned under the preceding heading, 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 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 a class and the constants within that set 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.

Objects for which there can be many of the object type within a given scope are available as both an object and a collection object. A collection is the entire collection of all 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 the following image. 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.

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

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

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

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

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 that 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 (not to mention saving 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. Instead, 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 Troubleshooting tip titled "Troubleshooting: Why does my recorded macro have so many lines of code, when I only did 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 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 = ActiveDocument.Styles("Heading 1")
   .TypeText "Company Overview—" & vbCr & vbCr & vbCr & vbCr
End With

Though you might not be saving much when the macro is just two lines long, imagine something a bit more lengthy. 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

The thing to remember with grouping structures is that all items in the With statement must apply to all statements between With and End With, if the statement starts with a period (indicating 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.

Troubleshooting: How can I apply Theme fonts and colors with Word VBA?

In the preceding examples of grouping structures, you may have noticed that I used an intrinsic constant 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 earlier versions. These are not Theme colors.

However, if you find and try to use the wdThemeColor... constants, you’ll find that they don’t work as expected. (Note that in Excel VBA, the xlThemeColor... set of constants do work as expected.) 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, 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. Find a macro named ThemeColorReferenceTable in the file named Sample Macros.dotm that is included with the sample files on the book’s CD. That macro (available from the SampleMacros module in the referenced file) 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 that 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 that follows 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"

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’re going to look at variations on two of the most common types of loops, For loops and Do loops.

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

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

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, Do While...Loops are usually used with a numeric variable. Do Until...Loops 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, you find that a floating object is causing the unstable document behavior. But, 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. I wouldn’t use a For...Next loop in this case with a counter, because each time a shape is deleted, the shape object reference .Shapes(myI) would refer to a different object. Instead, if I continually delete the first shape until there are no more shapes, I don’t need to be concerned with the way VBA counts the shapes in the document as their number is being reduced.

  Note

  In the case of deleting all shapes in a document, you may wonder why I didn’t use a For Each...Next loop for this, since I 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 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, I’ve added a counter for the integer variable to increase the number by one on 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 of the preceding examples, 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.

Note

I wish I had conceived the questions that follow, but I can’t take the credit. Many thanks to Beth Melton, this book’s technical reviewer, for sharing her clear and concise approach to this topic (and others).

• 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 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 variable 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 the following.

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

The following diagram summarizes the decision process for selecting the best type of loop for your macro.

Troubleshooting: 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.

Similarly, when you work in VBA, some commands require that you execute them separately for each story in which you want to take action. For example, deleting all floating objects from the active document, as shown in this section of the primer, 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 the story. 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.

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 the paired If and End 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 only when specified criteria are met. Take a look at the following examples.

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 .RangeInformation(wdActiveEndPageNumber) > 3 Then
     .PageSetup.Orientation = wdOrientLandscape
  Else
     .PageSetup.Orientation = wdOrientPortrait
  End If
End With

The preceding example adds an additional 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 include 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.

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

As you’ve seen in examples throughout the primer to this point, an ampersand is used to combine 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."

The operators mentioned in this section are likely to be all that you need, but this isn’t an exhaustive list of every operator available in VBA. To learn about others, search for the topic Operator Summary in VBA help.

When creating macros for others to use, you’re likely to 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.

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 the previous example looks like this:

The text of the preceding message box 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), if 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 that looks like this:

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

Note

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. Learn to work with basic error handlers later in this chapter, in "Creating Error Handlers". You’ll find an example in that section of an error handler created specifically for the preceding macro.

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 of the macros that need 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, just 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, I would type the following statement.

Call VarDefs

If the macro from which I 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 also 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.

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

To end code execution entirely, just type End on its own line. 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
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

When you run one macro from another, you might also want to exit just the individual macro under certain conditions and not stop executing code altogether. For example, if you call one macro from another but only want to run the called macro under certain conditions, you can tell the code to exit that called macro with an Exit Sub statement, as you see in the following example.

If ActiveDocument.Tables.Count = 0 Then
Exit Sub
End if

You can run a macro directly from the Visual Basic Editor, or you can add it to the user interface to run it from the Quick Access Toolbar (or, in Word, from a keyboard shortcut).

Running Macros

To run a macro directly from the Visual Basic Editor (as I do frequently with on-the-fly document production macros), just click in the macro (anywhere between the Sub and End Sub lines will do) and then click the green Run SubUser Form arrow icon on the Standard toolbar. This option is also available from the Run menu, or by pressing the F5 key.

To add a macro to the Quick Access Toolbar, click the arrow at the end of your Quick Access Toolbar and then click More Commands to open the Customization tab of the <Program> Options dialog box. Then, do the following.

1. In the Choose Commands From list, select Macros.

2. If you want to add the macro to the toolbar just for a specific document or template, select that document or template from the Customize Quick Access Toolbar list.

   To be available in this list, the document or template must use an Open XML Format and must be open at the time.

3. Select the macro to add from the list at the left and then click Add.

4. To customize the appearance of the macro on the Quick Access Toolbar, select it in the list on the right, and then click Modify. In the Modify Button dialog box, you can select a different icon and edit the way the name appears in the ScreenTip for that macro.

To add a macro to a keyboard shortcut in Word, open the Word Options dialog box to the Customize tab and then click the Customize button to open the Customize Keyboard dialog box. As you see in the following image, select Macros in the list on the left and then select the macro you want to assign to a shortcut on the right.

Press the keystroke combination you want to use—such as Ctrl+; shown in this example. A notice appears to let you know if the combination is already assigned. You can select it even if it’s already assigned to another action, as long as you don’t need the shortcut for that other action. Notice that you need to select the document or template in which to save the shortcut. Keyboard shortcuts can be saved in both Open XML Format and legacy format Word documents and templates.

Note

For many actions, you might want to run just one line of code at a time—such as to get information about your document or to execute a single action. In this case, you can run the code from the Immediate window in the Visual Basic Editor. This is an extremely handy option that you’re likely to use regularly once you become accustomed to using VBA as a document production tool. Learn how to use the Immediate window later in this chapter.

Errors can occur when you compile a project or when you attempt to run a macro. Following are a few of the most common error types, along with their corresponding error numbers.

When an error occurs while you’re compiling a project, the error statement is selected and a message box appears telling you the type of error and offering help. If you click the help option, an article explaining the error type is opened in most cases.

If an error occurs when you run a macro, a similar message box appears to indicate the error type, but it provides the options End, Debug, and Help. Just as with compile errors, the help option usually provides an article with more information on the error type. (Note also that clicking the help option doesn’t close the error message for either compile or runtime errors.) Click End to dismiss a runtime error message if you’re familiar with the error and know where to find it. Or, click Debug for guidance in correcting a runtime error.

When you click Debug, the statement that caused the error is highlighted. (Note that, by default, the statement is highlighted in yellow with a yellow arrow at the margin indicator bar, but you can customize this formatting in the Options dialog box.) This also puts the program into Break mode, which stops code from executing. When this occurs, "[break]" appears in the title bar after the project name. To exit Break mode, which removes the highlight from the error statement, click the Reset button on either the Standard toolbar or the Run menu. (Note that Break mode also restricts many actions in the application window. So, resolve the error and turn off Break mode before you return to working in the applicable program.)

If a compile error occurs at runtime (meaning that you didn’t compile the code, but ran a macro that contains a compile error), the project goes into Break mode when you click OK to dismiss the error message. The title line of the macro that caused the error is highlighted and the error statement is selected.

When you write macros, particularly for others to use, it’s a good idea to try to account for any errors you can control. You can account for many possible errors with conditional structures to protect the macro from conditions you know would cause an error. For example, if the macro acts on the active document, you can set the macro to end without taking any action if no documents are open, as shown in "Setting Macros to Conditionally Stop Executing Commands".

However, you might also want to set up error handlers to manage what the user sees if an unexpected error occurs. In some cases, you might even know that certain conditions could cause an error, but find it more efficient to add an error handler than to write code to account for every possibility.

The two most common types of error handlers are the statements On Error Resume Next and. On Error GoTo ErrorHandler. Let’s look at those one at a time.

Use On Error Resume Next when a given instance of a loop, for example, might throw an error under certain conditions, but you’d still want the loop to continue running after it encounters an error. To do this, type On Error Resume Next on its own line early in the macro, before any code that could possibly throw the error for which you want the handler to correct. Then, if the possible error occurs, the user won’t be notified—the code will just skip the instance it couldn’t act on and continue on its merry way.

Use On Error GoTo... when you want to control what happens when an error occurs. For example, you might write an error handler that contains a message box telling the user that an error has occurred and what to do to correct it. Take a look at the following example.

Sub Sample()
On Error GoTo MyHandler
<code statements>
End
MyHandler:
MsgBox "Please place your insertion point in the table you want to copy before running this macro.", vbExclamation, "Please Try Again"
End Sub

Notice that this handler consists of an On Error GoTo... statement as well as another statement with the name of the handler followed by a colon (both in bold in the preceding sample). You can name the handler anything you like, within VBA naming conventions. Notice also the End statement that precedes the error handler, so that a macro that doesn’t throw an error ends before the error handler is executed. If you call one macro from another, use Exit Sub in place of the End statement shown in the preceding example. The End statement ends all code execution. Exit Sub exits the active macro and returns to the macro from which it was called.

What this error handler indicates is that, if an error occurs, the code stops executing and moves to the line following MyHandler: to continue executing code from that point. In this case, the handler just displays a message box giving the user information on why they couldn’t run the macro.

When deciding on how to handle errors for a particular macro, consider whether accounting for possible conditions or adding an error handler is the more effective way to go. For example, in the preceding code sample, if you just need the user to click in a table before running the macro, you might have added the following code at the beginning of the macro instead of an error handler.

If Selection.Information(wdWithinTable) = False Then
MsgBox "Please place your insertion point in the table you want to copy before running this macro.", vbExclamation, "Please Try Again"
End If

Dim myInp As Integer
ResumeInputBox:
On Error GoTo ErrorHandler
myInp = InputBox("How many columns would you like?", "My Input Box")
With Selection
   .Tables.Add Range:=.Range, NumRows:=5, NumColumns:=myInp
End With
End
ErrorHandler:
Select Case Err.Number
   Case 13
         MsgBox "Please enter a numeric value to continue.", vbInformation
         Resume ResumeInputBox
   Case Else
         Msgbox Err.Description
End Select

Of course, you can search for help using the Type A Question For Help box on the Visual Basic Editor menu bar. But, there are often faster ways to get to exactly what you need.

In the case of error messages, remember that 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 topic Trappable Errors—available from the Visual Basic Editor in Word, Excel, and PowerPoint. You can then use Ctrl+F for the find feature, 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, 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.

You can export a module of code (as well as some other project elements, discussed later in this chapter), which is the equivalent of saving a copy of the file. To do this, right-click the module in the Project Explorer pane and then click Export. Choose a location and name the file, following file naming conventions. The file name 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 on this book’s CD (discussed in the next section), right-click the project in Project Explorer and then click Import. Browse to and select the .bas file you need, just as if you were opening a document.

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 often 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, considering that 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.