VBA, C#, COM+ Isolate C# DLL in a separate process

If you have every authored a C# .NET class library to act as an in-process COM server Dll for use in Excel VBA then you've probably been irritated about having to close your Excel.exe session every time you wanted to rebuild the C# component. I have a fix for that, the answer is to create a COM+ application which gives a process separate to Excel.exe. I detail this in this blog post.

Also in this blog post I will show a beautiful software application called Process Explorer by Mark Russinovich of SysInternals. This beauty will track down a Dll and show which process has it loaded.

Click here for separate Youtube window

Our Simple C# Class Library COM Dll server

So for demonstration purposes we have a really simple C# class library with extra attributes to make it inter-operable with COM clients and thus act as an in-process COM Server Dll. The project name is ClassLibrary.

In the code below you see how we must define the interface separately (actually in VB.Net one can be more succinct but never mind). As mentioned in the comments one needs to make the assembly COM visible, check the 'Register for COM interop' checkbox on the build tab and don't forget to run Visual Studio with administrator rights so the the registration is successful.

The code does do very much. All it does is return a string "baz" pre-pended with a message about the current process id. I need the current process id to highlight the tools I'm going to demonstrate.

using System.Diagnostics;
using System.Runtime.InteropServices;

// (i) in AssemblyInfo.cs change to [assembly: ComVisible(true)] 
// (ii) in the Project Properties->Build tab under the Output section ensure  the 'Register for COM interop' checkbox is checked.
// (iii) You will need to run Visual Studio with admin rights so it can register the dll.

namespace ClassLibrary
{
    public interface IFoo
    {
        string bar();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IFoo))]
    public class CFoo : IFoo
    {
        string IFoo.bar()
        {
            return "Current process id:" + Process.GetCurrentProcess().Id +  "\nbaz";
        }
    }
}

and here is some client VBA code. Once the CreateObject() line is executed the Dll is loaded into the Excel.exe process space. We are using late binding but the Dll still gets locked. Once loaded into Excel.exe, it cannot be freed. Once loaded into Excel.exe, it is impossible for Visual Studio to build the project. One has to close the Excel.exe session; this can be really disruptive.

Option Explicit
Option Private Module

Private Sub Test1()

    Dim obj As Object 'ClassLibrary.CFoo
    Set obj = CreateObject("ClassLibrary.CFoo")
    
    Debug.Print obj.bar
    
    Stop
    Set obj = Nothing
    
End Sub

Process Explorer - by Mark Russinovic, SysInternals

So during my investigations I used Process Explorer by Mark Russinovich, Sysinternals. This is a superb program that has a ton of features worth investigating in your spare time. Relevant here is the ability to sweep through processes looking for a file that has been loaded. First though we need to run the VBA client code, run this now and allow it to halt on the Stop statement. Halting gives us time to look at where its loaded.

Here is a screenshot of it finding the C# class library loaded into Excel.

I'll give a second screenshot which will successfully show the Dll loaded into a separate COM+ process. Let's move onto COM+.

COM+ (Component Services) allows surrogate hosting for in-process Dlls with a separate process

So we need to load the Dll into a separate process and the easiest way to do this is to configure a COM+ application. On the request for a configured object, COM+ will launch a separate process call DLLHost.exe and then load the COM DLL server into that process and marshal method calls thereto.

The COM+ Microsoft Management Console

It is best to dive in and look at the COM+ administration tool before talking about it, here is the steps required to open that. These are valid for Windows 10 users (and prior editions)

1. Open the classic Control Panel
2. In the classic Control Panel, click on Adminstrative Tools icon to open Adminstrative Tools explorer
3. In the Adminstrative Tools explorer, double-click on the Component Services icon to open the Microsoft Management Console (MMC) for Component Services
4. In the Microsoft Management Console (MMC) for Component Services expand the Component Services treeview node to reveal the Computers folder
5. Click on (and thus expand) the Computers folder treeview node to reveal the My Computer folder icon
6. Click on (and thus expand) the My Computer folder icon treeview node to reveal the COM+ Applications folder icon
7. Click on (and thus expand) the COM+ Applications folder icon treeview node to reveal the COM+ Applications

Your MMC should look something like this below

From here we can create a new Application, the steps are as following.

1. Right-click on the COM+ Applications folder icon treeview node to get the context menu from which select New->Application to launch the COM+ Application Install Wizard
2. Click Next on the front page on the launched COM+ Application Install Wizard to move to the 'Install or Create a New Application' wizard page
3. On the 'Install or Create a New Application' wizard page click on 'Create an empty application' which opens the 'Create Empty Application' wizard page
4. On the 'Create Empty Application' wizard page, enter 'MyNewApplication' for the name and ensure the Server application radio button is selected (this is key to getting a separate process). Click 'Next' to move to the Application Identity wizard page.
5. Click 'Next' on the Application Identity wizard page to take the defaults and move to the Add Application Roles wizard page
6. Click 'Next' on the Add Application Roles wizard page to take the defaults and move to the Add Users to Roles wizard page
7. Click 'Next' on the Add Users to Roles wizard page to take the defaults and move to final finish page. Click Finish.

You now have a new COM+ Application, symbolised by a COM+ Application icon and labelled 'MyNewApplication'. But it has no components yet, so lets add our ClassLibrary.dll. Here are the steps.

1. Select the MyNewApplication COM+ application by clicking on its icon.
2. Expand the MyNewApplication COM+ application folder icon to reveal the contained Components folder.
3. Select the Components folder by clicking on its icon.
4. Right-click on the Components folder icon to get the context menu from which select New->Component to launch the COM+ Component Install Wizard
5. On the front page of the COM+ Component Install Wizard click next to move to the Import or install a component wizard page
6. On the Import or install a component wizard page choose 'Import component(s) that are already registered' to move to the Choose Components to Import wizard page
7. On the Choose Components to Import wizard page, I had to check the 32-Bit Registry checkbox before I could find ClassLibrary.CFoo
8. Select ClassLibrary.CFoo in the list and then click Next to move to the Finish wizard page whereupon click Finish,

Your console should now look like this...

So you now have configured your COM+ application to contain and serve instantiations of the ClassLibrary.CFoo class. ClassLibrary.Dll will be loaded in a surrogate DllHost.exe process. I will prove this by running the VBA client code again and again using Process Explorer to find the ClassLibrary.dll. YOU MUST CLOSE EXCEL.EXE AND RESTART NEW EXCEL.EXE

After closing the exiting Excel.exe session and restarting a new Excel.exe session and running the VBA client code the ClassLibrary.CFoo object was instantiated into a DllHost.exe surrogate process. Here is a screenshot proving the new location of the Dll in the DllHost.exe surrogate process (and no trace in any Excel.exe process).

More proof that the COM+ Application is running is by observing its icon in the console, it becomes an animated spinning icon!

Shutting down the COM+ Application surrogate process

So the real benefit to all this is the ability to shut the surrogate process, release the Dll from being loaded and locked and allow Visual Studio to rebuild the next version of the component. We no longer have to close the Excel session. All we need to do is shutdown the surrogate process of the COM+ Application. This is easy, here are the steps.

1. In the MMC, click on the MyNewApplication icon to select it.
2. Right-click on the MyNewApplication icon to get the context menu from which select Shutdown

There isn't much feedback to say a shutdown has happened, Process Explorer will delete the line if you are keen to see evidence. The best evidence is that Visual Studio can now rebuild the component.

One caveat, if you change the component's interface then be prepared to repeat the above configuration of the application. Other than that you can now enjoy more enduring Excel sessions; something I've got used to whilst developing Python scripts which do not lock up.

COM+ information (catalog) is NOT stored in the registry

I got a little confused about where the COM+ information was stored. I searched and search the registry but to no avail. It turns out that it is NOT stored in the registry as per this quote from the COM+ (Component Services) documentation on the COM+ catalog...

The data store that holds COM+ configuration data. Performance of COM+ administration tasks requires reading and writing data stored in the catalog. The catalog can be accessed only through the Component Services administrative tool or through the COMAdmin library.

Actually, this is probably a good thing. On numerous occasions, I have had to poke around the registry to solve a COM issue. Locking up the information is probably a step in the right direction and I guess this approach previews the .NET global assembly cache.