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.
- Get the IDispatch interface pointer for given VBA class
- Get the class's ITypeInfo interface pointer by calling IDispatch::GetTypeInfo
- Get the type library's (VBA Project's) ITypeLib interface pointer by calling ITypeInfo::GetContainingTypeLib
- Get the total count of types of the type library (VBA Project) by calling ITypeLib::GetTypeInfoCount to set up a For loop
- Use a For loop to iterate over all the types in the type library (VBA Project)
- On each iteration call ITypeLib::GetTypeInfo to get the ITypeInfo interface pointer for each type
- Test the type's Typekind to see if it an enumeration
- 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 EnumThe MoreEnums VBA class
Option Explicit
'* Instancing must be set to '2 - PublicNotCreatable'
Public Enum MyColor
Red = 1
Green
Blue
'Yellow
'Purple
End EnumThe 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 SubSample 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
