How to release inprocess COM Server object from Excel VBA

Question

How do you force Excel (2007) VBA to release references to a COM server object?

I have written an inprocess (Single instance DLL) COM Server in Visual Foxpro 9 SP2 which is instantiated from Excel 2007 VBA code on my development machine. Excel seems to be holding a reference to the COM object/dll even though I set it = Nothing. This prevents me from rebuilding the DLL due to a "File access is denied TestCOM.dll" message until I quit Excel which is a pain everytime I want to make a change and test it.

I have boiled the code down to a very simple test setup: The VFP9 project (TestCOM) has just one .prg file with the following contents

DEFINE CLASS TestClass As Session OLEPUBLIC

ENDDEFINE

The VBA Code is as follows:

Sub Test()

    Set objTest = CreateObject("TestCOM.TestClass")
    Set objTest = Nothing

End Sub

I have tried removing the reference to the COM server library in the VBA project but this does not make any difference. I have tried with and without DIMing the object variables and it makes no difference. I have tried creating a new VFP DLL project but the problem persists.

If I build the VFP application/dll as an INPROCESS/DLL and run the VBA code I get this problem but if I build it as an OUTOFPROCESS/EXE and run the VBA code I do NOT get this problem.

I found a very similar problem in COM Object Cleanup except that my COM Server is written in Visual Foxpro 9 SP2 whereas that relates to C# and the OP has not explained in detail how they resolved the problem so I don't know how to get around it; if that is even possible.

Answer 1

The procedure used to instantiate your COM class from the code in your DLL is that Excel calls the COM library layer to find your implementation using either the ProgID or the ClassID. When you have an inproc server this means it finds a path to your DLL and uses LoadLibrary to load it into your client process and then creates the class factory and calls methods in the DLL. So the end result is that Excel calls LoadLibrary on your DLL and this locks the file until Excel calls FreeLibrary on the handle.

Using COM interfaces you don't have control of this. You call CoCreateInstance() (or from VBA you create the object with New or CreateObject which calls this Win32 API underneath). The implementation of this handles the LoadLibrary and everything else until you get handed an interface pointer to work with. Some applications will periodically call CoFreeUnusedLibraries() to attempt to release loaded COM dlls that are not currently in use. The default class factory implementation maintains a counter of objects created that can be used to determine if a DLL is in use or not - but this is not always reliable as COM class writers may not obey the rules. Exiting Excel obviously releases the lock on the file.

When you create your COM class as an out-of-process server - it lives in a separate executable or DLL whose lifetime is managed differently. Excel no longer holds a lock on the DLL and releasing the COM instance may well allow the hosting process to exit.

You can convert a DLL to be used as a localserver (out-of-process) by arranging to have it hosted by DllHost. If you use the OleView utility and find your classes ProgId then you can enable hosting in a surrogate process (dllhost). It's been a while since I did that but there should be information on the web about using surrogate hosting. Obviously hosting a COM object out-of-process makes everything slower and introduces the potential for various marshalling issues. Provided you keep oleautomation compatible interfaces it should be fine.