Introduction to 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.

Note 

Macros can no longer be recorded in PowerPoint (which has always been the most limited of the three programs in terms of VBA capabilities), but you can still write VBA macros in PowerPoint. Macros can be recorded and written in Word and Excel.

So, what is a macro? A macro is simply a set of commands that can be executed together, similar to a paragraph style. However, where 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, literally every step you take is recorded, including moving your insertion point up or down or making a selection. Note that, while recording a macro, your insertion point can’t be used as a selection tool. Use the arrows on your keyboard, along with the Shift key, to make selections as needed while recording a macro.

Note 

Experienced users of VBA 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 a handful of commands can’t be recorded. For example, if you record a macro while adding items to the Quick Access Toolbar or the Quick Styles gallery, 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. 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. Or, on the status bar, click the Record Macro icon shown beside this paragraph.

Once you click Record Macro, the icon changes to a blue Stop Recording box. Click Stop Recording on either the Developer tab or the status bar when you’ve finished recording your macro.

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 body text. The first several pages of that document will each begin with the text Company Overview-in the Heading 1 paragraph, which will be followed after the em dash by different text on each page.

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

Setting Up the Sample Document

Before recording the macro, I set up the document in the interest of using the simplest method for any task. I set Style For Following Paragraph for Headings 1, 2, and 3 to the style that follows each heading at the top of every page. I also added Page Break Before formatting to the Heading 1 style, so that my new pages are started automatically when I apply Heading 1. Even so, I can still save time on setting up these pages by using a macro.

If you’d like to save time on this document setup, you can find the file  First Recording.docx that is included with the sample files on the book’s CD. The document contains no text, just styles Heading 1 through Heading 3 customized as indicated in this sidebar.

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, shown here, that recorded macros are stored, by default, in the global template Normal.dotm.

   

   In the Save In drop-down list, you have the option to select any open document or template, including currently loaded global document templates (Building Block and Quick Style Set templates won’t be available here). For now, leave the default Save In setting 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+(keypad)-.)

4. Press Enter four times.

   Because Style For Following Paragraph has been set for the first three heading styles, these four hard returns add paragraphs with the styles Heading 2, Heading 3, and Body Text, consecutively, followed by an additional Body Text paragraph. That additional Body Text 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.

With this particular macro, you could run it 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 functionality that enables it to do even more for you, 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.

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

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 the preceding example have an apostrophe at the beginning of the line. Those are comments. An apostrophe at the beginning of a line of VBA code means that there is no code to run on that line. When you record macros, VBA automatically adds some comment lines, one of which includes the name of the macro, as you see in the preceding image.

  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 causes the line to be skipped when the macro runs.

• After the comment text, you see the commands that comprise the steps of this macro. If you tried this for yourself and you see more lines of code in your macro than in my 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 lines of code you see above and how you can write your own macros that are even simpler than recorded macros for accomplishing the same tasks.

Troubleshooting

Why does my recorded macro have so many lines of code, when I only did 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. The reason for this is that, 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 what VBA did was record a setting for every option in the Font dialog box. This is because of the limitations related to recording macros with dialog box commands.

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 Wi th and End Wi th, except the Bol d setting, you’d still get the result you need. (Learn about the With…End With syntax later in this primer, in “Grouping Statements” on page 1103.)

Statements, Procedures, Modules, and Projects

To begin to work in the Visual Basic Editor, one of the most important things to understand is 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. Bol d=wdToggle is a statement. As you’ll see in “Writing, Editing, and Sharing Simple Macros” on page 1085, 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).