September/October 1999
by Peter G. Aitken (c)1999
Originally published in Visual Developer magazine
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.
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.
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.
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.
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.
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.
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.