When you record a macro, every step you take is recorded, including moving your insertion point up or down or making a selection.

To begin recording a macro, on the Developer tab, in the Code group, click Record Macro. You can also access the Macro Recorder from the Status bar in Office 2010 or the Tools menu in Office 2011.

Once you click Record Macro, the Developer tab (or Status bar) icon changes to indicate that recording is in progress. The appearance of the button differs by program and where you access it. Click Stop Recording (accessible from the same location where you accessed the Record Macro feature) when you’ve finished recording the actions you need.

Let’s try one together as an example. Say that you’re starting a new, long presentation document. Each page of the document needs to begin with Headings 1, 2, and 3, consecutively, followed by a paragraph of Normal text. The first several pages of that document will each begin with the text Company Overview—(including the em dash) in the Heading 1 paragraph, followed by different text on each page.

To save a bit of time, let’s record a macro for setting up these pages.

With your insertion point at the top of the empty document, click Record Macro and then do the following:

To run that macro, on the Developer tab, click Macros, select the macro you just recorded, and then click Run.

You could run this particular macro each time you need to set up a page, or run it as many times as you’ll need identical pages. Or, you could edit it to add even more functionality, such as automatically adding the number of pages you need. But, for the moment, let’s just look at this macro as an example to demonstrate how to read VBA code.

To view the macro you just recorded, on the Developer tab, click Macros. Then, select your macro from the Macro Name list and click Edit. This will open the Visual Basic Editor with your macro open on screen. Your screen should look something like Figure 23-1.

Figure 23-1. The Record Macro dialog box, shown in Word 2010.

For now, focus on the macro itself—we’ll look at the different elements of the Visual Basic Editor shortly.

Notice that this code doesn’t include any unfamiliar terms, even if you’ve never seen a line of VBA code before. Selection, style, active document, type text, and type paragraph all refer to extremely basic Word tasks. The majority of program-specific terms in VBA will be similarly familiar, just from your experience with the program.

As you progress through this primer, you’ll come to understand how to construct the preceding lines of code and how you can write your own macros that are even simpler than recorded macros for accomplishing the same tasks.

Selection.Font.Bold = wdToggle

With Selection.Font
.Name = "+Body"
.Size = 11
.Bold = True
.Italic = False
.Underline = wdUnderlineNone
.UnderlineColor = wdColorAutomatic
.StrikeThrough = False
.DoubleStrikeThrough = False
.Outline = False
.Emboss = False
.Shadow = False
.Hidden = False
.SmallCaps = False
.AllCaps = False
.Color = wdColorAutomatic
.Engrave = False
.Superscript = False
.Subscript = False
.Spacing = 0
.Scaling = 100
.Position = 0
.Kerning = 0
.Animation = wdAnimationNone
End With

To begin to work in the Visual Basic Editor, you need to understand how files work in VBA—that is, how macros are organized and stored. The following common items are the principal components you need to know:

The code window is where your procedures appear. This is where you type macros when writing code and where you find the macros you’ve recorded. Notice that the procedure list is at the top-right of the code window. From this list, you can quickly move to any procedure in the active module.

Also notice the view options at the bottom of the screen. When you have several macros in a module, it can be helpful to view them one at a time. Full Module view is the default, but you can change this setting and many others through the Options dialog box (Preferences in Office 2011).

Project Explorer is where you see the list of all VBA projects that are currently open or loaded. All open documents, as well as open or loaded document templates, appear here, whether or not they contain macros. You can collapse or expand a project to view the modules and objects that it contains.

A project has a Modules or Forms folder only if it contains code modules or UserForms. However, in Word and Excel, every project contains an Objects folder, such as the Microsoft Word Objects folder you see under each of the projects visible in Figures Figure 23-2 and Figure 23-3.

In Word, the Objects folder contains a document object referred to as ThisDocument. In Excel, it contains both a ThisWorkbook object and a sheet object for each existing sheet in the workbook. Some types of code (such as a type of procedure known as a document-level event) are added directly in the code window for the document object rather than in a module. However, you will often have projects that have no code added to the document objects.

The Properties window shown in Figures Figure 23-2 and Figure 23-3 doesn’t look like much, but don’t be fooled. For modules, the Properties window is generally used only to edit the module name. However, for some object types (such as UserForms), the Properties window becomes extremely important because it’s populated with many settings that you can edit directly within it, ranging from the height and width of a UserForm to the value to display on a form control (such as a text box or an option button).

To edit the name of a module in the Properties window:

Note that, when you record macros, they’re always added to a module named NewMacros. You can rename that module if you like, but the next time you record a macro, a new module will be created with the name NewMacros.

You’ll find many settings that can be customized in the Options dialog box, available on the Tools menu in the Visual Basic Editor. (In Office 2011, this is the Preferences dialog box, available from the application name menu, such as Word.) I don’t recommend spending much time in this dialog box just yet, because you might not be familiar with many of the settings. However, it’s good to know that it’s there, because you are likely to need it. This primer will point out when settings can be customized in this dialog box.

Possible settings in the Options (or Preferences) dialog box include default behavior for a number of programming actions (such as the way you’re notified about errors in your code), the formatting for each type of text or notification you see in the code window (such as comment text or errors), and the way the window itself is arranged.

In addition to settings in the Options dialog box, notice that you can drag to resize panes in the Visual Basic Editor window (such as the Project Explorer or Properties window), and can close those you don’t need. In the Visual Basic Editors for Office 2010, you can also drag to dock or float panes.

Use the View menu to access any windows you’ve closed. If you’re unable to dock any window in an Office 2010 Visual Basic Editor, you can change the setting for that window on the Docking tab of the Options dialog box.

To create a module:

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

Note

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

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

Caution

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

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

Dim myI as Integer
Dim myName as String

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

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

Declare Multiple Variables in One Statement

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

Dim myName, myPath, myStyle as String

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

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

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

Note

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

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

Public myName as String, myI as Integer

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

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

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

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.

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.

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.

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

.Shading.ForegroundPatternColor = -738131969

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

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

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.

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

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

Using Do Loops

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

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

• Say that you’re troubleshooting a document. Using Open And Repair in Word 2010, you find that a floating object is causing the unstable document behavior. However, you don’t see any floating objects in the document (this would happen if floating objects were off the page, or hidden behind opaque document elements because of the Behind Text wrapping style). Using a Do…Loop, you can delete all floating objects in the body of the document, as follows:

  With ActiveDocument
     Do Until .Shapes.Count = 0
           .Shapes(1).Delete
     Loop
  End With

  In the preceding code, notice that ActiveDocument.Shapes(1) refers to the first shape in the document. In this case, you wouldn’t use a For…Next loop with a counter, because each time a shape is deleted, the shape object reference .Shapes(myI) would refer to a different object. Instead, if you continually delete the first shape until there are no more shapes, you don’t need to be concerned with the way VBA counts the shapes in the document as their number is being reduced.

  In the case of deleting all shapes in a document, you may wonder why a For Each…Next loop wasn’t used, since we want to act on all instances of shapes in the document. For Each…Next loops are an easy solution in most cases that require acting on all instances of an object type. However, there are two reasons why the Do…Loop was the better choice here. First, there’s less code with a Do…Loop in this case because you don’t need to declare the object variable before executing the loop. Second, there’s an anomaly when you use a For Each…Next loop specifically to delete floating graphics (that is, members of the Shapes collection object), and one or more shapes may be left behind. Using the Do…Loop structure instead ensures that all shapes are deleted.

• The following code uses a Do While…Loop instead of a For…Next loop for formatting all tables other than the first five with the Table Contemporary style and AutoFit To Window behavior.

  Dim myI as Integer
  myI = 6
  With ActiveDocument
     Do While myI <=.Tables.Count
        With .Tables(myI)
           .Style = "Table Contemporary"
           .AutoFitBehavior (wdAutoFitWindow)
        End With
        myI = myI + 1
     Loop
  End With

  Notice in the preceding code that the integer variable was set to start counting at six, so the first five tables in the document would be ignored. The Do While statement says to execute the code in the loop while the integer value is less than or equal to the number of tables in the active document. Then, at the bottom of the commands that fall within the loop, you see a counter for the integer variable to increase the number by one with each iteration of the loop.

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

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

Note

I wish I had conceived the questions that follow, but I can’t take the credit. Many thanks to Beth Melton, who was the technical reviewer for the first version of this primer and 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 you’re using a Do…Loop structure, is the condition initially true?

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

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

A top evaluation loop is structured as follows:

Do While <condition>
   <statements>
Loop

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

Do
   <statements>
Loop While <condition>

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

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

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

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

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

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

Note

See Also For an explanation of Word stories, see Chapter 6.

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

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

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

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

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

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.

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

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

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

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

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

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

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.

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

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

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 Input Boxes

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

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

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

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

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

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

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

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

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

Caution

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

Note

Companion Content To learn how to work with code errors and create error handlers, see the article “Managing VBA Errors,” available in the Bonus Content folder as part of the online companion content for this book, at http://oreilly.com/catalog/9780735651999. You’ll find an example in that article 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 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.

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

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.

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.

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:

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.