VBA Primer

4/15/2011

Contents

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

Introducing the VBA Language and Code Structure

The easiest way to begin learning VBA is to record macros and then look at what you’ve recorded in the Visual Basic Editor. In the subsections that follow, we’ll use this method to help you become acquainted with how to read VBA code.

So, what is a macro? A macro is simply a set of commands that can be executed together, similar to a paragraph style. However, whereas a style is a collection of settings that you can apply at once, a macro is a collection of actions.

Recording Macros

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

NOTE

Experienced VBA users continue to find macro recording useful for learning how to accomplish new tasks in VBA. One thing we all run into at some point, however, is the fact that there are a few commands that can’t be recorded. For example, if you record a macro while adding items to the Quick Access Toolbar in Office 2010 or using the new Search In box above the Ribbon in Office 2011, your steps won’t be recorded. In some cases, a macro that can’t be recorded means that you can’t accomplish the task through VBA, but it doesn’t always. You can do a great many things when writing VBA that can’t be done by recording macros, such as applying a document theme (or, in the preceding example from Office 2011, automating Find and Replace tasks). Learn more about this later in this chapter, as well as how to get help for finding commands that can’t be recorded.

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.

IMPORTANT

In the interest of using the simplest method for any task, set up your document as follows before recording the macro:

Set Style For Following Paragraph for Headings 1, 2, and 3 to the style that follows each heading at the top of every page. (Heading styles are followed by Normal style by default, so no change is needed for Heading 3.)
Add Page Break Before formatting to the Heading 1 style so that new pages start automatically when you apply Heading 1.

Even after taking these steps, you can still save time by setting up these pages using a macro.

See Also For help with the Style For The Following Paragraph feature used in this setup, see Chapter 8, “Styles.” For help with line and page break options such as Page Break Before, see Chapter 7, “Working with Text.”

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

In the Record Macro dialog box, type a name for your new macro. Macro names must start with a letter and can include letters, numbers, and the underscore character, but can’t include spaces or most special characters.

Notice, in the Record Macro dialog box, that recorded macros in Word are stored by default in the global template Normal.dotm. (In Excel, recorded macros are stored by default in the active workbook.)

In the Store Macro In list, you have the option to select any open document or template, including currently loaded global document templates in Word. For now, leave the default storage location and click OK to begin recording.
Apply Heading 1 style to the active paragraph.
Type Company Overview—. (To add the em dash, you can use the keyboard shortcut Ctrl+Alt+Hyphen in Word 2010 or Command+Alt+Hyphen in Word for Mac 2011. Note that you can only use the hyphen on the number keypad for this shortcut.)
Press Enter (Return) four times.

Because Style For Following Paragraph is set as needed for the first three heading styles, these four hard returns add paragraphs with the styles Heading 2, Heading 3, and Normal, consecutively, followed by an additional Normal paragraph. That additional Normal paragraph is where your insertion point will be when the macro starts to run again, so it will become Heading 1 style in the first step of the macro.
Click Stop Recording.

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.

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

Sub stands for subroutine, which is basically just another term for macro. Every macro begins with Sub and ends with End Sub, as you see in the preceding example.
The first few lines below Sub in Figure 23-1 have an apostrophe at the beginning of the line. These are comments. An apostrophe at the beginning of a line of VBA code tells the macro to skip this line. When you record macros, VBA automatically adds some comment lines, one of which includes the name of the macro, as you can see in Figure 23-1.

You can delete any line that begins with an apostrophe without damaging the macro. Be sure, however, not to delete the apostrophe and leave other text on the line that you don’t want to run as a VBA command. The apostrophe is what tells VBA to skip the line when the macro runs.
After the comment text, you see the commands that make up the steps of this macro. If you tried this for yourself and you see more lines of code in your macro than in this sample, ask yourself if you took other steps. If, for example, you made a typo in the Company Overview text and went back to correct it, that could have been recorded as a collection of several steps. Remember that when a macro is recorded, every keystroke is recorded. So, each time you use a different arrow key to move your insertion point, for example, you’ll get another line of code. Take a look again at the commands from the preceding macro.
```
Selection.Style = ActiveDocument.Styles("Heading 1")
Selection.TypeText Text:="Company Overview-"
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
```

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.

Why Does My Recorded Macro Have So Many Lines of Code, When I Did Only One Thing?

As mentioned earlier, when you record a macro, every keystroke is recorded. So, you often end up with much more code for a simple action than you would if you wrote the macro yourself.

In particular, if you use a dialog box to execute an action while recording a macro, you’re likely to get far more code than you may expect. When you click OK to accept the settings in a dialog box, you’re accepting all settings in that dialog box. VBA doesn’t record your keystrokes while you’re in most dialog boxes, so it must record every setting you accepted when you clicked OK.

For example, if one step in my macro was to bold a selected word, and I used the bold icon in the Font group on the Home tab, the code for that command would look like this:

Selection.Font.Bold = wdToggle

If, on the other hand, I opened the Font dialog box to apply bold and then clicked OK to close the dialog box, the code for that command would include all of this:

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

Notice that, because of the limitations related to recording macros with dialog box commands, VBA recorded a setting for every option in the Font dialog box.

If you write a macro, or edit your recorded macro, you don’t need to specify any setting unless you want the macro to execute that setting. In this example, if you were to delete everything between the lines that begin With and End With, except the Bold setting, you’d still get the result you need.

See Also Learn about the With…End With syntax in the section “Grouping Statements,” later in this primer.

Understanding Statements, Procedures, Modules, and Projects

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:

A statement is a single command or action in a macro—that is, it’s a line of code. For example, Selection.Font.Bold = wdToggle is a statement. As you’ll see in the section “Writing, Editing, and Sharing Simple Macros,” later in this chapter, when you think of VBA as a language, think of a statement as a sentence.
A procedure is essentially another way of referring to a macro, although there are other types of procedures as well, such as functions. A function is a procedure that returns a result.
A module is a collection of code. Think of a module as a code document. A module can contain several procedures. And, like documents, modules can be saved as files, copied, and shared.
A project is the collection of all modules and related VBA objects in your document, template, or add-in. A project might have one or several modules, as well as other elements such as UserForms (dialog boxes).

Companion Content All of the VBA elements discussed in this list are covered in this chapter, with the exception of UserForms. You can learn about creating and working with UserForms in the online companion content provided for this book. Once you master the VBA basics covered in this chapter, find information about the online companion content—and other resources for taking your work with VBA further—in the chapter conclusion.

Save to your account