如何从 Excel 2007 VBA 模块中调用 System.Runtime.InteropServices.Marshal.ReleaseComObject

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

提问by Pauli Price

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:

我想在我的 VBA 代码中实例化的某些 3rd 方 COM 对象上调用 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)，来自我的工作簿关闭事件，如下所示：

    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 qualifierwith the Systemhighlighted 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.

不过，我得到一个compile error: invalid qualifier与System与上面的代码高亮显示。搜索似乎没有返回任何从 VBA 宏调用 System.Runtime 方法的 VBA 代码 - 我只能找到 VB.Net 自动化 Excel。我不确定这是否可能。

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

我正在尝试解决这个问题：Excel 2007 Zombie Process 不是 COM 自动化，而是通过确保在 Excel 退出之前正确处理这些 3rd 方 COM 对象来引用 3rd 方 com 对象。

采纳答案by Pauli Price

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

我无法使用推荐的 VBA only 方法解决僵尸问题，即使在仔细检查和删除循环引用之后也是如此。

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

额外的搜索发现了一种使用代码调用该ReleaseCom方法的方法，该代码包装System.Runtime.InteropServices.Marshal.FinalReleaseComObject以创建可从 VBA 调用的 COM 可见 dll

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.

使用教程“如何使用 vs 2008 创建 com 对象并从 vb6.0 客户端使用它”和新安装的 VS2010 Express 副本，我能够创建一个可从 VBA 调用的 COM 可见 dll。

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

这是稍微修改的包装器以及如何构建 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.InteropServicesand 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

要构建，创建一个新的类库项目，添加对System.Runtime.InteropServices和 的引用System.EnterpriseServices，启用“签署程序集”（在项目/属性/签名下的菜单中）并选择或创建一个密钥文件。添加类和接口代码文件。在 AssemblyInfo.cs 文件（位于属性下）中添加

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:

并建立。如果一切顺利，您可以按如下方式注册您的 dll：

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:

在 VBA 中，添加对新 COM 库的引用后，按如下方式使用它：

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.

早期测试表明它可以正常工作，即 Excel 会干净地关闭并且不再创建僵尸进程。

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

有趣的是，我刚刚遇到了这种从 VBA 使用 dll 的方法，而无需先注册它，如果您无法访问客户端计算机上的注册表，这可能很有用。

回答by user1610015

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.

我没有使用过 VBA，但我认为它不是 .NET 语言，所以自然不能在 VBA 中使用 System.Runtime.InteropServices.Marshal 等 .NET Framework 类。

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:

当您希望 Excel 退出时，您确定要删除对 COM 对象的所有引用吗？确保您为每个对 COM 对象的引用放置如下所示的行：

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:

如果这不能解决问题，也有可能是循环引用问题。COM 对象是否存储对您的 VBA 对象的引用，而 VBA 对象又包含对 COM 对象的引用？如果是这样，将创建循环引用并且永远不会释放对象。我正在寻找其他东西，发现一个帖子与您的非常相似：

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.

如果这是问题所在，您真的需要一种类似于 Marshal.ReleaseComObject 的手动释放 COM 对象的方法（并且多次释放直到引用计数为零），但我不知道如何在 VBA 中执行此操作。

A couple of other similar threads:

其他几个类似的线程：

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

如何从 Excel VBA 中释放进程内 COM 服务器对象Excel 进程在互操作后保持打开状态；传统方法不起作用