VBA - Reflection - use Python to write your enumeration helper functions

This is second blog post demonstrating how we can use Python code to leverage a C++ reflection API with respect to a VBA project and thus confer the capabilities of reflection to a VBA programmer where no native VBA functionality exists. In other words, reflection is not normally available to a VBA developer but with some clever code we can fix that. The use case this time is converting enumerations to strings (and back).

Click here for separate Youtube window

So in the previous post I highlighted how there is no in built VBA language feature to give the string equivalent of an enumeration value; and that one had to write a helper function. But, the helper function has to be kept synchronised which can be a little painful. Luckily, we can use some more Python reflection code to help with this.

You are strongly advised to first read the prior article on Python reflection where a simpler version of the diagram below is introduced. In this post, we continue to flesh out our diagram with more hyperlinks for methods and structures we're going to use. New on this diagram is the ITypeLib interface (rightmost box). One can acquire a reference to a class's containing type library (VBA project) via the class's ITypeInfo interface by calling ITypeInfo::GetContainingTypeLib.

Being able to acquire the containing type library (VBA project) is an important advance. In the prior reflection blog post we got run-time type information (RTTI) for a single VBA class instance. But now we have the capability to interrogate the whole type library (VBA project). In this post I will give code which will find all the enumerations and write some helper functions to convert enumeration values to and from strings. I'm sure, I will blog other use cases that will make use of 'type-library-wide' information.

Another addition to the diagram below is the link to the VARDESC structure. The documentation for that structure is unwelcoming (to a VBA programmer at least), don't worry that documentation is typically for C++ programmers. We will be using a Python layer called pythoncom authored by Tim Golden. The Python layer is beautiful to work with. Thanks Tim!

Python Reflection code to query a Type Library (VBA Project) for all enumerations

So we are in a position to give the strategy for listing all the enumerations in a type library (VBA Project) given a VBA class instance from that type library.

1. Get the IDispatch interface pointer for given VBA class
2. Get the class's ITypeInfo interface pointer by calling IDispatch::GetTypeInfo
3. Get the type library's (VBA Project's) ITypeLib interface pointer by calling ITypeInfo::GetContainingTypeLib
4. Get the total count of types of the type library (VBA Project) by calling ITypeLib::GetTypeInfoCount to set up a For loop
5. Use a For loop to iterate over all the types in the type library (VBA Project)
6. On each iteration call ITypeLib::GetTypeInfo to get the ITypeInfo interface pointer for each type
7. Test the type's Typekind to see if it an enumeration
8. For each enumeration run our code to generate some VBA enumeration helpers function

PythonVBAEnumHelper.py, houses the Python COM Gateway class

So this is the Python code. It needs to be run at least once from a command line with administrator rights so that the registry can be updated. Once registered then simple use VBA.CreateObject("PythonInVBA.PythonVBAEnumHelper") to instantiate this Python class and then call the WriteMyEnumHelpers method.

import pythoncom

class PythonVBAEnumHelper(object):
    _reg_clsid_ = "{232D07E5-4BCE-4FB9-93DC-2F6B58B809F7}"
    _reg_progid_ = 'PythonInVBA.PythonVBAEnumHelper'
    _public_methods_ = ['ClearLog','ReadEnums','ReadEnum','WriteMyEnumHelpers','WriteMyEnumHelper'] 
    _public_attrs_ = ['Log']
    _readonly_attrs_ = ['Log']

    def __init__(self):
        self.Log = ""

    def ClearLog(self):
        self.Log = ""

    def GetTypeLibrary(self, o):
        try:
            pt = str(type(o))
            if pt == "<class 'win32com.client.CDispatch'>":
                ti = o._lazydata_[0]
            elif pt == "<class 'PyIDispatch'>" :
                ti = o.GetTypeInfo()
            else:
                self.Log += "called with type " + pt + " no attempt to acquire typeinfo\n"
                return None

            self.Log += "Acquired typeinfo:" + ti.GetDocumentation(-1)[0] + "\n"
            typelib = ti.GetContainingTypeLib()[0]
            self.Log += "Acquired containing typelib:" + typelib.GetDocumentation(-1)[0] + "\n"

            return typelib

        except Exception as e:
            self.Log += "Error: " + str(e) + "\n"


    def WriteMyEnumHelpers(self,o):
        try:
            helpers = []
            enums = self.ReadEnums(o,True)
            if not (enums is None):
                for enum in enums:
                    helper = self.WriteMyEnumHelper(enum)

                    if not (helper is None):
                        helpers.append(helper)

                return list(helpers)

        except Exception as e:
            self.Log += "Error: " + str(e) + "\n"

    def WriteMyEnumHelper(self,enum):
        try:
            if not (enum is None):

                vbaStringToEnumFuncName = enum[0] + "StringToEnum"
                vbaStringToEnumFuncSrc = "Public Function " + vbaStringToEnumFuncName + "(s As String) As " + enum[0] + "\n\t" + vbaStringToEnumFuncName + " = "

                vbaEnumToStringFuncName = enum[0] + "EnumToString"
                vbaEnumToStringFuncSrc = "Public Function " + vbaEnumToStringFuncName + "(e As " + enum[0] + ") As String\n\t" + vbaEnumToStringFuncName + " = "
                
                stringToEnumSwitch = ""
                enumToStringSwitch = ""

                srcArray = ""
                for enumMem in enum[2]:
                    if (stringToEnumSwitch != ""):
                        stringToEnumSwitch+=", "
                        enumToStringSwitch+=", "

                    stringToEnumSwitch+= "s = \"" + enumMem[0] + "\", " + str(enumMem[1])
                    enumToStringSwitch+= "e = " + str(enumMem[1]) + ", \"" + enumMem[0] + "\""

                vbaEnumToStringFuncSrc+=" Switch(" + enumToStringSwitch + ")\nEnd Function\n"
                vbaStringToEnumFuncSrc+=" Switch(" + stringToEnumSwitch + ")\nEnd Function\n"

                return (vbaEnumToStringFuncSrc + vbaStringToEnumFuncSrc)
            else:
                return "Something went wrong"
        except Exception as e:
            self.Log += "Error: " + str(e) + "\n"



    def ReadEnums(self, o, readMembers):
        try:
            typelib = self.GetTypeLibrary(o)
            if not (typelib is None):

                enums = [[]]

                for index in range(0, typelib.GetTypeInfoCount()):
                    ti = typelib.GetTypeInfo(index)
                    ta = ti.GetTypeAttr()
                    tk = ta.typekind
                    if tk == 0:  # 0=ENUMERATION
                        self.Log += "Found enum:" + ti.GetDocumentation(-1)[0] + "\n"
                        members = None
                        if (readMembers):
                            members = self.ReadEnum(o,index)
                        tup = (ti.GetDocumentation(-1)[0],index, members)
                        enums.append(tup)

                return enums

        except Exception as e:
            self.Log += "Error: " + str(e) + "\n"

    def ReadEnum(self, o, index):
        try:
            typelib = self.GetTypeLibrary(o)
            
            if not (typelib is None):
                ti = typelib.GetTypeInfo(index)
                ta = ti.GetTypeAttr()
                count = ta.cVars
                enumMems = []
                self.Log += "Enum count:" + str(count) + "n"
                for memberIndex in range(0, count):
                    varDesc = ti.GetVarDesc(memberIndex)
                    enumMems.append((ti.GetDocumentation(varDesc.memid)[0],varDesc.value))

                return list(enumMems)

        except Exception as e:
            self.Log += "Error: " + str(e) + "\n"


def RegisterThis():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(PythonVBAEnumHelper)


if __name__ == '__main__':
    RegisterThis()
    print("End of execution")

VBA classes

I have contrived to split the enums over two classes. This is to demonstrate that the Python code can interrogate all Instancing '2 - PublicNotCreatable' classes in a type library (VBA Project) given just a single class instances. So forgive me if this looks a little odd. Also, do please note that enums defined is either a (i) standard module or (ii) a class with Instancing '1 - Private' will not be found. So you'll need two separate classes, Enums and MoreEnums both with Instancing '2 - PublicNotCreatable'.

The Enums VBA class

Option Explicit
'* Instancing must be set to '2 - PublicNotCreatable'

Public Enum Cars
    BMW
    Ford
    Lotus
    'Ferrari
End Enum

The MoreEnums VBA class

Option Explicit
'* Instancing must be set to '2 - PublicNotCreatable'

Public Enum MyColor
    Red = 1
    Green
    Blue
    'Yellow
    'Purple
End Enum

The VBA Client code

So the Python code does the clever stuff with reflection but we still need some VBA client code to call into the Python COM server.

Option Explicit

Private Function WriteEnumsHelpers(ByVal oAnyPublic2VBAClass As Object)
    
    Static oHelper As Object
    If oHelper Is Nothing Then Set oHelper = VBA.CreateObject("PythonInVBA.PythonVBAEnumHelper")
    oHelper.ClearLog
    If oAnyPublic2VBAClass Is Nothing Then Err.Raise vbObjectError, "", "#Null oAnyPublic2VBAClass!"

    On Error GoTo PythonComInteropErrorHandler
    WriteEnumsHelpers = oHelper.WriteMyEnumHelpers(oAnyPublic2VBAClass)
    'Debug.Print oHelper.Log
    
    Exit Function
PythonComInteropErrorHandler:
    If Err.Number = 98 Then
        Err.Raise vbObject, "", "#oVBAClass of type '" & TypeName(oAnyPublic2VBAClass) & "' must have Instancing '2 - PublicNotCreatable'!"
    Else
        Debug.Print Err.Description, Hex$(Err.Number), Err.Source
        Debug.Print "Log:" & oHelper.Log
    End If

End Function

Private Sub TestWriteEnumsHelpers()

    Dim oAnyPublic2VBAClass As Object
    Set oAnyPublic2VBAClass = New Enums

    Debug.Print VBA.Join(WriteEnumsHelpers(oAnyPublic2VBAClass), vbNewLine)
    
End Sub

Sample output

So the Python code actually generates VBA code for copying and pasting into the VBA project to help with your enumeration to string (and back again) logic.

Public Function CarsEnumToString(e As Cars) As String
    CarsEnumToString =  Switch(e = 0, "BMW", e = 1, "Ford", e = 2, "Lotus")
End Function
Public Function CarsStringToEnum(s As String) As Cars
    CarsStringToEnum =  Switch(s = "BMW", 0, s = "Ford", 1, s = "Lotus", 2)
End Function

Public Function MyColorEnumToString(e As MyColor) As String
    MyColorEnumToString =  Switch(e = 1, "Red", e = 2, "Green", e = 3, "Blue")
End Function
Public Function MyColorStringToEnum(s As String) As MyColor
    MyColorStringToEnum =  Switch(s = "Red", 1, s = "Green", 2, s = "Blue", 3)
End Function