There are two different ways of implementing OLE Automation: VTBL (virtual method table, pronounced V-Table) binding, and the IDispatch (pronounced EYE-Dispatch) interface. Using VTBL binding is similar to programming the speed dialer on your phone. You look up the number once and program it into the phone. Once you verify that it's valid, a single button dials the number. All the work of looking up the phone number is handled long before you use the phone number. Using the IDispatch interface is similar to looking up the phone number in a phone book before each call, and then dialing the phone. Both will get the intended results, but the speed dialer is much more efficient when you need to make the call. However, looking up the number each time verifies its correctness.
A VTBL is a data structure containing the addresses (pointers) for the methods and properties of each object in an Automation server. Using the VTBL is generally known as early binding in VBA. Frequently, early binding requires type information provided in the form of a type library. This type information allows VBA to perform compile-time syntax and type checking. At run time, this type of binding is faster, as the entry points for the Automation server are already known and the data types and syntax have already been verified.
Using the IDispatch interface is a way to call member functions within an object without knowing the exact location of their entries in the VTBL. An IDispatch implementation is frequently referred to as late binding. With IDispatch, VBA has no preexisting information about the server it will be calling. It assumes during its compile phase that the code is correct. It then attempts at run time to execute the code and trap for run-time errors. This is much less elegant and represents a step backward from the familiar syntax and type checking we have come to expect from VBA. An IDispatch call also requires both the client and the server to marshal their arguments into a consistent form that is understood by both. In most cases, this means that arguments are copied into and out of variants. This not only slows down IDispatch, but it also limits the data types that can be passed using this technique. In all future discussions VTBL binding will be referred to as early binding and IDispatch as late binding.
What does this mean to you as a developer using VBA? If possible you should attempt to early bind when using Automation objects from VBA. Early binding not only increases performance, but you get syntax and type checking at compile-time. If you can't early bind you must use late binding, which is slower and doesn't allow you to catch simple errors (such as type mismatches) until run time.
Early binding should be used whenever possible. However, there are programming situations in which late binding is preferred. If you are implementing polymorphic code (one call can represent multiple different objects), then late binding is required. For example, you can create three classes — widgets, gidgets, and gadgets — all with a Create method. By defining your variable as an object, you can call the Create method for any of the three classes based on run-time conditions.
When testing this type of late-bound strategy, it's generally best to test a single object, early bind to get the syntax and type checking, and then switch to late binding after you've tested each object individually.
What does early binding versus late binding look like in VBA? The main difference is in how you declare your variables. The following code snippet declares an object variable that refers to an instance of Microsoft Word. This example represents late binding, since we have used the generic Object data type. VBA doesn't know what type of object it is and will use late binding at run time to handle it. This is like running to the phone book before dialing the phone.
Dim wrd as Object
In the following code snippet, we declare the same object variable and specifically type it as Word.Application. This can be done only after we have referenced the Microsoft Word type library using Tools/References. Since VBA knows about the type of this object, it attempts to use early binding when dealing with it.
Dim wrd As Word.Application
How you declare the object variable is the factor that determines whether an object is early or late bound. How you create the object has no bearing on the type of binding VBA does, but it does have some performance implications.
To create an object, you can use either the New keyword or the CreateObject method. The CreateObject method is typically used when you don't know the Program ID (ProgID) of the object you are going to create until run time. Excel.Application is an example of a ProgID. Typically, a ProgID is composed of the application name followed by a period and then the class name (for example, Application.Class). To arrive at the ProgID for a particular object, use this method and then look at HKEY_CLASSES_ROOT in the registry to verify that you are correct. If you are correct, the ProgID should show up in this hive as a key. It's often assumed that if you use CreateObject you are using late binding, but this is not always the case. The declaration of the object variable is the overriding factor. CreateObject does have a slight performance disadvantage when compared to New. CreateObject assumes there is no reference to the object it is trying to instantiate and loads all the type information from the registry. This process is slower than using the type information available in a type library.
The New keyword has two syntaxes. The following example declares and instantiates the object in separate lines:
Dim wrd As Word.Application
Set wrd = New Word.Application
This is the recommended syntax. The following example shows how New can be used to declare and instantiate an object at the same time:
Dim wrd As New Word.ApplicationThis ensures that wrd always contains a valid instance of Word.Application. You don't have to worry about checking to see if wrd has been initialized yet. But there is a minor penalty for this, since the VBA compiler adds some overhead to be sure the object was loaded. This is transparent to you, but your compiled code will be slightly larger compared with the previous syntax.
CreateObject can also be used, and it retrieves the type information for Word.Application from the referenced type library. The results of the following are identical with but slightly slower than using the similar construct with the New keyword:
Dim wrd as Word.Application
Set wrd = CreateObject("Word.Application")
New is the recommended method for instantiating objects if you have access to the object's type library.