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.
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
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.
