Python - Excel - Call Python from worksheet using reflection

Today is final day of Python month on this Excel Development Platform blog where I highlight some Python technologies of interest to Excel (VBA) Developers.

Here I show how one can call Python functions from a worksheet. It requires a VBA pass through function but don't worry we can very easily use reflection to very easily automatically generate the VBA pass through functions.

Python Calculator with Reflection

So I have shown the pattern in the code below many times this month where I define a class and register the COM class so it is creatable and callable from VBA. It is a requirement to maintain a list of public methods for the COM interop to work. We can use this list to filter reflection artefacts.

In the code below pythonSum and pythonMultiply are just example methods which we will call from the worksheet. They are named distinctly to help distinguish from other worksheet functions; you can choose your own naming convention.

What's new in the code below is the reflect method. This method uses Python's inspect module. First, we call inspect.gemembers() but we have to filter these results firstly against our list of _public_methods_ and also to filter out the reflect method itself; this yields a collection of two methods pythonSum and pythonMultiply. Then for both of these methods we call inspect.getargspec() but from the results we remove the leading self argument and keep the remaining arguments.

The method name and argument lists are then packed into a Scripting.Dictionary for consumption in VBA.

class PythonCalculator(object):
    _reg_clsid_ = "{38C05F9C-5FEA-40A3-83B3-FD0E102E97B7}"
    _reg_progid_= 'PythonInVBA.PythonCalculator'
    _public_methods_ = ['pythonMultiply','pythonSum','reflect']

    def reflect(self):
        import inspect 
        import win32com.client
        msDict = win32com.client.Dispatch("Scripting.Dictionary")
        
        for inspectMethodLoop in inspect.getmembers(self):
            if inspectMethodLoop[0] in PythonCalculator._public_methods_:
                if inspectMethodLoop[0]!='reflect':
                    msDict.add(inspectMethodLoop[0],str(inspect.getargspec(inspectMethodLoop[1]).args[1:]))

        return msDict 

    def pythonSum(self,x,y):
        return x+y

    def pythonMultiply(self,a,b):
        return a*b

if __name__=='__main__':
    print ("Registering COM server...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(PythonCalculator)

VBA Code to Auto-generate Pass-Through Worksheet Functions

So on the VBA side we write the following code which calls the reflect method and then processes the results in the Scripting.Dictionary and automatically generates VBA source code for the pass through functions which are then callable from a worksheet. Here is the VBA code...

Sub Test4()
    On Error GoTo ErrHand:

    Dim obj As Object
    Set obj = VBA.CreateObject("PythonInVBA.PythonCalculator")
    
    Dim dictReflect As Object
    Set dictReflect = obj.reflect()
    
    Debug.Print Reflect2("PythonInVBA.PythonCalculator", dictReflect)
    
SingleExit:
    Exit Sub

ErrHand:
    Debug.Print Err.Description
    GoTo SingleExit
End Sub


Function Reflect2(ByVal sClassName As String, ByVal dictReflect As Object)
    Dim sVBACode As String
    
    Dim vKeyLoop As Variant
    For Each vKeyLoop In dictReflect
        Dim sArgsInRoundBrackets As String
        sArgsInRoundBrackets = VBA.Replace(VBA.Replace(VBA.Replace(dictReflect(vKeyLoop), "'", ""), "[", "("), "]", ")")
        Dim sFunc As String
        sFunc = "Function " & vKeyLoop & sArgsInRoundBrackets & vbNewLine
        
        sFunc = sFunc & vbTab & vKeyLoop & " = VBA.CreateObject(""" & sClassName & """)." & vKeyLoop & sArgsInRoundBrackets & vbNewLine
        
        sFunc = sFunc & "End Function" & vbNewLine & vbNewLine
        sVBACode = sVBACode & sFunc
    Next

    Reflect2 = sVBACode
End Function

The output of the above code is some more VBA code sitting in the Immediate window which you should then copy and paste into your source. Here is the output.

Function pythonMultiply(a, b)
    pythonMultiply = VBA.CreateObject("PythonInVBA.PythonCalculator").pythonMultiply(a, b)
End Function

Function pythonSum(x, y)
    pythonSum = VBA.CreateObject("PythonInVBA.PythonCalculator").pythonSum(x, y)
End Function

After pasting the pass through functions into your source the functions are now callable from the worksheet. As proof that this works here is a little screenshot of a worksheet showing the pythonMultiply() function being called.

Final Thoughts

I have seen some Python packages that go to great lengths to make Python callable from the worksheet but this is unnecessary. A trivial amount of VBA that can be very easily generated with reflection gets you there. This took less than an hour to figure out.