Understanding Object Binding in VBA

Microsoft’s Office products allow you to create powerful macros using Visual Basic for Applications (VBA) in an object-oriented environment. Much of the power presented by VBA is in its ability to use third-party and COM objects to expand upon the codebase that the environment presents. Unfortunately, this is also a common point of confusion for many VBA developers. Today I’m going to give a little “object lesson” to better help you understand how objects work and the different ways of implementing them in your code.

Before we begin, there are a few concepts that you need to understand.  Objects are nothing more than external code classes that can be made available for extending your own application.  For example, if I were writing a simple calculator application, I might use a third-party math object that contained common mathematical operations.  Making use of this pre-written object would prevent me from having to create all of those math functions myself, allowing me to develop my application much more quickly.

From a programming standpoint, objects represent code classes.  A class is simply a group of related properties, methods, and events that are often reusable in other, future applications.  In the past, developers would have to save these classes and maintain their own libraries, where they could cut and paste code they had written in the past into a new application.  As application development evolved over time, this process was simplified into a public system known as the Component Object Model, or COM.

The Component Object Model provided a means for developers to create and share reusable objects, or code classes.  The most common of these COM objects are code pieces used by the Windows operating system that Microsoft has made available to programmers developing software for the Windows platform.  However, many third-party software packages will also install COM objects that other developers may build their own applications around.

{mospagebreak title=Early Binding}

There are two distinctly different ways to implement objects in VBA.  The first is called Early Binding and is the most common way demonstrated by the Microsoft documentation.  It’s also the most highly recommended, but I’ll explain a little later on in this article why that’s not always a wise choice.

So what exactly is object binding anyway?  As I said before, objects just represent code classes.  In order to use that code in your own application, you must make your app aware of the code by telling it where to find it and how to interpret it.  The Component Object Model provides a system for doing this.  COM objects are registered to the system by adding registry entries that provide all of the required information so that a reference to this information is all that is required.

In the Visual Basic Editor, references are made by choosing References… from the Tools menu and selecting the COM component in the dialog box.  When a reference is added, the VBE will examine the type library associated with the object.  The type library informs the VBE of all publicly available properties and methods.  The VBE then makes that information available to its IntelliSense feature and the Object Browser.  Adding a reference will also allow you to reference an object in your code through its exposed interface.  Let’s take a look at an example.

In this example, I’m using the Visual Basic Editor in Microsoft Word 2007 to create a reference to the Excel 2007 application.  I begin by launching the Visual Basic Editor (pressing Alt+F11), choosing References… from the Tools menu and selecting Microsoft Excel 12.0 Object Library.  This adds a reference to my project and exposes an interface named Excel to my application.  I can then instantiate the object using the following syntax.

Dim objExcel As Excel

Set objExcel = New Excel

Instantiating objects in VBA requires two lines.  The first line is the Dim statement, which is used to create the variable that will hold the object.  The second is the Set statement, which actually instantiates the object.  Let’s begin with the Dim statement.

As you are probably well aware, Dim statements are used to declare variables in VBA.  You must also declare a type for that variable.  Since an object is not a primitive VB data type, we must specify what kind of object the variable will hold.  Since the VBE had loaded the type library for the object we intend to use, you can use the object’s interface as the object type.  As you recall in our example, the Microsoft Excel 12.0 Object Library exposes the Excel interface.  The Set statement is then used to instantiate the object through its exposed interface with the New keyword.

{mospagebreak title=Late Binding}

The second method of instantiating objects is known as late binding.  In late binding, a reference to the object is not provided to the application; instead, instantiation is made through an object’s IDispatch interface at the time of execution.  This is the type of object instantiation used by the Windows Script Host environment, which doesn’t provide support for object references.

Since the object’s type library is not processed prior to execution, the object’s exposed interfaces are not available.  This means two things as far as we’re concerned.  First, we cannot declare a variable of that type.  Second, we will not be able to instantiate the object by its interface.

Since the variable we’ll be using for our object cannot be declared by the object type, we’ll simply declare it as a generic type Object.  Without an interface by which to instantiate it, we’ll forgo the New syntax and use VBA’s CreateObject method instead.  To do that you’ll need to know the ProgId for the COM object you wish to use.  In this case, the ProgId for the Microsoft Excel Object Library is “Excel.Application.”

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

Those familiar with the Windows Script Host will find this syntax very familiar, as it is quite close to its WSH equivalent.

Instantiating objects in this way negates any need to have references in your application, but it also provides a few other distinct advantages.  Let’s take a moment to examine the pros and cons of both early and late binding so you know when to use each of them.

{mospagebreak title=An object lesson}

Early and late binding each come with their own advantages and disadvantages.  In order to choose between the two, you’ll need to take your application’s purpose into consideration.

Early binding will load all of the information for an associated object prior to code execution.  This makes early binding more efficient than late binding, which means faster code execution.  It also means that the object’s properties and methods will be available to the Visual Basic Editor’s IntelliSense feature and object browser, which makes writing code faster.  Finally, VBA supports constant enumerations for early bound objects, meaning that you will not need to define an object’s constants in order to use them in your code. 

Early binding’s disadvantage is that it requires a Reference in your application.  This means that your code cannot simply be copied and pasted. References also attach specific versions of a particular COM object.  So moving your code from one machine to another may or may not work.

The advantage of late binding is that there is no need to create a Reference in your application to use an object.  While you will sacrifice a little speed, the code in your application is fully portable from one machine to the next because you will be instantiating your objects by using their version-independent ProgId.  You will also need to define any required constants in your code, since VBA does not support constant enumeration for late bound objects.

So which one should you choose?  If you are developing code for a single machine or writing code for an object with which you are unfamiliar, you should choose early binding.  Thus, you’ll be able to take advantage of better performance and all of the VBE’s coding help.  However, if you intend to use your code across several machines or provide the snippet for others to use, you should use late binding to help ensure compatibility across different code versions.

I hope that this article has helped you gain a better understanding of object binding in VBA.  Learning when to properly use early or late binding of objects brings you one step closer to being a more proficient and better prepared programmer.  Until next time, keep coding!

[gp-comments width="770" linklove="off" ]