VBA calling Python calling back into VBA

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...

1. Pass in the Excel.Application object and call Application.Run with the name of a macro to callback on.
2. 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.
3. 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.
4. 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.