Basically Visual

September/October 1999

by Peter G. Aitken (c)1999

Originally published in Visual Developer magazine


Beyond Visual Basic

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 some other language! I am, however, interested in exploring the capabilities of Visual Basic wherever it leads me, and that sometimes means going outside the tradition 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 applications 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 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 Office: Word, Excel, Access, Outlook, PowerPoint, and FrontPage. VBA is also 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, 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.

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 do is select the address in the document, 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.

Listing 1. A Word macro to print a shipping label.

Sub ShipLabel()

'  
' ShipLabel Macro  
' Print Shipping Label  
' 
   
Selection.Copy 
   
Documents.Add Template:="C:\documents\Templates\home_ship.dot", _ 
       
NewTemplate:=False, DocumentType:=0 
   
Selection.MoveDown Unit:=wdLine, Count:=3 
   
Selection.Paste 
   
ActivePrinter = "CoStar LabelWriter" 
   
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, _ 
       
Item:=wdPrintDocumentContent, Copies:=1, Pages:="", _ 
       
PageType:=wdPrintAllPages, _Collate:=True, Background:=True, _ 
       
PrintToFile:=False, PrintZoomColumn:=0,PrintZoomRow:=0, _ 
       
PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0 
   
ActiveDocument.Close 
   
ActivePrinter = "HP LaserJet 3100"  

End Sub

You can see that 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. Any 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.

If you examine 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 exactly right. 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 and 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. If however 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 which contains (you guessed it!) the characters, if any, that are 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.

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 that you 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. If you are not already familiar with Collection object syntax, you should be because it is an important part of VBA programming.

 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 do 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 and not visible to the typical user. This is the continuing shift away from the old procedural model of application development to the new object model. As a result, with the current version of Office (Office 2000) essentially all of the programs' functionality is encapsulated within objects. Furthermore, most of these objects are exposed and therefore available for use by other applications. It is these objects that are the bricks of development with Office and VBA.

Needless to say, 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 the Component Object Model (COM). You can, for example, write a regular Visual Basic application that makes use of some Word objects for text processing, or some Excel objects for numerical analysis. This is all fine and good, but many developers prefer to create business solutions without either they or their end users 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.

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), 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 of them are part of the Access object hierarchy, while other 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 is always working 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 application (by means if 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 e-mail 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 your 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 that the end user sees, the "normal" screen interface of one of the Office applications, 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 a dialog box, and you can place a variety of controls on it, including most the intrinsic control we are used to from Visual Basic (Text Box, Picture Box, Combo Box, etc.) as well as ActiveX controls that are 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 go, the ones you actually call from an Office application, and also your global variable declarations, types, enums, API function declarations - generally 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 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. 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 are designing 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 of the debugging and editing tools that help make a Visual Basic programmer's life go more smoothly are also present in the VBA editor.

 Microsoft Office Developer's Edition

 You do not need any special software to create VBA/Office applications. All of the objects are, by necessity, included in any Office installation, as is the VBA editor. Even so, Microsoft has created a special Developer's Edition of Office which contains some extra goodies that you'll want to have 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 e-mail 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, 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 here):

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 it's 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 then click Run (be sure the document is not empty or you'll be sending a bunch of blank messages!). You'll be prompted for a subject, then the macro will create a new Outlook e-mail 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 for certain solutions. Also, as a Visual Basic programmer you have an edge with Office/VBA because you already speak the language.

Service Pack 3

Microsoft has released service pack three for Visual Studio, which of course includes Visual Basic. It is worthwhile to install this service pack because it addresses quite a few known problems with Visual Basic, and while you may not have encountered these problems yet you may run into them down the road at some time. You can get the download at http://msdn.microsoft.com/vstudio/sp/dnldoverview.asp. It's a huge download, sizing in at some 54 MB even if you just get the Visual Basic parts. Clearly this is not something you want to do over a modem! Fortunately you can order it on a CD. Note that Visual Studio Enterprise Edition customers as well as some MSDN subscribers will receive the service pack automatically.