从VBA访问COM加载项代码 [英] Accessing COM add-in code from VBA

问题描述

我使用Visual Studio 2005 Tools for Office为Excel 2003创建了一个COM加载项。加载项代码如下所示：

I have created a COM add-in for Excel 2003 using Visual Studio 2005 Tools for Office. The add-in code looks like this:

[Guid("EAC0992E-AC39-4126-B851-A57BA3FA80B8")]
[ComVisible(true)]
[ProgId("NLog4VBA.Logger")]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Logger
{
    public double Debug(string context, string message)
    {
        Trace.WriteLine(message);
        return message.Length;
    }

    [ComRegisterFunctionAttribute]
    public static void RegisterFunction(Type type)
    {
        Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));
        RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);
        key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String);
    }

    [ComUnregisterFunctionAttribute]
    public static void UnregisterFunction(Type type)
    {
        Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);
    }

    private static string GetSubKeyName(Type type, string subKeyName)
    {
        System.Text.StringBuilder s = new System.Text.StringBuilder();
        s.Append(@"CLSID\{");
        s.Append(type.GUID.ToString().ToUpper());
        s.Append(@"}\");
        s.Append(subKeyName);
        return s.ToString();
    }
}

我已将项目设置为注册COM互操作，我已注册DLL：

I've set the project to register for COM interop, and I've registered the DLL with:

regasm.exe /tlb NLog4VBA.dll

当我打开Excel，我去工具 - >插件，单击自动化，并添加NLog4VBA.Logger。然后我可以去插入 - >功能，从类别列表中选择NLogVBA.Logger，然后选择调试。

When I open Excel, I go to Tools -> Add-Ins, click Automation, and add NLog4VBA.Logger. I can then go to Insert -> Function, pick NLogVBA.Logger from the list of categories, and choose Debug.

最终结果是一个内容类似的单元格：

The end result is a cell with contents like:

=Debug("My Context","My Message")

...以及显示值：

... and a displayed value of:

10

$ b b

这是所有应有的。在我的VBA代码中，我可以去工具 - >引用和添加NLog4VBA。然后将以下代码添加到工作表上的按钮：

This is all as it should be. In my VBA code, I can go to Tools -> References and add NLog4VBA. I then add the following code to a button on my sheet:

Private Sub CommandButton1_Click()
        Application.COMAddIns("NLog4VBA.Logger").Object.Debug "My Context", "My Message"
End Sub

$ b b

这会失败，因为COMAddIns（NLog4VBA.Logger）失败：

This fails, because COMAddIns("NLog4VBA.Logger") fails with:

Run-time error '9': Subscript out of range

有人可以告诉我我需要做什么来进行调试方法访问我的VBA代码（这对我来说比能够从单元格内调用方法更有用）？

Could someone please tell me what I need to do to make the Debug() method accessible to my VBA code (which is more useful to me than being able to call the method from within a cell)?

我确定我在这里缺少一些简单的东西。

I'm sure I'm missing something simple here.

07：我已将代码段更新为包含[ProgId]属性，如下面的Jim所示：问题仍然存在。我可以在注册表中看到对象：

Edited 2010/09/07: I've updated the code snippet to include the [ProgId] attribute as suggested below by Jim; the problem persists. I can see the object in registry:

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}]
@="NLog4VBA.Logger"

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}\Implemented Categories]

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}\Implemented Categories\{62C8FE65-4EBB-45e7-B440-6E39B2CDBF29}]

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}\InprocServer32]
@="C:\\WINDOWS\\system32\\mscoree.dll"
"ThreadingModel"="Both"
"Class"="NLog4VBA.Logger"
"Assembly"="NLog4VBA, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///C:/projects/nlog4vba/NLog4VBA/bin/Debug/NLog4VBA.dll"

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}\InprocServer32\1.0.0.0]
"Class"="NLog4VBA.Logger"
"Assembly"="NLog4VBA, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///C:/projects/nlog4vba/NLog4VBA/bin/Debug/NLog4VBA.dll"

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}\ProgId]
@="NLog4VBA.Logger"

[HKEY_CLASSES_ROOT\CLSID\{EAC0992E-AC39-4126-B851-A57BA3FA80B8}\Programmable]

此外，ProgID在加载项对话框中可见：

Also, the ProgID is visible in the Add-Ins dialog:

我还不知道为什么这不工作： - （

I still have no idea why this isn't working :-(

推荐答案

原来，我的VBA代码是错误的; 这里是答案礼貌 Jan Karel Pieterse ：

It turns out that my VBA code was quite wrong; here is the answer courtesy Jan Karel Pieterse:

我认为你需要这样做
：

I think you would need to do something like this:

Private Sub CommandButton1_Click()

        'Declare an object variable using the referenced lib.
        'if all is well, intellisense will tell you what the proper object name is:
        Dim objLogger as NLog4VBA

        'Create an instance of the object
        Set objLogger = New NLog4VBA

        'Now use the object
        objLogger.Object.Debug "My Context", "My Message"
End Sub

这篇关于从VBA访问COM加载项代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！