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 FunctionAppendix 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 {
};
