High Level Overview of COM

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
• Intellisense
• 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:

1. Uncheck the COM server from the References dialog
2. Use CreateObject calls in place of New calls
3. Use Object types 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:

    wb.Close SaveChanges:=True
'    xl.Quit
'
'    Set ws = Nothing
'    Set wb = Nothing
'    Set xl = Nothing

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):

'    wb.Close SaveChanges:=True
    xl.Quit

    Set ws = Nothing
    Set wb = Nothing
    Set xl = Nothing

Referenced articles

What the #$%& is COM!?

One of the most important concepts in VBA is also one of the least understood. Let’s shine a light on the Component Object Model.

No Longer SetMike Wolfe

Image by saddleroad from Pixabay