Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Fundamentals

Controlling another application is only a matter of knowing how to connect to the target application and efficiently use its object model. This part of the chapter discusses the fundamentals of connecting to other applications and best practices for using third-party object models.

Automation

Automation is Microsoft's generic term for technology that allows one application to manipulate another application's objects, including allowing VBA to manipulate Excel. Automation began with a technology called OLE (Object Linking and Embedding) and has since evolved into other forms such as COM (Component Object Model), also known as ActiveX, and DCOM (Distributed Component Object Model).

The application that manipulates the objects is called the automation client or host application. Applications whose objects are manipulated are called automation servers or target applications.

All of the Office applications can be used as automation servers so long as they've been properly installed (registered) on the computer. Microsoft Office applications are automatically registered during installation, but can be re-registered using the /regserver command-line switch.

Referencing

The easiest way to get started is to create a reference to the object library for the application that we want to automate using the Tools > References menu in the VBE. In Figure 18-1, we've added a reference to the Word 2000 object library, as indicated by the 9.0 version number in the reference description.

Figure 18-1. Adding a Reference to the Word Object Library

With the reference set, we can use the objects in the Word object model as if they were classes in our project, as shown in Listing 18-1, which uses the New keyword to create a new instance of the Word application.

Listing 18-1. A Simple Procedure to Control Word

Sub ControlWord()
'Declare an object variable to reference
'the Word application
Dim wrdApp As Word.Application
'Start a new instance of Word
Set wrdApp = New Word.Application
'Do something
'Close Word and tidy up
wrdApp.Quit savechanges:=False
Set wrdApp = Nothing
End Sub

Development Best Practices

In addition to the advice given in Chapter 3 Excel and VBA Development Best Practices, some additional techniques should always be used when automating other applications.

Always Include the Object Library in Variable Declarations

Whenever we declare a variable as a specific object type, such as Dim rngData As Range, the VBA interpreter scans through the object libraries referenced in the Tools > References list (in the order shown in that list) until it finds an object with the same name as specified in the variable declaration. In this case, the first object library to contain an object called Range is the Excel object library, so our variable is typed as an Excel Range object. In most cases, that's exactly what we want to happen. Problems arise, though, if we're referencing multiple object libraries that use the same name for their own objects. For example, Excel and Word both have Range objects, but they're very different. To make sure the interpreter uses the Range object from the correct library, we should always include the library name in the variable declaration, as shown in Listing 18-2.

Listing 18-2. Declaring Objects with the Correct Object Library

Sub GetRanges()
'An Excel Range
Dim rngData As Excel.Range
'A Word Range
Dim wrngTitle As Word.Range
End Sub

In addition to explicitly telling VBA which Range object we want a reference to, fully qualifying our object declarations also makes our code much easier to understand; when we see As Word.Range, we have the mental prompt that the Word object library is being used. This prompt is carried through to the variable name, where we've included a w prefix to indicate an object from the Word object library.

Always Fully Qualify Property and Method Calls

All of the Office object libraries include some global properties that we often use as shortcuts into the object model, such as Excel's ActiveSheet, ActiveCell, Selection and Word's ActiveDocument, Selection and so on. Whenever we use any such property in cross-application development, we must always provide a fully qualified object reference that can be traced back to the original variable we used to reference the application. Listing 18-3 shows the correct way to get a reference to the ActiveDocument in a Word instance that we're controlling.

Listing 18-3. Referring to the Active Document

Sub GetActiveDoc()
'Declare an object variable to reference
'the Word application
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
'Start a new instance of Word with a blank document
Set wrdApp = New Word.Application
'Do something that opens or creates a document
'Get a reference to the active document
Set wrdDoc = wrdApp.ActiveDocument
'Close Word and tidy up
wrdApp.Quit savechanges:=False
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub

If we omitted the wrdApp. in the highlighted line, the VBA interpreter will try to find a Word document in any instance of Word the user might have open, which may not be the instance we're controlling. By providing the wrdApp. reference, we're explicitly telling the interpreter to return the active document in the instance of Word we've created.

Develop Using the Earliest Version You'll Support

For reasons explained later, references that we declare to Office object libraries are forward compatible but not backward compatible. This means that if we save our workbook with a reference to the Word 2003 object library, anyone opening the workbook on a PC with only Office 2000 installed will receive a compile error "Can't find project or library" as soon as the code is run. If we save our workbook with a reference to the Word 2000 object library, it will work correctly on any machine with Office 2000 or any later version.

Every version of Office adds more features to each application and correspondingly more objects, methods, properties and optional parameters to the application's object library. By developing using the earliest version that we intend to support, we stop ourselves from accidentally using any of the more recent objects, methods, properties or parameters. If we were to develop using the latest version of the application, we would not discover our accidental use of the newer objects until we were well into our testing, or even after deployment.

Group Routines in Application-Specific Modules

The VBA interpreter/compiler compiles our code on a module-by-module basis. A module is compiled when code contained in that module is first run. If we have a reference to an object library that isn't registered correctly, the compiler will not be able to compile the module that contains code that uses objects in that library and we'll get a compile error. This issue can be partially mitigated by ensuring that all the code that controls another application is contained in a single module dedicated to that purpose. The module should not contain any procedures used by other parts of the application. If we're automating multiple applications, the routines for each application should be in their own modulesone for Word, one for Access, one for PowerPoint and so onwith each module having a descriptive name such as MWordCode. With all the application-specific code contained in a single module, we can check whether the application is installed correctly and safely use or avoid that module, as shown later.

The vTable and Early vs. Late Binding

Every COM object has a structure called a vTable, or virtual function table, which lists all its properties and methods along with the memory addresses where their entry points are located and the parameters they take. When we declare a variable as a specific object data type, the compiler can look up the addresses of all the property and method calls for that object in its vTable at compile time. It can then store jump to instructions that identify the entry points of those property and method calls directly in our code. When the code is executed and VBA encounters one of these property or method calls, it just runs the code at the memory location specified by the stored jump to instruction. This is known as early binding.

Early binding requires us to set a reference to the object library through the Tools > References menu, so VBA can include that object library's GUIDs and other type information in our project. (A GUID is a unique 128-bit number used to identify all COM objects.) When our project is run on a different computer, VBA verifies that an object with the same GUID as the one we originally referenced is available there. If so, it means this is the same object as the one we originally referenced so it will have the same vTable. Therefore, all the direct memory jump instructions that were compiled into our application for that object can be trusted. If the GUID is not found on the client computer, VBA knows that the object we want to use is not available and so gives us a compile error (and marks the reference as MISSING in the Tools > References list). This is why MISSING object library references caused by referenced COM components not being installed on a user's computer will stop a VBA application dead in its tracks.

When we declare a variable as the generic Object data type, the compiler doesn't know which vTable to use. Therefore, it cannot determine the memory location of that object's properties and methods and it cannot compile jump to instructions for them into our code. Instead, after the generic object variable has been set to reference a specific object at run-time, VBA checks the vTable of that object every time it encounters one of the object's property or method calls in order to locate the memory address to jump to. This is known as late binding. The continual vTable lookups can have a significant impact on performance, but they don't require us to set a reference to the object library we're controlling. This results in any referencing problems (such as a missing object library) appearing as runtime errors (which we can handle gracefully) rather than compile errors.

The vTable also explains why Office object libraries are forward compatible, but not backward compatible. In each new version of an Office application the vTable is extended with new property and method entries, but the existing sections are not changed. This makes it safe to use an earlier version vTable entry to call into a later version of the same application, but not vice versa. The first section of the later version's vTable is identical to the earlier version's vTable, but it also contains additional entries that do not appear in the earlier version. If VBA attempted to execute a property or method call identified by an entry late in the Word 2003 vTable while running under Word 2000, for example, that entry wouldn't exist in the Word 2000 executable and our application would crash.

As well as controlling whether we can use early binding, adding a reference to an object library also controls whether we can use the constants and parameter names defined in the library, as shown in Listing 18-4.

Listing 18-4. Early vs. Late Binding

'Early-Bound
'Requires a reference to the Word object library,
'but allows us to use specific object types,
'named parameters and defined constants
'and gives us IntelliSense information
Sub EarlyBound(wrdApp As Word.Application)
'Open a text file
wrdApp.Documents.Open FileName:="c:\myfile.txt", _
Format:=wdOpenFormatText
End Sub
'Late-Bound
'Have to use the generic Object type,
'can't use named parameters or defined constants,
'don't get IntelliSense information,
'but doesn't require a reference either.
Sub LateBound(wrdApp As Object)
'Open a text file
wrdApp.Documents.Open "c:\myfile.txt", , , , , , , , , 4
End Sub

The key factor in choosing between early or late binding is the likelihood that the applications we're controlling are available and installed correctly on the users' computers. We can ensure the applications are installed correctly on our computers, so we should always use early binding during development. That allows us to use the IntelliSense information, object types, constants and named parameters which together make early-bound code much easier to develop, read, debug and maintain.

Before distributing our application to our users, we need to decide whether to switch to using late binding. This will usually depend on both the likelihood that the applications are available and the amount of code that calls the application. The fundamental advantage of using late binding is that we can easily handle a failure to link to the object we want to control (see later for an example). In the case of Excel automating Word within a company environment, it's highly likely that anyone with Excel installed will have Word installed as well, so it's probably safe to stay with early binding. The same can't be said when automating, say, FrontPage, so it would probably be best to switch to late binding for that. If we only have a few lines of code, it's safest to always use late binding. With lots of code, the inability to use named parameters and defined constants when late binding can make our applications much harder to maintain.

Handling Instances

Before we can use an application's features, we need to connect to an instance of the application. We can either hijack an instance the user might already have open or create a new instance for our dedicated use. Unless there is a specific need to link to the instance that the user is working with, we should always create our own instances, use them and close them when we're finished. This is mainly because the user may have left the instance they're using in a state that would cause errors in our application if we tried to use it, such as having a modal dialog displayed. This could either prevent our application working correctly, or worse, result in our application interfering with the work the user is doing in that instance.

Create a New Instance

We can use either the New keyword or CreateObject function to create a new instance of an application, as shown in Listing 18-5. The New keyword can only be used if we have set a reference to the type library (synonymous with object library), while the CreateObject function can be used either with or without a reference. The manner in which an application is started does not determine whether we're using early or late binding. Rather, the opposite is true; our choice of binding determines whether we can use New or CreateObject. Although using the New keyword is slightly faster than CreateObject, it is our opinion that CreateObject should always be used, as it is one less thing to change if we choose to switch between early and late binding.

Listing 18-5. Creating a New Instance of Word

Sub StartWord()
'Early bound
Dim wrdApp1 As Word.Application
Set wrdApp1 = New Word.Application
'Early bound
Dim wrdApp2 As Word.Application
Set wrdApp2 = CreateObject("Word.Application")
'Late bound
Dim wrdApp3 As Object
Set wrdApp3 = CreateObject("Word.Application")
End Sub

Table 18-1 lists some of the Office application class names used by the CreateObject function.

Properly Tidying Up

Whenever we create a new instance of an application, we must ensure that we close it correctly. In most cases, this is just a matter of calling the application's Quit method and then destroying any variables that we may be using to reference it. We must be particularly careful with error handling, to ensure that the application we're controlling is correctly shut down in the case of an error, as shown in Chapter 12 VBA Error Handling.

Listing 18-6. Starting and Closing Word, with Error Handling

Sub ControlWord()
Const sSOURCE As String = "ControlWord"
Dim wrdApp As Word.Application
On Error GoTo ErrorHandler
'Start Word
Set wrdApp = CreateObject("Word.Application")
'Do something here
ErrorExit:
'The tidy-up code is performed
'whether or not we get an error
If Not wrdApp Is Nothing Then
'Close Word, ignoring any errors
'Without On Error Resume Next, an error would
'cause an endless loop in the error handler.
On Error Resume Next
wrdApp.Quit savechanges:=False
On Error GoTo ErrorHandler
'Tidy up
Set wrdApp = Nothing
End If
Exit Sub
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub

Reference an Existing Instance

It is nearly always best to create a new instance of an application for our program to use. A notable exception is when controlling Outlook, because it only allows a single instance to be running at any one time; using either the New keyword or CreateObject function to create an instance of Outlook only creates an instance if Outlook is not already running. If Outlook is already open, a reference to that instance is returned. This behavior can cause us problems when we've finished using the instance we asked for, as we won't know whether or not we should shut Outlook down. If Outlook was already running when we asked for our instance, we should leave it running. If it wasn't already running, we should close it. We can use the GetObject function to obtain a reference to an existing instance of an application, then use CreateObject if the GetObject call fails, as shown in Listing 18-7. In either case, we set a Boolean variable that we use to see whether we should close Outlook when tidying up.

Listing 18-7. Checking for, Starting and Closing Outlook, with Error Handling

Sub ControlOutlook()
Const sSOURCE As String = "ControlOutlook"
Const sOUTLOOK_APP As String = "Outlook.Application"
Dim olkApp As Outlook.Application
Dim bOutlookCreated As Boolean
'Try to get a reference to Outlook
On Error Resume Next
Set olkApp = GetObject(, sOUTLOOK_APP)
On Error GoTo ErrorHandler
If olkApp Is Nothing Then
'Start Outlook
Set olkApp = CreateObject(sOUTLOOK_APP)
bOutlookCreated = True
End If
'Do something here
ErrorExit:
'The tidy-up code is performed
'whether or not we get an error
If Not olkApp Is Nothing Then
If bOutlookCreated Then
'Close Outlook, ignoring any errors
On Error Resume Next
olkApp.Quit
On Error GoTo ErrorHandler
End If
'Tidy up
Set olkApp = Nothing
End If
Exit Sub
ErrorHandler:
If bCentralErrorHandler(msMODULE, sSOURCE) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Sub

When we use CreateObject to start a new instance of PowerPoint, we actually get a reference to an existing instance if there is one already running. The Presentations collection includes all the open presentations from all the "instances" that have been created. This is similar to Outlook's behavior, but PowerPoint also tidily handles calls to Application.Quit by closing only the presentation and not the entire application. We can therefore safely use CreateObject to start PowerPoint and Application.Quit to close it.

Multiversion Support

It is a fact of Excel application development that we can expect our users to have a variety of Office versions, typically going back as far as Office 2000. We explained previously how the vTable allows object libraries to be forward compatible but not backward compatible. This means if we save our workbook containing a reference to Word 2003, it will give a compile error when run on a machine with only Office 2000 installed.

One solution to this problem is to save our workbook with a reference to the earliest version of the object library we intend to support. When a workbook saved with a reference to Word 2000 is run on a machine with Office 2003, the reference will automatically be updated to Word 2003. Unfortunately, if that workbook is then saved, the reference to Word 2003 will remain. If the workbook is forwarded to our Office 2000 user, we'll get the compile error again. This technique is therefore only suitable for workbooks that our users won't need to save, such as add-ins and the code workbooks of dictator applications.

If we put our code and UI in separate workbooks, it is probably only the UI workbook that will need to be saved by our users, thereby avoiding this problem. The safest solution, though, is to use late binding and save our workbooks without any reference to Word. When run, we can check the availability and version of Word and run the code appropriately.

Determining the Availability of an Application

The function shown in Listing 18-8 checks whether an application is installed by simply trying to start it. If the application starts successfully, the function returns a reference to it via the objApp parameter. Note that this function can (and should) be used regardless of whether we're late binding or early binding. Even if an object library exists and is registered, there is no guarantee that the application will start correctly.

Listing 18-8. Checking for an Installed Application

Function bIsAppAvailable(ByVal sClass As String, _
ByRef objApp As Object) As Boolean
On Error Resume Next
Set objApp = CreateObject(sClass)
bIsAppAvailable = (Not objApp Is Nothing)
End Function

Performance

VBA calls between applications, such as Excel controlling Word, are extremely slow, even if we're using early binding. To improve performance, we need to keep such calls to a minimum, using With blocks and object variables to refer to items deep in the object model. For best performance, we should move the code into the target application. For example, Listing 18-9 uses Excel to populate a number of Word bookmarks, with all the code contained in Excel. In Listing 18-10, we have moved the code that populates the document into a Word template, which is opened and called from our Excel code. Although this is a trivial example, the technique can result in a significant performance improvement in more complex situations. These examples can be found on the CD in the \Concepts\Ch18Controlling Other Office Applications folder and comprise the following:

PopulateWord.xls An Excel workbook containing both PopulateWordDoc procedures

Bookmarks.dot A simple Word template with some bookmarked text to update

FillDocument.dot A Word template containing the code from Listing 18-10

Listing 18-9. Populating a Word Document Entirely from Excel

'In an Excel module, with a reference to Word
Sub PopulateWordDoc1()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim sPath As String
Dim vaBookmarks As Variant
Dim lBookmark As Long
'Fill the Bookmarks array from the sheet
vaBookmarks = wksBookmarks.Range("rngBookmarkList").Value
'Start Word
Set wrdApp = CreateObject("Word.Application")
'Open the template to populate
sPath = ThisWorkbook.Path & "\"
Set wrdDoc = wrdApp.Documents.Add(Template:=sPath & _
"Bookmarks.dot")
'Populate the bookmarks in the template from the array
For lBookmark = LBound(vaBookmarks, 1) To _
UBound(vaBookmarks, 1)
wrdDoc.Bookmarks(vaBookmarks(lBookmark, _
LBound(vaBookmarks, 2))).Range.Text = _
vaBookmarks(lBookmark, UBound(vaBookmarks, 2))
Next lBookmark
'Save the filled document and close it
wrdDoc.SaveAs sPath & "Filled1.doc"
wrdDoc.Close
Set wrdDoc = Nothing
'Close Word
wrdApp.Quit False
Set wrdApp = Nothing
End Sub

Listing 18-10. Populating a Word Document Using Code in Word

'In a module in the Word template FillDocument.dot
Public Sub FillDocument(ByVal sTemplateName As String, _
ByVal sSaveName As String, _
ByVal vaBookmarks As Variant)
Dim docToFill As Document
Dim lBookmark As Long
Set docToFill = Documents.Add(Template:=sTemplateName)
For lBookmark = LBound(vaBookmarks, 1) To _
UBound(vaBookmarks, 1)
docToFill.Bookmarks(vaBookmarks(lBookmark, _
LBound(vaBookmarks, 2))).Range.Text = _
vaBookmarks(lBookmark, UBound(vaBookmarks, 2))
Next lBookmark
docToFill.SaveAs sSaveName
docToFill.Close
End Sub
'In an Excel module, with a reference to Word
Sub PopulateWordDoc2()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim sPath As String
Dim vaBookmarks As Variant
'Fill the Bookmarks array from the sheet
vaBookmarks = wksBookmarks.Range("rngBookmarkList").Value
'Start Word
Set wrdApp = CreateObject("Word.Application")
'Open the template containing our controlling code
sPath = ThisWorkbook.Path & "\"
Set wrdDoc = wrdApp.Documents.Open(sPath & _
"FillDocument.dot")
'Run the code within Word, passing all required information
wrdApp.Run "FillDocument", sPath & "Bookmarks.dot", _
sPath & "Filled2.doc", vaBookmarks
wrdDoc.Close
Set wrdDoc = Nothing
wrdApp.Quit False
Set wrdApp = Nothing
End Sub