如何从Excel 2007 VBA模块中调用System.Runtime.InteropServices.Marshal.ReleaseComObject [英] How can I call System.Runtime.InteropServices.Marshal.ReleaseComObject from within an Excel 2007 VBA module

问题描述

我想在我的VBA代码中实例化的一些第三方COM对象上调用System.Runtime.InteropServices.Marshal.ReleaseComObject（obj），从我的工作簿关闭事件，如下：

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

但是，我得到一个编译错误：invalid qualifier ，用上面的代码突出显示 System 。搜索似乎没有返回任何VBA代码从VBA宏调用System.Runtime方法 - 我只能找到VB.Net自动化Excel。我不确定是否可能。

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.

我尝试解决此问题： Excel 2007 Zombie Process not COM automation but w / references to 3rd party com objects by确保这些第三方COM对象在Excel退出之前被正确处理。

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.

推荐答案

我无法使用推荐的VBA唯一方法解决僵尸问题，即使仔细检查和删除循环引用。

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

其他搜索方法可以调用 ReleaseCom 方法使用包装的代码System.Runtime.InteropServices.Marshal .FinalReleaseComObject 创建可以从VBA调用的COM可见dll

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

使用教程如何使用vs 2008创建一个com对象，并从vb6.0客户端使用它和一个新安装的VS2010 Express副本，我能够创建一个COM可见dll从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.

下面是稍微修改的包装器以及如何构建dll：

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);
            }
        }
    }

p>

and the interface:

    using System;

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

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

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;

和

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

并构建。如果一切顺利，您可以如下注册您的dll：

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"

在VBA中，在添加对你的新COM库的引用之后，使用它如下：

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

表示它正在工作，即Excel完全关闭，不再创建僵尸进程。

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

有趣的是，我只是遇到了从VBA使用您的dll的方法，无需先注册，如果您无法访问客户端计算机上的注册表，这将是非常有用的。

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.

这篇关于如何从Excel 2007 VBA模块中调用System.Runtime.InteropServices.Marshal.ReleaseComObject的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！