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.

Casabonne

For any others reading this thread I have subsequently realised that if the project is built as an out of process (EXE) COM Server then you can only rebuild the project without quitting Excel if you do not have a reference to the type library in VBA/Excel. – Casabonne

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.

Aili

Thank you for your excellent detailed technial reply. So in brief you are basically saying that with COM interfaces I am at the mercy of the client, in this case Excel, and there is no way I force a release of the DLL? I should have mentioned in my question that this problem doesn't occur when the COM server is called from another copy of VFP so the fault does seem to be with the client rather than the server. I think as a workaround I will build this project as an Out of Process server (EXE) during development and then switch it to In Process (DLL) for final testing and release. – Casabonne

One extra ingredient is required. The COM server must return S_OK from its DllCanUnloadNow() entrypoint. Not simple to test in a VBA + Foxpro environment. – Miskolc

Answer 2

Adding a shorter answer ....

Releasing the DLL is knotty problem and one familiar to developers of in process COM components for use in Excel.

There are two conditions that need to be satisfied

1) Do not use an early binding library references (Tools->Reference) , use late binding instead. Early binding tools reference will hold a lock.

2) Call CoFreeUnusedLibraries to unload COM servers which no longer have clients.

From your sample code you are already late binding but please check your references. Whilst point 2) is referred to in payyhoyts answer no code is given.

Here is a copy and pasteable declaration

Private Declare Sub CoFreeUnusedLibraries Lib "ole32.dll" ()

Waverly