Access .NET generic objects from VBA

Question

My .net code has an object with a number of generic properties. This object is returned to the VBA code. All the non-generic properties are working well, but I also need to access the generic values. Is there a way to do it from VBA?

[ClassInterface(ClassInterfaceType.AutoDual)]
public class Obj
{
    public string GetProp1() {...}
    public IList<MyCustomType> GetProp2() {...}
}

VB code:

Sub Test()
    Dim o As Program.Obj
    Set o = New Program.Obj
    Set p2 = hp.GetProp2()

    set p2_0 = p2(0) ' doesn't work

End Sub

Answer 1

You can't do this directly (if VS is doing your COM registration, you should see a warning like: Type library exporter warning processing 'NS.Obj.get_GetProp2(#1), Assy'. Warning: Type library exporter encountered a generic type instance in a signature. Generic code may not be exported to COM.

What you'd need to do is make a little non-generic wrapper and an interface to expose to COM (assuming you want strongly-typed objects). As long as you reference the typelib in VBA and access your objects through strongly-typed VBA refs, you can do something like:

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Class1
{
    public IMyListOfString Strings {get; set;}
    public Class1()
    {
        Strings = new MyListOfString() { "Foo", "Bar", "Baz" };
    }
}

[ComDefaultInterface(typeof(IMyListOfString))]
public class MyListOfString : List<string>, IMyListOfString { }

[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IMyListOfString
{
    [DispId(0)]
    string this[int idx] { get; set; }
}

There's a trick to getting this to work without referencing the managed typelib in VBA (eg, latebound), too, but I've been out of the COM interop world too long to remember what it is.

Answer 2

I found this article describing an ugly hack - calling everything through reflection. Since a generic value is still returned as an object, reflection should work, but will be 1) slow, 2) error prone, and 3) very inconvenient to call.

As the article shows, I would use utility methods with the following signatures:

public object GetProperty(object Instance, string Property)
public object InvokeMethod(object Instance, string Method)
public object InvokeMethod_OneParm(object Instance, string Method, object Parm1)
public object InvokeMethod_TwoParms(object Instance, string Method, object Parm1, object Parm2)
public void SetProperty(object Instance, string Property, object Value)

Ugly, but workable.