How can I call System.Runtime.InteropServices.Marshal.ReleaseComObject from within an Excel 2007 VBA module

Question

I want to call System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) on some 3rd party COM objects that I've instantiated in my VBA code, from my workbook close event, something like the following:

    Public Sub disconnect(obj As Variant)
        Dim refs As Long
        refs = 0

        If Not obj Is Nothing Then
            Do
                refs = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Loop While (refs > 0)
        End If
    End Sub

However, I get a compile error: invalid qualifier with the System highlighted with the above code. Search doesn't seem to return any VBA code that calls System.Runtime methods from a VBA macro - I can only find VB.Net automating Excel. I'm not sure it's even possible.

I'm trying to resolve this issue: Excel 2007 Zombie Process not COM automation but w/ references to 3rd party com objects by ensuring these 3rd party COM objects are properly disposed of before Excel exits.

Answer 1

I haven't used VBA but I don't think it's a .NET language, so naturally you can't use .NET Framework classes like System.Runtime.InteropServices.Marshal in VBA.

Are you sure you are dropping all references to the COM object when you want Excel to exit? Make sure that you are by placing lines like the following for every reference you hold to the COM object:

obj = Nothing ' Where "obj" is a reference to the COM object

If that doesn't solve it, it's also possible that the problem is a circular reference. Does the COM object store a refenrece to a VBA object of yours which in turn holds a reference to the COM object? If so, a circular reference will be created and the objects will never be released. I was searching for something else and found a post very similar to yours:

Forcing Garbage Collection

If that's the problem, you'll really need a way to release the COM object manually (and several times until the reference count is zero) similar to Marshal.ReleaseComObject, but I don't know how you can do that in VBA.

A couple of other similar threads:

How to release inprocess COM Server object from Excel VBA Excel process remains open after interop; traditional method not working

Answer 2

I was unable to resolve the zombie problem using the recommended VBA only method, even after careful checking for and removal of circular references.

Additional searching turned up a way to call the ReleaseCom method using code that wraps System.Runtime.InteropServices.Marshal.FinalReleaseComObject to create a COM visible dll that can be called from VBA

Using the tutorial "how to create a com object using vs 2008 and consume it from vb6.0 client" and a newly installed copy of VS2010 Express, I was able to create a COM visible dll callable from VBA.

Here's the slightly modified wrapper and how to build the dll:

    using System;
    using System.Collections.Generic;
    using System.Runtime.InteropServices;
    using System.EnterpriseServices;


    namespace ComDisposerLib
    {
        [ClassInterface(ClassInterfaceType.None)]
        [ComponentAccessControl(false)]
        public class ComDisposer : System.EnterpriseServices.ServicedComponent, IDisposable, ComDisposerLib.IComDispose
        {
            private List<Object> _comObjs;

            public ComDisposer()
            {
                _comObjs = new List<Object>();
            }

            ~ComDisposer()
            {
                Dispose(false);
            }

            public Object Add(Object o)
            {
                if (o != null && o.GetType().IsCOMObject)
                    _comObjs.Add(o);
                return o;
            }

            public void Clear()
            {
                Dispose(true);
            }

            protected override void Dispose(bool disposing)
            {
                if (disposing)
                {
                    for (int i = _comObjs.Count - 1; i >= 0; --i)
                        Marshal.FinalReleaseComObject(_comObjs[i]);
                    _comObjs.Clear();
                }
            }

            void IDisposable.Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }
        }
    }

and the interface:

    using System;

    namespace ComDisposerLib
    {
        public interface IComDispose
        {
            Object Add(Object o);
            void Clear();
            void Dispose();
        }
    }

To build, create a new class library project, add references to System.Runtime.InteropServices and System.EnterpriseServices, enable 'Sign the assembly' (in the menu under project / properties / signing ) and select or create a key file. Add the class and interface code files. In the AssemblyInfo.cs file (located under properties) add

using System.Runtime.InteropServices;
using System.EnterpriseServices;

and

[assembly: ComVisible(true)]
[assembly: ApplicationName("ComDisposer")]
[assembly: ApplicationActivation(ActivationOption.Library)]

and build. If all goes well, you can register you dll as follows:

regsvcs "C:\Documents and Settings\username\My Documents\Visual Studio 2010\Projects\ComDispose\ComDispose\obj\Release\ComDisposer.dll"

In VBA, after adding a reference to your new COM library, use it as follows:

Sub disposeGlobalComObjects()
' global scope objects used only to simplify example 

    Dim cd As ComDisposer
    Set cd = New ComDisposer

    If Not SomeGlobalComObject Is Nothing Then
        cd.Add SomeGlobalComObject
        Set SomeGlobalComObject = Nothing
    End If
    If Not AnotherGlobalComObject Is Nothing Then
        cd.Add AnotherGlobalComObject
        Set AnotherGlobalComObject = Nothing
    End If
    cd.Dispose
End Sub

Early testing indicates that it's working, i.e. Excel closes cleanly and no longer creates zombie processes.

Interestingly, I just ran across this method for using your dll from VBA without having to register it first which could be useful if you didn't have access to the registry on your client machine.