Invisible Instances of Office Applications: Who is to Blame?

A couple of recent articles and videos in the Access community got me thinking about the somewhat related topic of COM automation.

Hanging Office Applications

The above articles and video deal specifically with the Access executable failing to terminate properly due to a variety of different Access bugs.  However, similar behavior can be seen with other Office applications, including Word, Excel, and Outlook.  And while those situations can also be caused by bugs in the respective applications, they can also be a side effect of sloppy COM automation programming in VBA.

COM Automation

By far, the most common usage of "COM automation" is to interact with one Office application (such as Excel) from another Office application (such as Access) from within VBA.

The key line from the above article for our purposes today is this one:

As a VBA developer, you have exactly one COM responsibility: clean up after yourself.

If you don't clean up the object references to other Office applications, those applications can't shut down.  

Placing the Blame for Hanging Office Applications

One of the easiest ways to identify who is responsible for a hanging Office application is to look at its command line.

If the command line includes the arguments /automation -Embedding, then you know that instance of the application was created via COM.

The command line column is not displayed by default in Task Manager.  To view it, follow these steps:

1. Open Task Manager
2. Go to the Details tab
3. Right-click an existing column header then choose Select columns
4. Scroll down and put a check mark next to [√] Command line
5. Click [OK]

Once you've displayed the "Command line" column, go to the hanging Office application and look for the words /automation -Embedding:

Reproducing a Hanging Excel Instance

To better understand how COM automation works at the process level, let's look at some sample code.

!!!WARNING!!! The sample code below includes a line that will close all open instances of Excel without even prompting you to save any pending changes.  I strongly recommend you close all open instances of Excel before continuing.

The sample code below uses late-binding to create an instance of Excel.  It has message box calls placed throughout to inform you of what's going on and introduce natural stopping points:

Sub CreateNewExcelProcessViaAutomation()
    Const LeaveExcelHanging As Boolean = False

    Dim appExcel As Object
    Set appExcel = CreateObject("Excel.Application")
    
    MsgBox "Invisible copy of Excel running via COM automation" & _
           vbNewLine & vbNewLine & _
           "excel.exe /automation -Embedding"
    
    appExcel.Visible = True
    MsgBox "Existing Excel process now visible"
    
    'If we don't set appExcel to Nothing, then excel.exe will hang indefinitely
    If LeaveExcelHanging Then Exit Sub
    
    Set appExcel = Nothing
    MsgBox "Existing Excel process terminating"
    
End Sub

Now, sometimes you don't want to create a new instance of the other Office application and instead you just want to grab an existing instance.  You can do that using the GetObject() function:

Sub GrabExistingExcelInstance()
    Shell "Excel.exe", vbNormalNoFocus
    MsgBox "Visible copy of Excel running as normal"
    
    'Grab the running copy of Excel
    Dim appExcel As Object
    Set appExcel = GetObject(, "Excel.Application")
    MsgBox "appExcel refers to running copy of Excel"
    
    appExcel.Visible = False
    MsgBox "Excel temporarily hidden"
    
    appExcel.Visible = True
    MsgBox "Excel now visible again"
    
    Set appExcel = Nothing
    MsgBox "Excel still running despite being disconnected from appExcel"
    
    '!!! Be careful testing this next line !!!
    'It will close all open instances of Excel instantly,
    '   not just the one we started above
    Shell "taskkill /im Excel.exe"
    MsgBox "All existing Excel processes now terminated."
    
End Sub

More Info on COM Programming

It took all the restraint I had not to turn this article into yet another COM explainer.  That said, if you would like to learn more about COM programming in VBA, check out this series of articles I wrote on the topic:

Acknowledgements

• Cover image generated by DALL-E-3