问题描述
我使用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代码是错误的; 是答案礼貌：
It turns out that my VBA code was quite wrong; here is the answer courtesy Jan Karel Pieterse:
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加载项代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！