Accessing COM add-in code from VBA

Question

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

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

regasm.exe /tlb NLog4VBA.dll

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

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

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

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

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.

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]

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

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

Answer 1

The COMAddIns collection is either indexed via a numerical index, or via a string that is the ProgId of the desired component. Make sure that your ProgId is actually "NLog4VBA.Logger" (via the ProgId attribute in .NET) and verify that the object is registered with this id (which you can easily check in the registry, searching for your assigned GUID).

Answer 2

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