In this post I again show a Python COM gateway class callable from VBA; this time it demonstrates calling back into the VBA using Application.Run, ThisWorkbook or a VBA-defined class. It does this by ensuring a win32com.client.Dispatch wrapper. It also demonstrates code to report all the local variables for debugging purposes. Further, it demonstrates running the COM server in a separate process.
Calling back using COM
So, I need some code to callback into VBA from a Python COM component, the mechanism will clearly be another COM call. Let's list the COM ways we can do this...
- Pass in the Excel.Application object and call Application.Run with the name of a macro to callback on.
- Write callback code in the ThisWorkbook module and pass in the Workbook object and have Python call a method in the workbook's ThisWorkbook module.
- Write callback code in a VBA defined class and pass in instance of such a class and have Python call a method on that instance.
- Write code to implement COM events, i.e. a source interface and declare a variable in VBA using the WithEvents keyword to sink the events.
I have implemented the top three in the code below. If you want COM events you'll need a Type Library, but Python does not supply one automatically; I did write some code to use Python reflection to generate a type library but this is not out of the box functionality from Python. Never mind, a callback routine in Excel could itself raise events if you really wants events; after all, COM events are simply callbacks to multiple listeners instead of a single listener.
For some very odd reason the third case, the VBA defined case had a bug/glitch. It would report not just the return value but instead a tuple of the return value and all the input parameters. This is a pitfall but easily solved and not a problem once one is aware that the problem exists.
Ensure Dispatch
The key tip in the code with regard to calling back is to wrap whatever is passed in from VBA in a win32com.client.Dispatch wrapper. In testing however, sometimes I use an object which I have acquired from the Running Object Table which already comes wrapped. So I wrote a small class called the DispatchEnsurer to inspect what it has been given and wrap where necessary.
Inability to Attach the Microsoft Visual Studio debugger
So I am embarrassed to admit I cannot get the Microsoft Visual Studio debugger to attach to the Excel session and specifically hit breakpoints in my Python code. This means stepping through the code as initiated by a call from Excel VBA is impossible. If you know how to do this please leave a comment. In the meantime I had to write a class to report all the local variables for a method into a nicely formatted string and pass that back to the calling VBA so I can see what is going on (see below). I would point out that it is still possible to acquire a workbook object from the running object table using win32com.client.GetObject(<<workbook full file path>>) and that is primarily how one can get around this problem.
Perhaps I should report this breakpoint problem as a bug to Microsoft's Visual Studio team. I do not believe it is Python's fault.
Declarative Python COM server registration
So the breakpoint problem above was quite painful. Another painful problem was updating the Python script and have Excel VBA pick up the changes. In some instances I had to change the COM's class's CLSID to force the Excel VBA client code to pick up the script changes. Other tactics involved shutting down Excel and reopening but this didn't always work as I believe there is some sort of session recycling (I'll investigate this at some point). Anyway, I found the best way for rapid iterative development without re-registering and without rebooting Excel was to make the Python COM server sit in a separate process and then kill that process to pick up script changes.
Getting a COM server to sit in its own process requires adding the following line of code to your Python COM gateway class
_reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER
Adding one single line of source to effect this change is quite impressive. To get C# to do this requires a degree of fiddling around in the registry plus some extra code, so Python deserves praise for this declarative simplicity.
With this extra line of code the Python executables will not be loaded into the Excel.exe process but instead loaded into a pythonw.exe process. So this means we can kill the pythonw.exe process and force Excel VBA/COM runtime to spawn a new one that will effect any new script changes. Here is some VBA code to kill the pythonw.exe process...
Sub PickupNewPythonScript()
'# for development only to help pick up script changes we kill the python process
Call CreateObject("WScript.Shell").Run("taskkill /f /im pythonw.exe", 0, True)
End Sub
So with the ability to kill the Python process and force it to pick up new changes without rebooting Excel and without incrementing CLSIDs and re-registering meant I could start to get some work done!
Reporting all the local variables
As mentioned above without the ability to step through the code one loses the ability to see the values of the local variables. Luckily and to Python's credit there is a locals() dictionary which contains all the local variables; we can print this although I added some extra line breaking and formatting. I give a formatting class in the code below. I use this to return all the local variables when an error occurs. This allows me to debug in the absence of proper/formal debugger support.
VBA Class use case returns tuple of return value and input arguments
I can only assume that this is a bug or a glitch but in the case of the VBA defined class I found that the return value was returned in a tuple that also consisted on the input arguments. Not a real problem as we simply access the tuple's first element with [0].
Python Code
The Python code is one single listing. Run this within Microsoft Visual Studio or from the command line. One needs to run it at least once to register the COM class registration. If you run without Administrator privileges it will request to elevate.
import win32com.server.register
from win32com.client import Dispatch
import pythoncom
class CallingBackIntoVBA(object):
_reg_clsid_ = "{352B1FE3-8F8E-478B-93D0-A5AAC612D09A}"
_reg_progid_ = 'PythonInVBA.CallingBackIntoVBA'
_reg_desc_ = "Demonstrates a Python COM server calling back into calling VBA code"
_public_methods_ = ['DemoAppRunCallback','DemoThisWorkbookCallback', 'DemoClassCallback']
_reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER
def DemoClassCallback(self, classInstance, arg0=None, arg1=None, arg2=None):
try:
dispCb = DispatchEnsurer.EnsureDispatch(classInstance)
# for some reason a tuple of the return value and arguments is
# returned, very odd!!
return dispCb.ClassCallback2(arg0,arg1,arg2)[0] # [0] gets first element of the tuple
except Exception as ex:
return LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
def DemoAppRunCallback(self,excelApplication, appRunMacro: str, arg0=None, arg1=None, arg2=None):
try:
dispApp = DispatchEnsurer.EnsureDispatch(excelApplication)
return dispApp.Run(appRunMacro, arg0,arg1,arg2)
except Exception as ex:
return LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
def DemoThisWorkbookCallback(self, workbook, arg0=None, arg1=None, arg2=None):
try:
dispWb = DispatchEnsurer.EnsureDispatch(workbook)
#dispWb.madeUpName()
return dispWb.ThisWorkbookCallback2(arg0,arg1,arg2)
except Exception as ex:
return LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
class LocalsEnhancedErrorMessager(object):
@staticmethod
def Enhance(ex, localsString):
locals2 = "n Locals:{ " + (",n".join(localsString[1:-1].split(",")) ) + " }"
if hasattr(ex,"message"):
return "Error:" + ex.message + locals2
else:
return "Error:" + str(ex) + locals2
class DispatchEnsurer(object):
@staticmethod
def EnsureDispatch(comObj):
""" Sometimes we get a PyIDispatch so we'll need to wrap it, this class takes care of that contingency"""
try:
dispApp = None
apptypename = str(type(comObj))
if apptypename == "<class 'win32com.client.CDispatch'>":
# this call from GetObject so no need to Dispatch()
dispApp = comObj
elif apptypename == "<class 'PyIDispatch'>":
# this was passed in from VBA so wrap in Dispatch
dispApp = Dispatch(comObj)
else:
# other cases just attempt to wrap
dispApp = Dispatch(comObj)
return dispApp
except Exception as ex:
if hasattr(ex,"message"):
return "Error:" + ex.message
else:
return "Error:" + str(ex)
def RegisterCOMServers():
print("Registering COM servers...")
import win32com.server.register
win32com.server.register.UseCommandLine(CallingBackIntoVBA)
def TestCallingBackIntoVBA():
import win32com
cb = CallingBackIntoVBA()
wb = win32com.client.GetObject(r"C:\Users\Simon\Downloads\PythonVBACallbackDemo.xlsm")
app = wb.Parent
sb = app.StatusBar
ret = cb.DemoAppRunCallback(app,"PythonVBACallbackDemo.xlsm!Module1_Proc1",0,1,2)
print(ret)
if __name__ == '__main__':
pass
RegisterCOMServers()
#TestCallingBackIntoVBA()
VBA Code Listings
Whereas the Python code is a single listing the VBA code is in contrast dispersed across several modules, please place into one workbook. I called mine PythonVBACallbackDemo.xlsm.
ThisWorkbook module
Place the following code in the ThisWorkbook module
Public Function ThisWorkbookCallback1()
Debug.Print "ThisWorkbookCallback1"
End Function
Public Function ThisWorkbookCallback2(arg0, arg1, arg2)
ThisWorkbookCallback2 = CStr(arg0) & " " & CStr(arg1) & " " & CStr(arg2)
Debug.Print "ThisWorkbook.ThisWorkbookCallback2"
End Function
Callback Class
Place the following code in a VBA class named Callback with its Instancing type set to 2 - PublicNotCreatable
Public Function ClassCallback1()
Debug.Print "ClassCallback1"
End Function
Public Function ClassCallback2(arg0, arg1, arg2) As String
ClassCallback2 = CStr(arg0) & " " & CStr(arg1) & " " & CStr(arg2)
Debug.Print "Callback.ClassCallback2"
End Function
Standard module code
Place the following code in a standard (non-class module), it doesn't matter what you name this module but I called mine modCallbackDemos . The procedures to run (by placing cursor and pressing F5) are ThisWorkbookCallbackDemo(), AppRunMacroDemo() and ClassCallbackDemo().
Option Explicit
Sub PickupNewPythonScript()
'# for development only to help pick up script changes we kill the python process
Call CreateObject("WScript.Shell").Run("taskkill /f /im pythonw.exe", 0, True)
End Sub
Sub ThisWorkbookCallbackDemo()
Dim obj As Object
Set obj = VBA.CreateObject("PythonInVBA.CallingBackIntoVBA")
Debug.Print obj.DemoThisWorkbookCallback(ThisWorkbook, 0, 1, 2)
Set obj = Nothing
End Sub
Sub AppRunMacroDemo()
'Stop
Dim obj As Object
Set obj = VBA.CreateObject("PythonInVBA.CallingBackIntoVBA")
Dim sAppRunMacro As String
sAppRunMacro = ThisWorkbook.Name & "!Module1_Proc1"
Dim vRet As Variant
vRet = obj.DemoAppRunCallback(Application, sAppRunMacro, 0, 1, 2)
Debug.Print vRet
Set obj = Nothing
End Sub
Function Module1_Proc1(arg0, arg1, arg2)
Dim sRet
sRet = "Module1_Proc1 running"
Debug.Print sRet
Module1_Proc1 = sRet & " foobar"
End Function
Sub ClassCallbackDemo()
Dim obj As Object
Set obj = VBA.CreateObject("PythonInVBA.CallingBackIntoVBA")
Dim oCallback As Callback
Set oCallback = New Callback
Debug.Print obj.DemoClassCallback(oCallback, 0, 1, 2)
Set obj = Nothing
End Sub
So if I run ThisWorkbookCallbackDemo() then the expected output is
ThisWorkbook.ThisWorkbookCallback2
0 1 2
And if I run AppRunMacroDemo() then the expected output is
Module1_Proc1 running
Module1_Proc1 running foobar
And finally if I run ClassCallbackDemo() then the expected output is
Callback.ClassCallback2
0 1 2
Amending the script and picking up changes
So in the above code listing there is a routine PickupNewPythonScript() which I use to avoid having to reboot Excel. You might like to experiment with it, so make a change in the Python script, save your Python script changes then run PickupNewPythonScript() and see your changes effected. I have added a commented out line of code in the Python script
#dispWb.madeUpName()
which if uncommented and effected will mean the expected output of running ThisWorkbookCallbackDemo() changes to
Error:<unknown>.madeUpName
Locals:{ 'self': <CallingBackIntoVBA.CallingBackIntoVBA object at 0x030224D0>,
'workbook': <PyIDispatch at 0x009EDF70 with obj at 0x009301BC>,
'arg0': 0,
'arg1': 1,
'arg2': 2,
'dispWb': <COMObject <unknown>>,
'ex': AttributeError('<unknown>.madeUpName') }
thus demonstrating the local variables report.
Enjoy!
Final thoughts
If I could express a preference for which pattern to use I would pass in the Excel Application and use Application.Run. This is because the Application object is most durable. After all, workbooks can be unloaded meaning the callback target could go missing and you are calling on a null (stale) pointer. Moreover, an instance of a VBA class is even more temporary in that class instances can be torn down during a state loss.
I am pleased about running the Python COM server in a separate process and then killing it to spawn a refreshed and updated new instance picking up code changes. This is a major productivity boon.
