Chapter 21: VBA Primer

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

This partial chapter is an excerpt from Advanced Microsoft Office Documents 2007 Edition Inside Out by Stephanie Krieger from Microsoft Press (ISBN 13: 9780735622852 copyright Stephanie Krieger 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Contents

• When and Why to Use VBA

• Introduction to the VBA Language and Code Structure

• Understanding and Using the Visual Basic Editor

• Writing, Editing, and Sharing Simple Macros

• About the Author

If you have any concerns about venturing into this chapter, take a deep breath and relax. You'll be perfectly comfortable here. This is the first thorough primer on Microsoft Visual Basic for Applications (VBA) written for advanced Microsoft Office users, and not for programmers.

I'm not a programmer, so I won't treat you like one. The fact is that you don't have to be a programmer to make effective use of VBA (or XML, as discussed in the next chapter). Yes, I use VBA and XML to develop solutions for clients, but that just means I'm taking advantage of all the tools that Microsoft Office Word 2007, Microsoft Office Excel 2007, and Microsoft Office PowerPoint 2007 have to offer for creating documents. If you can learn to format a table, create styles, or create fields in Word 2007; to write formulas or generate charts in Excel 2007; or to customize masters in PowerPoint 2007, you can learn VBA.

After years of avoiding VBA because it seemed technical and scary, I fell head over heels one day after I had no choice but to venture into the Visual Basic Editor for a client. I discovered both how easy it is and how much you can do with VBA even with just a basic level of knowledge. But, the most important thing I discovered was how much of the VBA language I already knew just from being an advanced Microsoft Office user. Nearly all elements of VBA that are specific to each program are the names of features and tasks you already know from using the program. Keep in mind that VBA is just an additional way to work with, and expand the capabilities of, the programs you already know.

Outside of the program-specific feature and task names, most VBA language and structure is virtually identical across Word 2007, Excel 2007, and PowerPoint 2007. So, the majority of what you'll learn in this primer will apply to macros you may want to write in any of these programs. However, because I assume that this is your first introduction to writing VBA (or writing any programming language, for that matter), I use one program for most examples, to avoid the confusion of trying to cover too much too fast. Because Word 2007 is the primary document production program for Microsoft Office, most examples throughout this primer use Word 2007 VBA. Just keep in mind that, once you're comfortable with Word 2007 VBA, you can apply all of the basics you learn to VBA tasks in Excel 2007 and PowerPoint 2007 as well.

When and Why to Use VBA

One of my favorite examples of both when and why to use VBA if you're not a programmer, came up one evening at dinner with a friend. She had been up until 3 A.M. the night before cleaning up tables for a report that was due that day. It was a Word document containing 50 tables copied from Excel that needed to be cleaned up and reformatted. The task took her, a power user, about six hours—which, at just over seven minutes per table, isn't bad.

But, she wanted to know if there was a quicker way for her to have gotten it done. She had created a few table styles and even recorded a macro for some of the formatting, but still had click into each table to apply them and then manually take care of any unique elements for each table.

In reply to her question, I asked if she knew any VBA, and she looked at me as if I had to be insane. But, then I told her that if she had known some basic VBA (just part of what you'll learn in this primer, by the way) she could have accounted for most of the differences between her tables in one macro and then formatted all of those tables at once. The task would have taken about six minutes instead of six hours. As you can imagine, learning VBA no longer seemed like a crazy idea.

Of course, this timesaving example is just one of several types of situations where you can benefit from VBA. As you can see in a couple of simple examples in the Excel chapters of this book, you can often use a single line of code to save substantial time or even do things you can't do through the features in the user interface. Or, to take things further, you might also use VBA to create customizations or automation for your documents and templates, such as with custom dialog boxes that can help users complete form documents.

In general, the answer to the question of when to use VBA is the same as when to use any feature in the 2007 Microsoft Office System—use it when it's the simplest solution for the task at hand. In the case of VBA, however, you may also be able to use it when there doesn't appear to be a solution for the task. VBA expands the capabilities of Word 2007, Excel 2007, and PowerPoint 2007, so that you might find yourself with easy answers to tasks that you didn't even know were possible.

In the 2007 Microsoft Office System, however, it's important to ask yourself if VBA is still the simplest solution before you embark on a complex project. With the introduction of the Office Open XML Formats, you can do some things in the 2007 release more easily today with XML—such as automatically populating document content with data from other sources. Also, some functionality that would have required automation in the past can now be done with built-in features, such as using a Content Control to display a custom Building Block gallery when you need a selection of boilerplate text options that can't be deleted. However, VBA macros are still almost exclusively the way to go when you want to use automation to save time on repetitive or cumbersome tasks.

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 2007, but you can still write VBA macros in PowerPoint. Macros can be recorded and written in Word 2007 and Excel 2007.

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.

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

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.

Note

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.

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

   Figure 1. The Record Macro dialog box

   

   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.

Figure 2. The Visual Basic Editor

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 With and End With, except the Bold setting, you'd still get the result you need.

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.Bold = wdToggle is a statement. As you'll see in the section titled "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).

Understanding and Using the Visual Basic Editor

Before you start working with VBA code, take a few minutes to settle in to your surroundings. To help you work more comfortably, the subsections that follow tell you a bit about each of the components of the Visual Basic Editor that are identified in the following diagram.

Figure 3. Visual Basic Editor components

The Code Window

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, discussed under the heading "Setting Up Your Workspace" later in this section.

Project Explorer

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.

Note

Documents appear in this list whether or not they're macro-enabled file formats. This is important to keep in mind because, if you add code to a document using an Open XML Format that ends with the letter x, you won't be able to save the document with its code. Save the document with the equivalent file format that ends in the letter m to enable your code to be saved along with the document or template.

• A project only has a Modules or Forms folder if it contains code modules or UserForms. However, in Word 2007 and Excel 2007, each project contains an Objects folder, such as the Microsoft Word Objects folder you see under each of the projects visible in the preceding image.

  In Word 2007, 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. Learn more about using the document objects in the section titled "Introduction to Using Events" later in this chapter.

The Properties Window

The Properties Window shown in the preceding image 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 in that window, 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, click into the name where it appears on either the Alphabetic or Categorized tabs, edit it as you would document text, and then press ENTER to set it. Module naming rules are the same as macro naming rules—no spaces or special characters, and the name must begin with a letter.

Note
All names in VBA subscribe to a similar set of rules. Names must always start with a letter, and can't include spaces or most special characters. Most names are limited to 255 characters in length. However, module names can't exceed 31 characters, and macro names added in the Record Macro dialog box are limited to 80 characters.

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.

Setting Up Your Workspace

You'll find many settings that can be customized in the Options dialog box, available on the Tools menu in the Visual Basic Editor. I don't recommend spending much time in this dialog box just yet, because you might not be familiar with many of the settings. But, it's good to know that it's there, because you will need it. I'll point out, throughout this primer, when settings can be customized in this dialog box.

Possible settings in the Options 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 docked panes in the Visual Basic Editor window (such as the Project Explorer or Properties Window), or close those you don't need. Use the View menu to access any windows you've closed. If you're unable to dock any window in the Visual Basic Editor, you can change the setting for that window on the Docking tab of the Options dialog box.

Writing, Editing, and Sharing Simple Macros

Note
Most of the features you'll learn about in this section are programming basics. They're written here specifically for VBA. However, it might be useful to know, should you ever want to learn another programming language, that many of the concepts and terms used throughout this section are fairly standard across common programming languages.

One of the most important differences between macros you record and macros you write is that, when you record a macro, you need to select an object to act on it. But, when you write macros, you can usually identify items to act on instead of selecting them. That apparently simple difference gives you tremendous power and flexibility. For example, you can write a macro to act on all tables in your document automatically, rather than recording a macro that you run from each table.

The section you're beginning is the core of this primer. From creating a macro to reading and understanding essential VBA language constructs, the headings in this section progress in a logical order to help you learn in a way that you can immediately put your knowledge into practice. I recommend reviewing the content under each heading and trying examples for yourself in the Visual Basic Editor. Be sure that you understand the content covered under each heading before progressing, and you'll be using VBA comfortably before you know it.

Creating Modules and Starting Procedures

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.

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 'are paired (such as With and End With, demonstrated elsewhere in this primer). 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.

Objects, Properties, and Methods

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.

• An object is just that—it's a thing that can be acted on.

• A property is a characteristic of an object—something that describes the object, such as its size or style.

• A method is an action you can perform on an object. For example, Save and Close are both available methods for the ActiveDocument object.

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.

• In the following statement, ActiveDocument is an object and Save is a method.

  ActiveDocument.Save
  

• In the following statement, Selection is the object (referring to the location of the insertion point—the actively selected content) and Style is a property of that selection. Body Text, in this case, is the setting for the indicated property.

  Selection.Style = "Body Text"
  

To continue reading this chapter, see Advanced Microsoft Office Documents 2007 Edition Inside Out.

About the Author

Stephanie Krieger is a Microsoft Office System MVP and the author of two books, Advanced Microsoft Office Documents 2007 Edition Inside Out and Microsoft Office Document Designer. As a professional document consultant, Stephanie helps many global companies develop enterprise solutions for Microsoft Office on both platforms. She also frequently writes, presents, and creates content for Microsoft. You can reach Stephanie through her blog, arouet.net.