Can VBA enumerate a COM object's methods or fields?

Question

I have a COM object (built with C#.NET) I'm using in VBA (Excel) and it would be really nice enumerate the COM object's fields and reference them automatically. In .NET, this could be done with reflection. Is there any way to do this in VBA?

So, instead of

Dim x As MyCOMObject
Set x = New MyCOMObject
x.f1 = 1
x.f2 = 2
x.f3 = 3

Something more like:

Dim x As MyCOMObject
Set x = New MyCOMObject
For i = 0 to COMFieldCount(x) - 1
    SetCOMField(x, GetCOMFieldName(i), i+1)
Next i

I would not even do this in C# using reflection. Have you thought about making a function that does this ? — parapura rajkumar, Nov 09 '11 at 02:01
You might want to look at this question: http://stackoverflow.com/questions/547903/self-inspection-of-vb6-udts — GTG, Nov 09 '11 at 13:02
@parapurarajkumar, why wouldn't you do this in C# using reflection? I am trying to make a function that does this, hence the question. — Ben, Nov 10 '11 at 01:18
@GTG, thanks for the link -- the DLL is hard to find through official channels and doesn't seem to match up to the examples exactly, but I think that's going to be the answer. — Ben, Nov 10 '11 at 01:18
Unfortunately, @GTG's link does not work for me. Oddly, VarType(x) equals vbString (8). But it's clearly not a string; I can access its fields and methods explicitly. TLI.TypeInfoFromRecordVariant(x) returns Nothing. — Ben, Nov 10 '11 at 02:05

score 1 · Answer 1 · edited Aug 06 '14 at 15:19

You will probably need to refine this code a bit, but it does roughly what you are looking for. First, you need to add reference to "Typelib information", TLBINF32.dll. I'm not sure if this is a part of Windows or came with some of the numerous SDKs I have installed on my machine, but it is in the System32 folder.

I am assuming that you are setting properties of a COM object, so you will be calling "property put" functions to set the values of your object. You may need to check for the datatypes of those properties, I haven't done any datatype conversion in my code.

The code looks like this:

'Define the variables
Dim tliApp As TLI.TLIApplication
Dim typeinfo As TLI.typeinfo
Dim interface As TLI.InterfaceInfo
Dim member As TLI.MemberInfo

'Initialize typelib reflector
Set tliApp = New TLI.TLIApplication
'Get the type information about myObject (the COM object you want to process)
Set typeinfo = tliApp.ClassInfoFromObject(myObject)

'Set all properties of all the object's interfaces
For Each interface In typeinfo.Interfaces
    For Each member In interface.Members
        'If this is a "property put" function
        If member.InvokeKind = INVOKE_PROPERTYPUT Then
            'Invoke the mebmer and set someValue to it.
            'Note that you'll probably want to check what datatype to use and do some more error checking
            CallByName myObject, member.Name, VbLet, someValue
        End If
    Next
Next

CallByName is a very useful routine I didn't know about. However, the enumeration doesn't work for my object as the interfaces recognized are just IDispatch (QueryInterface, AddRef, etc) and a few extras. The two interfaces retrieved by ClassInfoFromObject on my object are _MyObjectType and _Object. The first is simply an IDispatch interface. The second also contains the members ToString, Equals, GetHashCode, and GetType. Furthermore, I'd like to set fields rather than properties; in my C# module, I have declared, for instance, public double aDoubleField; — Ben, Nov 11 '11 at 17:34

Can VBA enumerate a COM object's methods or fields?

1 Answers1