VBA - .NET Interop - use .NET Reflection in VBA

In absence of missing Intellisense use .NET Reflection in VBA to list methods of .NET classes exposed as COM classes

In the previous post, I showed code that can scan the registry for .NET classes exposed as COM classes that are creatable and reflectable, the latter is required because the Intellisense is not working for these objects (a mystery I will solve later, I promise). In this post I give code that can (partially) call .NET Reflection logic.

Missing (VBA) Intellisense for System.Collection.* classes

In the last post I discovered 5 collection .NET classes I want to experiment with, all of them in the System.Collections.* namespace. They are Queue, Stack, SortedList, ArrayList & HashTable . The lack of Intellisense can be really hampering, I know there is online documentation (the preceding list is hyperlinked to the docs) but I'd like to get a grip with what I have to hand. Luckily, even from VBA, we can (partially) call the .Net reflection logic.

But .NET objects have a GetType() method

The secret is the .Net's Type class which we can get for a .NET exposed COM class instance by calling GetType. The Type class can report on its methods by calling Type.GetMethods(). Each method can reports its number of parameters, though the parameter type is oddly not available (an oversight or perhaps I have yet to understand how it works). Each method can report the type of the return value (which makes the missing parameter type info even more odd).

That the parameter type information is missing is why I call this partially calling reflection.

The problem of Overloads

Putting missing parameter information to one side, the count of parameters is useful in itself because there is an issue with calling overloads from VBA. .Net happily accepts overloads, that is to say, methods with the same number of parameters and the same method name but which differ by the type of the parameters. Overloads are not allowed in COM interfaces and this is one reason why the Intellisense is missing (there is yet another reason about which I will post later, I promise).

So how can VBA, a COM client, call various .Net overloads? The answer is that by convention where an overload occurs one appends a suffix to differentiate. So compare foo(Int32 x) with foo_2(Double x).

All this means we can write code to give a picture of what methods a .Net class will accept. The source code is given below, in advance I give the output of the (partial) reflection on the Queue class which is better than the IDL from OLEView (see Appendix A at the bottom).

'                   Int32 get_Count()
'                 Boolean get_IsSynchronized()
'                  Object get_SyncRoot()
'                  Object Clone()
'                    Void Clear()
'                    Void CopyTo(?,?)
'                    Void Enqueue(?)
'             IEnumerator GetEnumerator()
'                  Object Dequeue()
'                  Object Peek()
'                   Queue Synchronized(?)
'                 Boolean Contains(?)
'                Object[] ToArray()
'                    Void TrimToSize()
'                  String ToString()
'                 Boolean Equals(?)
'                   Int32 GetHashCode()
'                    Type GetType()

The source code listing

The following uses a Scripting.Dictionary and so requires a Tools->Reference to Microsoft Scripting Runtime.

The source has comments to document what is going on so I'll not replicate them here. Instead, to give a brief overview I needed one SortedList to keep track of unique methods so I can spot when to use a suffix and I needed a second SortedList to sort the methods into a nice sorted order. I wanted to keep information together so I defined a type and created an array of those types. Enjoy!

Option Explicit

'Tools -> References -> mscorlib.dll

Private Type udtMethodInfo
    '*
    '* package all the working variables into a type
    '*
    DotNetName As String
    ReturnType As String
    HasReturnType As Boolean
    Params As String
    ComName As String '* can be suffixed to differentiate
    ComNameAndParams As String
    ReportLine As String '* nice pretty format
End Type

'*
'* give ourselves an array of types so we can report
'*
Private mauMethods() As udtMethodInfo

Sub Test()
    Dim obj As Object
    '* Component needs to have Category ID (CATID) of {62C8FE65-4EBB-45E7-B440-6E39B2CDBF29}  .NET Cateogry
    '* ComputerHKEY_CLASSES_ROOTComponent Categories{62C8FE65-4EBB-45e7-B440-6E39B2CDBF29}
    
    Dim sProgId As String
    'sProgId = "System.Collections.ArrayList"
    'sProgId = "System.Collections.Stack"
    'sProgId = "System.Collections.HashTable"
    'sProgId = "System.Collections.SortedList"
    sProgId = "System.Collections.Queue"
    
    DotNetReflection sProgId
End Sub

Sub DotNetReflection(Optional ByVal sProgId As String = "System.Collections.Queue")
    '*
    '* Create a .NET object that is exposed to COM,
    '* Get the .NET Type object which gives reflections,
    '* then print out the interface of methods
    '* because sadly no Intellisense (even if early bound to mscorlib.dll!)
    '*
    
    Dim objDotNet As Object
    Set objDotNet = VBA.CreateObject(sProgId)
    
    Dim sortedUniqueMethods As Object  '* this tracks uniqueness
    Set sortedUniqueMethods = CreateObject("System.Collections.SortedList")

    Dim sortedCOMNamesAndParams As Object  '* this sorts for the final listing
    Set sortedCOMNamesAndParams = CreateObject("System.Collections.SortedList")

    Dim typ As mscorlib.Type
    Set typ = objDotNet.GetType()
    
    Dim mi() As mscorlib.MethodInfo
    mi = typ.GetMethods_2()       <--- good example of a suffixed overload!!!
    
    ReDim mauMethods(0 To UBound(mi)) As udtMethodInfo

    Dim idx As Integer
    For idx = 0 To UBound(mi)
    
        Dim miLoop As MethodInfo
        Set miLoop = mi(idx)
        
        mauMethods(idx).DotNetName = miLoop.Name
        mauMethods(idx).ReturnType = miLoop.ReturnType.Name
        mauMethods(idx).HasReturnType = (mauMethods(idx).ReturnType <> "Void")
        
        mauMethods(idx).Params = ListParameters(miLoop)
    
        Call FindUniqueMethodName(sortedUniqueMethods, sortedCOMNamesAndParams, mauMethods(idx), idx)
        
    Next
    
    '*
    '* so we have all the information in the array, mauMethods(lLookup).  But, instead of printing in arrival sequence
    '* we want to print alphabetical order that's why we have sortedCOMNamesAndParams
    '*
    Dim objValueList As Object
    Set objValueList = sortedCOMNamesAndParams.GetValueList()
    
    Dim lLoop As Long
    For idx = 0 To sortedCOMNamesAndParams.Count - 1
        Dim lLookup As Long
        lLookup = objValueList(idx)
        
        Debug.Print "'" & mauMethods(lLookup).ReportLine
    Next

End Sub

Sub FindUniqueMethodName(ByVal sortedUniqueMethods As Object, ByVal sortedCOMNamesAndParams As Object, _
    ByRef puMethod As udtMethodInfo, ByVal idx As Long)
    
    Dim sSig As String
    sSig = Signature2(puMethod.DotNetName, puMethod)
    
    If Not sortedUniqueMethods.ContainsKey(sSig) Then
        '*
        '* no clash, no overload, easy
        '*
        puMethod.ComName = puMethod.DotNetName
    Else

        '*
        '* we have an overload so now loop through adding suffix until unique
        '*
        Dim lLoop As Long
        For lLoop = 2 To 255
            Dim sSuffixedName As String
            sSuffixedName = puMethod.DotNetName & "_" & CStr(lLoop)
            
            sSig = Signature2(sSuffixedName, puMethod)
            If Not sortedUniqueMethods.ContainsKey(sSig) Then
                puMethod.ComName = sSuffixedName
                
                Exit For '* found one so can quit loop
            End If
        Next lLoop
    
    End If
    
    puMethod.ComNameAndParams = puMethod.ComName & puMethod.Params
    puMethod.ReportLine = PadSpacesRightAlign(puMethod.ReturnType, 24) & " " & puMethod.ComNameAndParams
    
    sortedUniqueMethods.Add sSig, idx
    sortedCOMNamesAndParams.Add puMethod.ComNameAndParams, idx
   
End Sub

Function Signature2(ByVal sName As String, ByRef puMethod As udtMethodInfo) As String
    '*
    '* concatenate attributes to help establish uniqueness or overload
    '*
    Signature2 = CStr(puMethod.HasReturnType) & " " & sName & " " & puMethod.Params
End Function

Function ListParameters(mi As MethodInfo) As String
    '*
    '* sadly, yet to figure out how to get the ParamterType TODO fix that
    '*
    Dim paramInfo() As mscorlib.ParameterInfo
    paramInfo() = mi.GetParameters()
    
    Dim lParamCount As Long
    lParamCount = UBound(paramInfo()) - LBound(paramInfo()) + 1

    Dim sReturn As String

    Dim lParamLoop As Long
    For lParamLoop = LBound(paramInfo()) To UBound(paramInfo())
        
        sReturn = sReturn & VBA.IIf(Len(sReturn) > 0, ",", "") & "?"
    
    Next lParamLoop
    
    ListParameters = "(" & sReturn & ")"

End Function

Function PadSpacesRightAlign(s, n) As String
    '*
    '* this helps align the method listing
    '*
    Dim lLen As Long
    lLen = Len(s)
    Dim n2 As Long
    If lLen > n Then n2 = lLen Else n2 = n
    PadSpacesRightAlign = Right(String(n2, " ") & s, n2)
End Function

Appendix A - Queue Class IDL shows no methods

OLEVIWEW.exe is usually the most revealing when looking for interface definitions, but even here the Queue class's IDL shows no clues as to what methods it supports and that is why there is no VBA Intellisense...

    [ uuid(7F976B72-4B71-3858-BEE8-8E3A3189A651), version(1.0), custom(0F21F359-AB84-41E8-9A78-36D110E6D2F9, "System.Collections.Queue") ]
    coclass Queue {
        [default] interface _Queue;
        interface _Object;
        interface ICollection;
        interface IEnumerable;
        interface ICloneable;
    };

    [ odl, uuid(3A7D3CA4-B7D1-3A2A-800C-8FC2ACFCBDA4), 
      hidden, dual, oleautomation, custom(0F21F359-AB84-41E8-9A78-36D110E6D2F9, "System.Collections.Queue") ]
    interface _Queue : IDispatch {
    };

VBA - .NET - Implement IComparable on your VBA classes to use .NET sorter

It is a common question to ask what is the best way to sort in VBA. Best can mean different things to different people. I do not wish to get into religious wars about sort algorithms, I simply give here a way of using a .NET sorter found in the System.Collections.ArrayList class. It requires implementing a standard .NET interface IComparable which is accessed by adding a Tools Reference to mscorlib.dll.

The use case given is a trivial one, we have Employees who have a unique identifier of an EmployeeId by which we will sort them.

Employee Class

First, the Employee class. After adding the Tools Reference to mscorlib.dll add the line Implements IComparable, this will require the implementation of only one method IComparable_CompareTo which must return -1,0 or 1 indicating smaller than, equal to or larger than.

We will sort employees by EmployeeID which will be a 32-bit integer so the implementation of IComparable_CompareTo below is easy. The logic can be very easily expanded to compare multiple variables, so long as the method returns -1,0 or 1.

The objects are held by System.Collections.ArrayList on a late bound basis (i.e. IDispatch) they are also passed in on a late bound basis so the obj parameter needs to be cast to Employee so it can be called for comparison.

Option Explicit

'*Tools->References->mscorlib.dll

Implements IComparable

Public EmployeeID As Long

Private Function IComparable_CompareTo(ByVal obj As Variant) As Long

    '* obj is late bound so we need to cast to get to a callable interface
    Dim oEmployee As Employee
    Set oEmployee = obj
    
    IComparable_CompareTo = (Me.EmployeeID - oEmployee.EmployeeID)

End Function

Test standard module

In a standard module (called anything) paste the following code. We create an instance of mscorlib.ArrayList but despite being early bound there was no Intellisense so we are a little in the dark.

Option Explicit

Function CreateNewEmployee(ByVal lEmployeeId As Long) As Employee
    Set CreateNewEmployee = New Employee
    CreateNewEmployee.EmployeeID = lEmployeeId
End Function

Sub Test()

    Dim oArrayList As mscorlib.ArrayList
    Set oArrayList = New mscorlib.ArrayList

    '* No Intellisense but for list of potential methods try
    '* https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist.add?view=netframework-4.7.2
    oArrayList.Add CreateNewEmployee(1114)
    oArrayList.Add CreateNewEmployee(157)
    oArrayList.Add CreateNewEmployee(623)
        
    oArrayList.Sort

    Debug.Print oArrayList.Item(0).EmployeeID
    Debug.Print oArrayList.Item(1).EmployeeID
    Debug.Print oArrayList.Item(2).EmployeeID

    '* the above prints the following, demonstrating they have been sorted
    '   157
    '   623
    '   1114
End Sub

The code requires little further explanation and should be easy to follow. Simply run Test to see the results. For fun, you can set a break-point in the Employee class to watch the execution dive in and out between the .NET platform and your VBA and witness true VBA / .NET inter-operability.