Unable to access COM exposed methods in VBA

问题:

I am trying to access COM exposed methods in VBA.

Problem: I am seeing all default methods (like GetHashCode, GetType and ToString) in VBA but not the ones which are part of the COM interface and specifically written to be COM visible (like getStringValue() below).

Set up Details:

• Visual Studio 2008
• Windows 7 x64
• Office 2007
• .NET 3.5

Interface 'IGetMyString.cs'

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace SimpleCOMAssembly
{
    [ComVisible(true), GuidAttribute("4153A1AC-ECE9-4f66-B56C-1DDEB6514D5D")]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    interface IGetMyString
    {
        [DispId(1)]
        string getStringValue();
    }
}

Implementation 'GetMyString.cs'

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.ComponentModel;

namespace SimpleCOMAssembly
{
    [ComVisible(true), GuidAttribute("0A3D4D65-CF50-4020-BF13-77001F8AAABE")]
    [ProgId("SimpleCOMAssembly.GetMyString")]
    [ClassInterface(ClassInterfaceType.None)]
    public class GetMyString : IGetMyString
    {
        public GetMyString() { }

        [ComVisible(true), Description("Get my string")]
        public string getStringValue()
        {
            return "hello";
        }
    }
}

In Build properties, I checked 'Make Assembly COM Visible' (see below snapshot)

Also asked Visual Studio 2005 to make the 'Register for COM interop' (see below snapshot)

And, finally as a post build event, I am running the regasm.exe to register the .DLL and also the .TLB to registry as follows:

%SystemRoot%\Microsoft.NET\Framework\v2.0.50727\regasm /codebase "$(TargetPath)" /tlb:"$(TargetDir)$(TargetName).lib"

In Excel Object Explorer view, I enabled COM server (SimpleCOMAssembly written above), and now in the Object Explorer it does not list down the COM interface method (see below)

Can someone help me know what I am missing which is causing the COM interface methods not show in VBA?

EDIT Attaching ITypeLib View of the generated TLB

回答1:

The Excel object browser screenshot clearly shows a problem. Note how the GetHashcode, GetType and ToString methods are visible. Those are methods that are inherited from System.Object. But your snippet explicitly (and correctly) uses [ClassInterface(ClassInterfaceType.None)] so that the class implementation is hidden.

It isn't hidden. Not so sure how that happened, an old type library from an earlier attempt could explain it. But your build steps are very suspect, you are helping too much. The "Make assembly types COM visible" option is a pretty crude way to force the build system to expose .NET types. But your code is using the refined upper-right-pinky-up-when-you-drink-tea way to expose types to COM. Which includes the [ComVisible(true)] attribute, what the checkbox does, and the [ClassInterface] attribute, which is what the checkbox doesn't do.

So the problem is that you asked the build system to implement two interfaces. Whatever it inherited from the base class, _Object in your case, plus what it inherited from the declaration, IMyGetString in your case. Which is fine and all quite COM compatible, but VBA isn't a very good COM consumer. It only likes the [Default] interface and that's _Object in your case. Clearly visible from the screenshot.

So turn off the "Make assembly COM visible" option.

And choose between either a postbuild event that calls Regasm or the "Register for COM interop" checkbox. Doing it both ways just doubles the odds that you don't know why it doesn't work. You only need the postbuild when you need to register the assembly for the 64-bit version of Office.

回答2:

I was having the same issue. I found this link.

Guide to calling a .net library from excel If I am referencing a TLB file called "Foo.tlb" which has a class called "FooClass" that has public members. You will not be able to see those members because they are built by default with late binding (run-time binding) interface only.

You can change your class to have early binding, which will allow for intellisense and the Members of the class to be exposed in the Object browser.

C#:

[ClassInterface(ClassInterfaceType.AutoDual)]
public class FooClass
{
    public FooClass()
    {
    }
    public string DoSomething()
    {
        return "I am printing....";
    }
}

You should be able to see the DoSomething method in the object browser after you implement this change. Note: You will have to deference the dll first.

回答3:

For the record - in case anyone else needs this answer - I just had the exact same issue as this (OLEView and everything) and it was driving me nuts. I couldn't figure out what I was doing wrong, because I've created several C# COMs before. I forgot to declare the interface (in this post that would be IGetMyString) as public.

Hope this saves someone some time.