I love finding rare stuff that works in Excel VBA, this blog is full of it! I love finding new useful type libraries of classes which drive functionality I didn't know was possible for a VBA developer. They turn up infrequently on Stack Overflow and other forum websites. Often the sample code creates an object using late binding and not with the type library. When I discover one of these I instantly want to find the containing type library to see what else is in that library. Such an investigation requires
Looking up the ProgID (the text string passed to CreateObject()) to get a CLSID
Looking up the CLSID to get the LIBID, the type library's guid
Looking up the LIBID to get the type library's full name
So for example an interesting component is created with CreateObject("WIA.ImageFile") that has a clsid of {A2E6DDA0-06EF-4df3-B7BD-5AA224BB06E8}, and type library guid of {94A0E92D-43C0-494E-AC29-FD45948A5221} which has the full name of Microsoft Windows Image Acquisition Library v2.0.
Combing though the registry is a pain so fortunately we can write some Python code and use the reflection interfaces, ITypeInfo and ITypeLib which are usually restricted to C++ developers but which Python developers have access to and in turn we can expose to Excel VBA code.
Below is a Python COM Gateway class, I have supplied many examples on this blog so the procedure should be familiar (to regular readers at least). But to recap you need to run the following script with adminstrator privileges from a command console. Once registered, the class becomes creatable from VBA using CreateObject.
class WhichTypeLibrary(object):
_reg_clsid_ = "{521B0A5A-4359-4874-AA9E-8F99DB35F4A6}"
_reg_progid_ = 'PythonInVBA.WhichTypeLibrary'
_public_methods_ = ['ReportTypeLibrary']
def ReportTypeLibrary(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:
return "Error, cannot get ITypeInfo interface."
typelib = ti.GetContainingTypeLib()[0]
return (typelib.GetDocumentation(-1)[0] + ":\t\t" + typelib.GetDocumentation(-1)[1] )
except Exception as e:
return "Error: " + str(e) + "\n"
def RegisterThis():
print("Registering COM servers...")
import win32com.server.register
win32com.server.register.UseCommandLine(WhichTypeLibrary)
if __name__ == '__main__':
RegisterThis()
print("End of execution")
VBA Client Code
So the calling VBA code is shown below. It shows passing a Workbook object to the library finder which correctly reports it as belonging the Excel type library (that's obvious but it proves the logic is working). A second object is created which is far rarer (certainly I'd not heard of it) and the Python code correctly reports the library prefix, WIA, and the library description, 'Microsoft Windows Image Acquisition Library v2.0', which can then be found in the Tools References dialog box.
Option Explicit
Sub Test()
On Error GoTo ErrHandler
Dim oLibFinder As Object
Set oLibFinder = CreateObject("PythonInVBA.WhichTypeLibrary")
'* a simple example, find the containing type library of a Workbook, should print "Excel: Microsoft Excel 15.0 Object Library"
Debug.Print oLibFinder.ReportTypeLibrary(ThisWorkbook)
'* a rarer example, should print "WIA: Microsoft Windows Image Acquisition Library v2.0"
Dim obj As Object
Set obj = CreateObject("WIA.ImageFile")
Debug.Print oLibFinder.ReportTypeLibrary(obj)
SingleExit:
Exit Sub
ErrHandler:
Debug.Print Err.Description
Stop
'Resume
End Sub
So the above program prints...
Excel: Microsoft Excel 15.0 Object Library
WIA: Microsoft Windows Image Acquisition Library v2.0
Factoid - ProgIDs have limit of 39 characters
What's Next, can the above Python class report on itself? No (or at least not yet).
If you are a little cheeky and wondered what would happen if you pass the library finder class itself to see what it said you would get an error.
This is because the standard Python COM Gateway class carries no type information by default. It is capable of shipping type information is you associate it with a type library but then the type library needs to agree with the Python source. We can write some code to read a Python class and generate a type library and that will be the subject of the next post.
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).
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 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
