Every Microsoft Office application contains most of what we know as Visual Basic. Hit Alt-F11 in Office to take yourself to an undiscovered country just over the VB horizon.

When you say “Visual Basic,” most developers—particularly those reading this magazine—will think of the Visual Basic development environment that has been the topic of these columns for several years now. So what do I mean by “beyond” Visual Basic? Don’t worry, I am not about to abandon the king of development tools for another language! I am, however, interested in exploring the capabilities of Visual Basic wherever it leads me, and that sometimes means going outside the traditional Visual Basic development environment. You’ll be surprised at the programming power you’ll find.

I am, of course, talking about Visual Basic for Applications, or VBA—the “macro” language that is supported by many Microsoft application programs. I put “macro” in quotes because, while VBA may have its roots in the keyboard macro tools of the past, which permitted recording and playback of keystroke sequences, it has evolved into something entirely different. In fact, VBA is essentially the regular Visual Basic language modified for use in controlling existing applications rather than creating stand-alone applications. You have the same rich set of language constructs, data types, control statements, and so on available to you. In fact, from the perspective of the language itself, a programmer would have trouble telling Visual Basic and VBA apart. Even so, VBA programs are still referred to as macros.

VBA is embedded in many Microsoft applications, most notably those that are part of Microsoft Office: Word, Excel, Access, Outlook, PowerPoint, and FrontPage. VBA has also been licensed by Microsoft to some other publishers of Windows software. You can use VBA in a keyboard macro mode in which you start recording, perform some actions in the program, and then save the recorded macro to be played back later as needed. While recording macros only scratches the surface of VBA’s capabilities, it is nonetheless an extremely useful technique that I use on a daily basis. It is important to note that a recorded macro is not saved as a sequence of keystrokes, as was the case in some older programs. Rather it is saved as a Visual Basic subroutine, and the statements that carry out the recorded actions consist primarily of manipulation of properties and methods of the application’s objects.

What’s Inside the Box?

Let’s look at an example. I created the macro in Listing 1 to print a shipping label, using a specialized label printer, from Word. To use it, all I need to do is select the address in the document, and then run the macro. Look at the code and you should be able to see how it performs the following tasks:

1. Copy the selected text (the address) to the clipboard.
2. Create a new document, based on the template “home_ship.” This template contains my return address and is formatted for the size of the labels.
3. Move the insertion point down 3 lines.
4. Paste the address from the clipboard into the document.
5. Make the label printer the current printer.
6. Print the document.
7. Close the document.
8. Make the default printer current again.

This recorded macro looks like any regular Visual Basic procedure. You have the Sub and End Sub statements, code comments, use of the line continuation character, and so on. And why shouldn’t it look like a Visual Basic procedure, since that is exactly what it is? So, while the term “macro” is still used for VBA procedures (particularly for recorded ones), remember that we are really talking about procedures.

In examining the code in Listing 1, you may notice that there aren’t any actual Visual Basic statements per se, but rather the entire procedure seems to consist of manipulation of properties and methods. This is precisely correct. For example, the first line executes the Copy method of the Selection object, which copies the selected text to the clipboard. The second line calls the Add method of the Documents collection, creating a new document based on the specified template.

Macros created solely by recording will not contain VBA code, but you can later edit the macro to add code to modify the macro’s functionality as desired. The above macro, for example, assumes that the address has been selected by the user before the macro is run. However, if the user runs the macro without first selecting the address, a blank address label will be printed. To guard against this possibility, we can modify the macro code by adding the following at the start of the procedure:

If Selection.Characters.Count < 20 Then
MsgBox ("You must select the address first.")
Exit Sub
End If

This code makes use of the fact that the Selection object contains a Characters collection that contains (you guessed it!) the characters, if any, currently selected in the document. On the assumption that even a short address will contain at least 20 characters, this code displays a message to the user and exits the macro if fewer than 20 characters are selected.

And here we come to the central theme of VBA programming: objects. The VBA language is essential, of course, but it is only part of the picture. If VBA development can be likened to building a brick house, then the VBA language is the mortar and the objects are the bricks. When you are working with Microsoft Office, you have an amazing and powerful set of bricks at your disposal—not to mention a whole range of prefabricated windows, doors, and walls.

The Office Object Model

There can be no doubt that Microsoft Office is the preeminent suite of business productivity applications, and the individual applications that make up Office have undergone a great deal of evolution over the years. While the improvements in functionality are obvious to anyone who uses the programs, there has been another equally important change going on behind the scenes, invisible to the typical end user. This is the continuing shift away from the old procedural model of application development to the new object-oriented model. With Office 2000, essentially all the programs’ functionality is encapsulated within objects. Furthermore, most of these objects are exposed and therefore available for use by other applications. These objects are the bricks of development with Office and VBA.

The objects that are exposed by Office applications are not limited to being used in a VBA application. A regular Visual Basic application can make use of them, as can programs created with any development tool that supports Microsoft’s Component Object Model (COM). You can, for example, write a regular Visual Basic application that makes use of Word objects for text processing or Excel objects for numerical analysis. This is all fine and good, but many developers prefer to create business solutions without leaving the familiar territory of Office. Microsoft refers to this as application programmability.

Each individual Office application has its own object model. In each case, the top level object is always Application, which represents the application itself and also subsumes all other objects exposed by that application. The first step in making use of an application’s object is usually to create an instance of its Application object:

Set myOutlookApp = CreateObject("Outlook.Application")
Set myExcelApp = CreateObject("Excel.Application")
Set myWordApp = CreateObject("Word.Application")

The second tier of objects within an application consists of relatively high-level objects related to the application’s main functions. Levels below the second tier consist of various objects that provide the functionality contained in the second tier objects. In many cases, multiple instances of objects are contained in collections. Word, for example, has a second-tier object named Document that represents a single Word document, and the Documents collection contains one Document object for each open document. Then, each Document object has a Paragraphs collection containing one Paragraph object for each paragraph in the document. Excel has a similar object organization. The Workbooks collection contains one Workbook object for each open workbook, and each Workbook object contains a Worksheets collection containing one or more Worksheet objects.

Collection Syntax

Collections are perhaps even more important in VBA than in Visual Basic, and whichever tool you are using, you need to be familiar with them. You can create your own collections, of course, but it is perhaps more important to be familiar with the built-in collections provided by the various applications. Collections are based on the Collection object, and collection syntax is standardized, which makes things a bit easier. A collection of objects is named as the plural of the object name. Thus, Word maintains a collection of Document objects which is called Documents, and contains one Document object for each open document. This is parallel to the way Visual Basic maintains a Forms collection, containing one Form object for each form in the application.

The Collection object has a set of methods and properties that you use to manipulate and get information about the collection contents. The Add method, for example, adds a new member to the collection, while the Count property returns the number of objects in a collection. It’s worthwhile studying collections, as you use them constantly in VBA programming.

Some objects do not belong to a specific Office application but are shared. This includes the Office Assistant (the animated character that provides help information, usually seen as Mr. Paper Clip), the Office Binder, Microsoft Map, Microsoft Graph, and the Visual Basic editor itself.

A very important part of the VBA/Office package is its data access tools. Microsoft has long recognized that data access is a central part of many business solutions, and we Visual Basic programmers have long been the beneficiaries of the emphasis that Microsoft has placed on providing powerful data tools. These same tools are available to the VBA/Office developer. Some are part of the Access object hierarchy, while others, such as ActiveX Data Objects (ADO) and Data Access Objects (DAO), are not specifically part of Access (but are better thought of as a part of Windows).

Let’s not forget Web technologies! The Internet Explorer 5 object hierarchy is available, along with its Document object model (different from that used by Word), as well as the scripting runtime, the Chart and Pivot Table components, Office Server Extensions, and a few other goodies.

Developing With Office and VBA

What exactly is the difference between a business solution (programmed as a standard Visual Basic executable that utilizes some of the objects exposed by Office) and a solution programmed within Office using VBA? In the former case, the end user sees—and the programmer must create—a typical Visual Basic program, based on forms, using a combination of Visual Basic objects and Office objects to provide the needed functionality. In the latter case, the end user works within one of the Office applications—typically the one most closely related to the task at hand—which has been extended and integrated with the capabilities of one or more other Office applications (by means of its exposed object) to provide the needed functionality. For example, an Office VBA application could do the following:

1. Upon opening an Excel spreadsheet, import the latest company sales data from an Access or SQL database on the network.
2. Perform numerical calculations to create sales projection figures for the next month.
3. Use PowerPoint objects to create a formatted presentation of the projections.
4. Use Outlook’s email capabilities to send the projection presentation to a list of recipients.

The developer’s job is to use VBA to combine and integrate the capabilities exposed by the Office applications, rather than to create an entire new application. At the risk of oversimplifying, I’ll say that the traditional Visual Basic executable approach is more flexible and also more difficult. The VBA approach is easier and quicker, but you are somewhat constrained by the capabilities of the objects exposed by Office. With over 600 exposed objects, this is rarely a problem as long as you are using Office to develop solutions appropriately—in other words, for typical business and data-related needs.

A completed VBA project will almost always have, as the main visual interface, the “normal” screen interface of the Office application, such as Excel or Word. You are not limited to this, however. A VBA project can include one or more User Forms, which are in some ways analogous to regular Visual Basic forms. A User Form displays as a window, or more accurately as a dialog box. You can place a variety of controls on it, including most of the intrinsic controls we are used to from Visual Basic (Text Box, Picture Box, Combo Box, etc.), as well as ActiveX controls registered on the system. A User Form contains code as well, consisting of event procedures for the User Form and its controls as well as Basic procedures. When the native visual interface of an Office application does not meet your project’s needs for presenting information to, or getting information from, the user, then you can “roll your own” with a User Form.

A VBA/Office project will also contain one or more modules, which are almost exactly analogous to Visual Basic code modules. It’s here that your macro subprocedures live (the ones you actually call from an Office application) and also your global variable declarations, types, enums, API function declarations—generally all the same stuff you put in a Visual Basic code module.

Finally, VBA supports class modules in a manner that seems to be exactly parallel to that of Visual Basic. In fact, you can, with the proper precautions, reuse classes created for your Visual Basic projects in a VBA project.

The task of developing a VBA/Office project is performed mostly in the VBA editor, shown in Figure 1. You can access the VBA editor from any Office application by pressing Alt+F11. It is quite similar to the regular Visual Basic development environment, with a Project Explorer window, a Properties window, and a code editing window. When you design a User Form, the code editing window is replaced by the visual designer. There are a few differences, of course—for example, there is no “Make EXE” command—but generally speaking, if you are comfortable in Visual Basic, you should not have any trouble with the VBA editor. Fortunately, all the debugging and editing tools that help make a Visual Basic programmer’s life go more smoothly are also present in the VBA editor.

Figure 1. The VBA editor.

 

Microsoft Office Developer’s Edition

You do not need any special software to create VBA/Office applications. All the objects are, by necessity, included in any Office installation, as is the VBA editor. Even so, Microsoft created a special Developer’s Edition of Office that contains some extra goodies that you’ll want if you go beyond simple Office/VBA apps. It includes Office 2000 Premium (all six Office applications), plus a variety of tools including:

• Data connectivity components: Data Environment Designer, Binding Collection Manager, and Data Controls.
• Visual Source Safe: Source code management for multi-developer projects.
• Answer Wizard Builder: Create natural language query capability.
• Replication manager: A visual tool for replicating and converting databases and viewing relationships in them.
• VBA COM Add-in Designer: A template for creating Common Object Model add-ins for Office 2000.
• Microsoft Developer Network: Extensive on-line help and documentation for Office/VBA development tools.

A Simple Demonstration

They say “a picture is worth a thousand words,” and when learning how to program, the same is true of a working application. By giving you a small taste of how Office and VBA can work together, I can give you a real idea of the capabilities of this combination. You can, of course, create tremendously complicated applications using Office and VBA, but I do not want to bore you with a long project you’ll never use. Rather, I’ll demonstrate something relatively simple but also quite useful: how to compose an email message in Word and then use Outlook to send it to everyone in your address book. The fact that this requires only about a dozen lines of code should suggest to you the power of VBA when combined with Office objects.

To write a procedure such as this, you would start in the most directly relevant Office application; in the case, Word. Press Alt+F11 to open the VBA editor, and then check to see if the default project already has any code modules associated with it. If so, that’s where the new procedure will go—otherwise select Insert|Module from the menus to create a new module. Then, select Insert|Procedure and add a public Sub named SendToEveryone. (Sounds a lot like adding a Sub to a regular Visual Basic project, no?)

In the new procedure, the first step is to create an instance of the Outlook application object, and then to use that object to access a particular address book (in this case called “Contacts”—use the name of your Outlook address book in its place):

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNameSpace("MAPI")
Set myAddressList = myNameSpace.AddressLists("Contacts")

Once you have a reference to the address list, you can retrieve the collection of address entries in the list:

Set myAddressEntries = myAddressList . AddressEntries

To obtain a subject for the messages, use the InputBox function that works identically to the one found in Visual Basic:

Subject = InputBox("Subject for this message?")

To create one message for each address entry, we set up a For…Next loop:

For i = 1 To myAddressEntries.Count
...
Next i

Within the loop, for each message, we start by creating a new Outlook message. Note that this task is performed by the Outlook instance we created earlier:

Set NewMessage = myOlApp.CreateItem(olMailItem)

Then, set the new message’s subject, its content (obtained from the active Word document), and its recipient address (obtained from the current entry in the myAddressEntries collection):

NewMessage.Subject = Subject
NewMessage.Body = ActiveDocument.Content.Text
NewMessage.To = myAddressEntries(i).Address

Finally, send the message (which places it in Outlook’s Outbox) and destroy the mail item in preparation for looping back and creating another one:

NewMessage . Send
Set NewMessage = Nothing

After entering the code and saving the module, return to Word. Press Alt+F8 to display the Macros dialog box and you’ll see that SendToEveryone is listed along with any other current macros. Select it, and then click Run. (Be sure the document is not empty or you’ll send a bunch of blank messages!) You’ll be prompted for a subject, and then the macro will create a new Outlook email message for each entry in your address book. Depending on your Outlook settings, the messages will be sent immediately or will remain in the Outbox to be sent later.

Again, this is a pretty simple example of what VBA and Office can do. Much of what is involved in learning to use this development combination consists of becoming familiar with the object models of the various Office applications. Of course, the Object Browser is a great help here.

Am I advocating abandoning Visual Basic and using Office and VBA as your only development tool? Not on your life! I will, however, argue that the Office/VBA combination is a tool that should be in every Windows developer’s toolbox. It is not appropriate for all or even most development projects, but it does provide a great deal of power that can be the fastest technique to realize certain solutions. Also, as a Visual Basic programmer, you have an edge with Office/VBA because you already speak the language. v

---

During daylight hours, Peter is a research scientist at Duke University Medical Center in Durham, North Carolina. Comments and suggestions may be sent to him at paitken@acpub.duke.edu

Copyright © 1999 The Coriolis Group, LLC. All rights reserved.