VBA Basics
Microsoft estimates that more than three million people use Visual Basic for Applications (VBA). Although the precise figure certainly remains open to debate, it's fair to say that Visual Basic and VBA, taken together, constitute the most popular programming language in the history of computing.
Visual Basic for Applications version 6.0 is a key component of Office XP. It bears a striking resemblance to Visual Basic 6.0 (VB), Microsoft's mainstream full-strength programming language, which creates executable programs (although it isn't yet up to the standards of Visual Basic 7). Most notably, the development environment—the place where you write and test program—is almost indistinguishable in VB 6 and VBA. The same tools are available in both products, the same commands work in both, and many of the language constructs are identical.
Some noteworthy differences exist, however, between VB and VBA:
VB has much more extensive tools for binding data to dialog boxes, designing databases, and interacting with a server in client/server or Web-based applications.
VBA, on the other hand, lets you use direct links to the object models of the underlying Office applications. Although VB can interact with objects in Office applications, it's a little more difficult to get at the objects (see the following example). In VBA, all the capabilities of the associated Office applications are immediately at hand.
→ For a description of the various Office object models, see "Using Object Models".
A simple example helps illustrate the difference between the two programming models. In VBA/Word, you can work directly with the Word object model to create a new Word document with a simple command:
Documents.Add
If you try to create a new Word document from Visual Basic, however, it's a little more difficult:
Dim objWord as Object
Set objWord = CreateObject("Word.Application")
objWord.Documents.Add
The object model in VBA/Word makes it easier to get at Word's underlying capabilities, just as the object models in VBA/Excel, VBA/PowerPoint, VBA/Outlook, VBA/Publisher, VBA/FrontPage, and VBA/Access make it easier to control the capabilities of those applications. In all other respects, the six variants of VBA are virtually identical.
And then there's VBScript—the "scripting" language used to write programs for Web pages. Although VB and VBA are so close that learning to use one gives you an enormous advantage in learning the other, VBScript hardly resembles VB at all. Some of the commands look the same, but VBScript doesn't work like VBA, doesn't respond like VBA, and the development environment (Microsoft Script Editor) bears only a vague semblance to VB's editor.
Note
You use VBA (or VB) to write macros (or programs) that will run with the Office applications. You use VBScript to write programs that run from Web pages (and, in unusual circumstances, HTML e-mail messages). VBScript is a language and environment unto itself; it's not covered in this book.
In general, you'll find it easier to work with Windows itself using VB, but it's much easier to automate individual Office applications through VBA. Unlike VB, which costs hundreds of dollars, VBA is included with Office, at no additional cost. Even if you have no programming experience, you can leverage your knowledge of Office applications to learn VBA (and VB) programming techniques on the side.
VBA contains an internal hierarchy that enables you to organize and manage your programs. At the lowest level, the individual macros (more accurately, procedures: snippets of programming code in subroutines and functions) do the work. Groups of procedures get organized into modules. Modules can then combine with custom dialog boxes that you create (VBA calls them UserForms) to make up a project. When things happen to a dialog box (say, the user types something, or clicks a button), procedures respond to the actions. In addition, procedures can change the contents of the dialog boxes, and make them appear and disappear (see Figure 39.1).
Figure 39.1. VBA's basic internal hierarchy enables you to organize and manage your programs.
The project is then attached to a document, template, workbook, Publisher publication, or presentation—depending on which Office application you're using. In Word, Excel, Publisher, and PowerPoint, VBA projects don't have an independent existence; they're always attached to a document, template, workbook, or presentation. In Outlook and FrontPage, they all float around in a universal global layer.
With that bottom-up preview, let's take a much closer look at the components, starting at the top and working down.
Projects
To understand some VBA terminology, it's important to keep in mind that VBA evolved as an offshoot of Visual Basic (VB). In VB, a project amounts to what many people would call a system or an application—a collection of programs, dialog boxes, program libraries, system declarations, and so on that, taken together, perform some sort of task. In VB, you try to store all the pieces of a project together so they're easy to find and use.
In VBA, all the pieces have to be stored together, of course, but Office users customarily attach macros to documents, templates, worksheets, and other data files. Storing VBA code independently, in VB-style projects, isn't a natural fit, so Office applications use this simple compromise: In Office, a project is the VBA part of a document, template, workbook, or presentation. The following shows what this means in each application of Office:
In Word, there is one (and only one) VBA project in each document or template.
In Excel, there is one (and only one) VBA project in each workbook.
In PowerPoint, there is one (and only one) VBA project in each presentation.
In Publisher, there is one (and only one) VBA project in each Publisher publication.
In Outlook, there can be many projects, and they are always open.
In FrontPage, there is only one project, called Microsoft_FrontPage, and it is always open.
In Access, the terminology gets confusing because project has an altogether different meaning. Suffice it to say that an Access project contains one and only one VBA project.
Note
Unfortunately, the archaic "project" terminology can be confusing when you work with Office and VBA, and it appears in dialog boxes, Help files, and even inside the VBA Editor itself, notably in the so-called Project Explorer. When you see the term project, think of it as a document, template, worksheet, or presentation that contains VBA code.
For example, if you want to move or copy a few macros to a new VBA project—say, to split a large project into two smaller pieces—you first need to create a new document, template, worksheet, publication, or presentation.
The author of a VBA project can choose a "locked for viewing" option—that is, define a password to protect other users from viewing or modifying the VBA code. But this option is an all-or-nothing decision: Either you password-protect the entire project, or you allow other people to look at everything in the project.
Modules
Modules, on the other hand, are just collections of macros (or, more accurately, collections of procedures; see the next section for a definition). A project can have any number of modules, and a module can contain any number of procedures.
Typical uses for modules include the following:
To Distinguish Among Groups of Programs— For example, you might want to set up one module for VBA macros that you're testing, while maintaining "production" macros in a second module.
To Group Subroutines and Functions That Call Each Other— As long as you stay within one module, the methods for calling other procedures are simpler.
You cannot password-protect individual modules.
Note
When you open the VBA Editor or use the Help files, you'll see occasional references to class modules. Think of class modules as super modules that hold self-contained pieces of code, which can be reused throughout a project. Class modules are not covered in this book. To learn more about class modules, see Special Edition Using Visual Basic 6, published by Que (ISBN: 0-7897-1542-2).
Procedures: Subroutines and Functions
The actual work of a VBA program takes place in the collection of procedures, or chunks of code, that make up the program. Procedures live in modules, and come in two different varieties:
Subroutines— Generally constructed to perform a specific task. For example, a subroutine might loop through a PowerPoint presentation and replace the word "cheap" with "inexpensive."
Functions— Work just like subroutines, except they return a value. A function might loop through a PowerPoint presentation, replacing "cheap" with "inexpensive," and then return the number of times it made a replacement.
Subroutines start with the keyword Sub and a name, and end with End Sub. Functions start with the keyword Function and a name, and end with End Function.
Note
If you've used Excel functions, you already know how VBA functions operate: They take arguments, and produce a value. VBA subroutines can also take arguments, but they don't turn out values.
Macros
In Word, Excel, PowerPoint, Outlook, Publisher, and FrontPage, a macro is just a subroutine. When you open an Office application and run a macro with a given name, it runs the subroutine that goes by that same name.
→ For details on locating the macros, see "How Office Applications Store Macros".
In Access, the term macro has a completely different meaning. Access's old-fashioned "macros" are rigidly defined commands stuck in a matrix; they don't have anything in common with macros in the other Office applications.
UserForms
Don't let the similar terminology throw you—VBA UserForms have nothing in common with Word forms or Access forms. In VBA, UserForms are custom dialog boxes. You build UserForms when you write a VBA program. They let the user enter information that the routine can use, or click buttons that activate specific subroutines.