VBA Primer

  • 4/15/2011

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.

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:

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

  2. Apply Heading 1 style to the active paragraph.

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

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

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

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.

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.