如何从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），类似于以下内容：

  Public Sub disconnect（obj As Variant）
 Dim refs As Long 
 refs = 0 
 
如果不是obj是没有，然后
做
 refs = System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
循环while（refs> 0）
 End If 
 End Sub 
  

但是，我得到一个编译错误：无效的限定符与系统突出显示与上述代码。搜索似乎没有返回从VBA宏调用System.Runtime方法的任何VBA代码 - 我只能找到VB.Net自动化Excel。我不知道这是甚么可能的。

我正在尝试解决这个问题： Excel 2007僵尸进程不是COM自动化，而是引用第三方com对象确保这些第三方COM对象在Excel退出之前被妥善处理。

解决方案

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

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

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

这是稍微修改的包装器和如何构建dll：

 使用系统; 
使用System.Collections.Generic; 
使用System.Runtime.InteropServices; 
使用System.EnterpriseServices; 
 
 
命名空间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 disposal）
 {
 if（disposal）
 {
 for（int i = _comObjs。 Count  -  1; i> = 0; --i）
 Marshal.FinalReleaseComObject（_comObjs [i]）; 
 _comObjs.Clear（）; 
} 
} 
 
 void IDisposable.Dispose（）
 {
 Dispose（true）; 
 GC.SuppressFinalize（this）; 
} 
} 
} 
  

和界面： p>

  using System; 
 
命名空间ComDisposerLib 
 {
 public interface IComDispose 
 {
 Object Add（Object o）; 
 void Clear（）; 
 void Dispose（）; 
} 
} 
  

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

 使用System.Runtime.InteropServices; 
使用System.EnterpriseServices; 
  

和

  [assembly：ComVisible（true）] 
 [assembly：ApplicationName（ComDisposer）] 
 [assembly：ApplicationActivation（ActivationOption.Library）] 
  

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

  regsvcsC：\Documents and Settings\username\\ \\ My Documents\Visual Studio 2010 \Projects\ComDispose\ComDispose\obj\Release\ComDisposer.dll
  

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

  Sub disposeGlobalComObjects（）
'全局范围对象仅用于简化示例
 
 Dim cd As ComDisposer 
设置cd = New ComDisposer 
 
如果不是SomeGlobalComObject是没有，然后
 cd.Add SomeGlobalComObject 
 Set SomeGlobalComObject = Nothing 
结束If 
如果不是AnotherGlobalComObject不是然后
 cd.Add AnotherGlobalComObject 
 Set AnotherGlobalComObject = Nothing 
结束如果
 cd.Dispose 
 End Sub 
  

早期测试表示它的工作有趣的是，我刚刚碰到这个方法来使用您的dll从VBA而不必首先注册它这可能是有用的，如果您无法访问您的客户端计算机上的注册表。

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

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