There are two primary ways to interact with COM objects from VBA:
- Early binding
- Late binding
To use early binding, you need to use Tools > References... to add a reference to the COM object's library. You then define the object using a specific type:
To use late binding, you define your object as a generic
VBA knows nothing about how to handle method and property calls to a late-bound object at compile time. In fact, at compile time, VBA does not know what methods or properties the object even supports. This is why there is no IntelliSense with late binding.
If VBA doesn't know how to handle method and property calls of late-bound objects at compile time, how in the world is it able to handle them at runtime?
IDispatch: The Key to Late Binding
Not all COM objects support late binding.
Late binding support requires the COM object to implement the IDispatch interface. In plain English, the IDispatch interface is like a COM object's table of contents.
To understand how this works, let's explore the four methods of the IDispatch interface.
The Four Methods of the IDispatch Interface
Returns one of two values:
1: the object provides type information
0: the object does not provide type information
Remarks from the official documentation:
The method may return zero, which indicates that the object does not provide any type information. In this case, the object may still be programmable through IDispatch or a VTBL [virtual function table], but does not provide run-time type information for browsers, compilers, or other programming tools that access type information.
As far as you're concerned as a VBA developer, the value of this function is meaningless. Whether the object provides type information is a bit of a moot point, as you won't get IntelliSense or any other sort of type information in the IDE for a late-bound object whether the object provides it at runtime or not.
From the official docs:
Retrieves the type information for an object, which can then be used to get the type information for an interface.
To execute methods or retrieve properties of an object that implements IDispatch, you need to use a numeric identifier to "invoke" those methods/properties.
The GetIDsOfNames method takes human-readable names (such as "Workbooks") and returns a number (known as a dispatch identifier or dispID) that maps to the object's associated method or property. This is analogous to a book index: you look up a word and the index provides you with the page number where you can find additional information about that topic.
Like a book index, GetIDsOfNames returns a dispID number based on a given property or method name.
Once you have the dispID number, you use Invoke to call the method or retrieve property data.
In addition to calling the method or property, Invoke also handles passing all the arguments back and forth between VBA and the COM object.
VBA Hides This Complexity
As with the IUknown interface, VBA calls each of these methods behind the scenes.
If you are a VBA developer, this article is provided for background information only. You need not concern yourself with the low-level details. However, this information provides important context for dealing with COM objects.
Here's the key takeaway:
Late binding–made possible only through the IDispatch interface–provides simplicity and flexibility at the expense of performance.
A Final Note About Performance
Authors Corry, Mayfield, Cadman, and Morin sum up the flexibility-for-performance tradeoff nicely in The Waite Group's COM/DCOM Primer Plus (p. 426):
Many CPU cycles are burned up in the client to pack up the arguments, make the call, and unpack the return values. Then the server side has to Invoke the request, unpack the arguments, route the request to a function inside the server, and repack the return values before returning to the client.
IDispatch, the friendly and flexible interface, comes with unavoidable performance costs on every call.