Beyond Visual Basicby Peter G. Aitken
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 developersparticularly those reading this magazinewill 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? Dont 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. Youll be surprised at the programming power youll find.
I am, of course, talking about Visual Basic for Applications, or VBAthe 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 VBAs 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 applications objects.
Whats Inside the Box?
Lets 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:
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 shouldnt 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 arent 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 macros 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:
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 disposalnot 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 Microsofts 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 applications object is usually to create an instance of its Application object:
The second tier of objects within an application consists of relatively high-level objects related to the applications 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.
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).
Lets 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 seesand the programmer must createa 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 applicationstypically the one most closely related to the task at handwhich 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:
The developers 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, Ill 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 appropriatelyin 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 projects 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. Its 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 declarationsgenerally 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 coursefor example, there is no Make EXE commandbut 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 programmers life go more smoothly are also present in the VBA editor.
Figure 1. The VBA editor.
Microsoft Office Developers 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 Developers Edition of Office that contains some extra goodies that youll want if you go beyond simple Office/VBA apps. It includes Office 2000 Premium (all six Office applications), plus a variety of tools including:
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 youll never use. Rather, Ill 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, thats where the new procedure will gootherwise 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 Contactsuse the name of your Outlook address book in its place):
Once you have a reference to the address list, you can retrieve the collection of address entries in the list:
To obtain a subject for the messages, use the InputBox function that works identically to the one found in Visual Basic:
To create one message for each address entry, we set up a For Next loop:
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:
Then, set the new messages subject, its content (obtained from the active Word document), and its recipient address (obtained from the current entry in the myAddressEntries collection):
Finally, send the message (which places it in Outlooks Outbox) and destroy the mail item in preparation for looping back and creating another one:
After entering the code and saving the module, return to Word. Press Alt+F8 to display the Macros dialog box and youll 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 youll send a bunch of blank messages!) Youll 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 developers 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 email@example.comCopyright © 1999 The Coriolis Group, LLC. All rights reserved.