High Level Overview of COM
This whirlwind tour of the Component Object Model technology will jump start your journey of automating Excel from VBA in Access.
Microsoft's Component Object Model technology is a binary standard that allows software written in different languages to interact with each other.
What does this mean for you as a VBA developer? The short answer: it means you can easily write code in VBA that calls code written in other languages, like C++.
COM Servers and COM Clients
This terminology tripped me up for a long time.
When I read about COM servers, it made me think of web servers or Windows Active Directory servers. In other words, I mistakenly imagined COM servers were devices, or, at least, I thought it was code that ran on other devices. I was wrong.
A COM server is code that exposes one or more of its routines to other code.
A COM client is code that calls the routines exposed by a COM server.
A Common Example: Excel Automation
To make things easier to follow, let's use a common example of COM interaction from the Microsoft Access world. As with every COM interaction, there is both a COM server and a COM client. In this example, the client is our VBA code and the server is the Excel object library.
- COM Server: the Excel Object Library (excel.exe, written in C++)
- COM Client: the custom VBA code you write in Access
Let's say you want to format an Excel workbook from within Access. First, you would have to create an instance of an object that the Excel COM server exposes. With the Excel object model, you usually start with the Excel Application object and drill down from there.
Here is some sample code that sets the header row to bold on the first worksheet:
Sub BoldifyHeader(fpSource As String) Dim xl As Excel.Application Set xl = New Excel.Application Dim wb As Excel.Workbook Set wb = xl.Workbooks.Open(fpSource) Dim ws As Excel.Worksheet Set ws = wb.Sheets(1) 'First sheet (1-based array) Dim HeaderRow As Excel.Range Set HeaderRow = ws.Rows(1) 'Top row (1-based array) HeaderRow.Font.Bold = True wb.Close SaveChanges:=True xl.Quit Set ws = Nothing Set wb = Nothing Set xl = Nothing End Sub
To call the above code from Access, you need to go to Tools > References... and put a check mark next to the Excel Object Library:
Early Binding vs. Late Binding
The example above uses what's known as "early binding." There are many advantages to early binding:
- Type safety
- Better performance
Type safety and intellisense are huge productivity boosts when you are writing code that interacts with COM servers.
Early binding has one critical flaw, though. It can blow up your whole application when you deploy your code to end users. If the COM server is not available on your end user's computer, or they have a different version of the COM server, your application won't even start.
So, when deploying code that interacts with COM servers (especially the other Office applications, like Excel, Word, Outlook, etc.), you are generally better off using late binding.
With early binding, your application will check to make sure the COM server is available when your application starts up. If it's missing or incompatible, execution stops immediately.
With late binding, your application won't check for the COM server until the first time you call it. This allows you to catch the error at runtime and provide a friendly error message, such as, "Excel not available."
Converting from Early to Late Binding
Most VBA developers prefer to do their initial development using early binding and then switch the code over to late binding prior to deployment.
How do you do that? It's a 3-step process:
- Uncheck the COM server from the References dialog
CreateObjectcalls in place of
Objecttypes in place of exposed COM server class types
Sub BoldifyHeader(fpSource As String) Dim xl As Object 'Excel.Application Set xl = CreateObject("Excel.Application") ' New Excel.Application Dim wb As Object 'Excel.Workbook Set wb = xl.Workbooks.Open(fpSource) Dim ws As Object 'Excel.Worksheet Set ws = wb.Sheets(1) 'First sheet (1-based array) Dim HeaderRow As Object 'Excel.Range Set HeaderRow = ws.Rows(1) 'Top row (1-based array) HeaderRow.Font.Bold = True wb.Close SaveChanges:=True xl.Quit Set ws = Nothing Set wb = Nothing Set xl = Nothing End Sub
COM Programming Responsibilities
The bulk of the work for implementing COM interaction lies with the COM server developer. COM server developers must implement very low-level interfaces to ensure their code is COM-compliant.
A big reason why so much of the COM documentation you read is so dense is because it is aimed primarily at COM server developers.
The good news for us as VBA developers is that we can simply ignore most of that low-level complexity. COM client code does have its own responsibilities, but the VBA language itself takes care of the low-level complexity.
As a VBA developer, you have exactly one COM responsibility: clean up after yourself.
You might be tempted to think that all you have to do is explicitly set the objects you created to Nothing to release them from memory. That's what these lines are doing:
Set ws = Nothing Set wb = Nothing Set xl = Nothing
The thing is, those lines are generally unnecessary. While it won't hurt to have them in there, and it does make your programming intent clear to others, VBA will take care of doing that for you in most cases.
What you really need to watch out for are two things:
- Circular references (where two object instances hold references to each other)
- Actions that you've taken that will block the COM server from shutting down
In our example code above, we can comment out four of the five cleanup lines and the Excel.exe process that our code spawns will go away:
The lines that explicitly set the objects to Nothing are completely optional in this case.
However, if we uncomment all four of those lines, but comment out the line that saves and closes the workbook we opened, then the Excel.exe process continues running in the background (you can confirm this via Task Manager):
Image by saddleroad from Pixabay