In a previous post this month, I wrote that VBA does not have reflection that allows some fancy dependency injection mechanism. This is strictly true of VBA itself but VBA is a COM artefact and reflection interfaces are available as part of the venerable COM specification. Luckily, with the help of some Python we can call some of these reflection interfaces on a VBA class.
| Click here for separate Youtube window |
WARNING - No warranty
WARNING: what follows is a little known technique which I have not run in a production environment; so use at your risk. No warranty is given for any code in this blog post, nor for any blog post. But do let us know how you get on if you chose to use it by commenting below.
The high-level use-case
In this post I'll show enough Python reflection code to drive a better dependency injection mechanism. The high level logic is easy enough to express: all I need is to check a VBA class instance for a certain method named "InjectDependencies" and if found return a list of argument names which will determine what to inject. Seems simple enough but it requires delving into COM reflection interfaces which are typically unknown to a VBA developer.
Strategy - acquiring ITypeInfo from an IDispatch method and calling ITypeInfo methods
In the diagram below the left hand box shows the virtual function table of a VBA class, the class only has three user defined methods, Foo, Bar and Baz but as it is a COM class it also has an implementation of IUnknown. Also because VBA classes can be late bound we know they support IDispatch, the methods of which come after the methods of IUnknown. Then come the user-defined methods, so Foo is in fact the 8th method in this virtual function table.
I want to attract your attention to the IDispatch methods. Maybe you're already familiar with the IDispatch methods GetIDsOfNames and Invoke; these implement late binding. Less well known, I would argue, are the other two IDispatch methods GetTypeInfoCount and GetTypeInfo but I believe these methods deserve high praise and more attention as they return run time type information (RTTI). In fact, I believe these methods are what allow rich reports on a late bound object's properties in the Locals or Watch windows.
In fact, GetTypeInfoCount is just a guard which returns 0 or 1. The real method is GetTypeInfo which returns a pointer to ITypeInfo. Our code will acquire a pointer to ITypeInfo by calling IDispatch::GetTypeInfo.
The Dual Interface and the need to hop from one ITypeInfo to another
If you have worked with the C# reflection classes you will probably smirk at the ITypeInfo interfaces particularly in this next case. A COM class can have dual interface meaning it can be accessed either via IDispatch or through its virtual function table (vtable) interface. Thus, dual interfaces require two separate ITypeInfo interface instances. IDispatch::GetTypeInfo returns a Dispatch ITypeInfo interface instance but we hop to ITypeInfo for the vtable interface instance with a call to GetRefTypeOfImplType and then GetRefTypeInfo. Please don't ask me to defend this API design.
Getting the TypeAttr structure
There is plenty of information on the TypeAttr structure which is acquired by calling ITypeInfo::GetTypeAttr. The Python library will handle releasing the structure's memory. On the TypeAttr structure, we are interested in cFuncs which is the count of functions (aka methods).
Looping through function descriptions and then acquiring parameter names
Knowing the count of functions/methods we can loop through and for each method acquire the function description, FUNCDESC, structure. (Again, the Python layer will release the structure for us.) We call GetDocumentation to examine the method name (first element on the returned tuple). If we find "InjectDependencies" then we take note of the index number; later we call GetNames to return the list of arguments.
The Python Com Server
So what follows is a Python COM Server aka gateway class, I have placed many examples of these on this blog. But for those new to this, this will implement a COM server that is create-able from VBA using CreateObject(). In the class below the code maintains a log because it can be quite difficult to communicate what is going on without a log.
The strategy outlined above is implemented in ReadParams() which takes a single parameter (the self keyword is what holds the state of a a Python class instance), o which should be a VBA class instance. Please pass a real VBA class and not a document class such as ThisWorkbook or Sheet1. Also for the VBA class the Instancing needs to be '2 - PublicNotCreatable' for reasons I have yet to confirm.
The opening lines of ReadParams() are defensive type checking. The key call is GetTypeInfo() which returns a pointer to ITypeInfo. If you know the strategy, the rest of the code should be easy to follow.
If you have written .NET reflection code in C# then you are lucky, it is very intuitive and easy to understand. The COM reflection interfaces are very far from intuitive, IMHO; especially if you are writing in C++. The COM reflection API can be quite painful. So be thankful that some Python contributors (Tim Golden et al.) have added a layer to insulate you from the C++ complexities. For more information here is the Python documentation.
Upon re-reading my code, I think a few lines could be taken out to make it tighter, perhaps quit the loop early if a method match is found.
Finally, on a COM interop note, Python tuples need to be converted to lists for passing back to the calling VBA, where they are marshalled to a Variant array.
import pythoncom
class PythonDependencyInjectionHelper(object):
_reg_clsid_ = "{2AFE4143-AC58-4D1F-A172-9D20C917D13A}"
_reg_progid_ = 'PythonInVBA.PythonDependencyInjectionHelper'
_public_methods_ = ['ReadParams','ClearLog']
_public_attrs_ = ['Log']
_readonly_attrs_ = ['Log']
def __init__(self):
self.Log = ""
def ClearLog(self):
self.Log = ""
def ReadParams(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"
ta = ti.GetTypeAttr()
tk = ta.typekind
if tk == 4:
try:
tivt = ti.GetRefTypeInfo(ti.GetRefTypeOfImplType(-1))
tavt = tivt.GetTypeAttr()
except Exception as ex:
raise Exception("Error whilst acquiring vtable interface " + str(ex))
try:
idx = -1
for funcIdx in range(0, tavt.cFuncs):
fd = tivt.GetFuncDesc(funcIdx)
if (ti.GetDocumentation(fd.memid)[0] == "InjectDependencies"):
idx = funcIdx
except Exception as ex:
raise Exception("Error whilst looping through functions " + str(ex))
if (idx != -1):
fd = tivt.GetFuncDesc(idx)
return list(tivt.GetNames(fd.memid)[1:])
except Exception as e:
self.Log += "Error: " + str(e) + "\n"
def RegisterThis():
print("Registering COM servers...")
import win32com.server.register
win32com.server.register.UseCommandLine(PythonDependencyInjectionHelper)
if __name__ == '__main__':
RegisterThis()
print("End of execution")The TaxCalculator business logic class
Lets give some client VBA code, enough to drive the demo so I have a business logic class called TaxCalculator which has a method called InjectDependencies() which has two arguments oLogger and oIdentity. It has some simple business logic but that's not the real focus, the real focus is the injection of dependencies.
Option Explicit
Private mobjLogger As Object
Private mobjIdentity As Object
Public Sub InjectDependencies(oLogger, oIdentity)
Set mobjLogger = oLogger
Set mobjIdentity = oIdentity
End Sub
Public Function CalculateTax(ByVal lAmount As Long) As Long
If mobjIdentity.HasPermission("Taxcalc") Then
CalculateTax = lAmount * 0.2
mobjLogger.Log "Authorised, Calculated tax at 20%"
Else
mobjLogger.Log "Not authorised to run this"
End If
End FunctionThe Calling VBA Code
Private Function ReadDependencies(ByVal oVBAClass As Object) As Variant
ReadDependencies = CreateObject("Scripting.Dictionary").Keys '# sets a default return value for error cases
Static oHelper As Object
If oHelper Is Nothing Then Set oHelper = VBA.CreateObject("PythonInVBA.PythonDependencyInjectionHelper")
oHelper.ClearLog
If oVBAClass Is Nothing Then Err.Raise vbObjectError, "", "#Null oVBAClass!"
On Error GoTo PythonComInteropErrorHandler
Dim vDependencies
vDependencies = oHelper.ReadParams(oVBAClass)
If Not IsNull(vDependencies) Then ReadDependencies = vDependencies
Exit Function
PythonComInteropErrorHandler:
If Err.Number = 98 Then
Err.Raise vbObject, "", "#oVBAClass of type '" & TypeName(oVBAClass) & "' 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 TestReadDependencies()
Dim objVBAClass As Object
Set objVBAClass = New TaxCalculator
Dim vDependencies
vDependencies = ReadDependencies(objVBAClass)
Debug.Print Join(vDependencies, vbNewLine)
End Sub
No comments:
Post a Comment