VBA - Python - Calling a Python Class using VBA with COM

I have been remiss in not including any Python so far in this blog. Python is a wonderful language with rich libraries and a great active community. VBA can talk to Python either by shelling or via COM. Here we show the minimum amount of code to get a Python COM server running. Unless you are already Python ninja and can tackle missing packages, you'll need Visual Studio 2017 Community Edition (which is free).

#save this file to disk as PythonCOMLib.py

class PythonClass1(object):
    _reg_clsid_ = "{82CF48D3-6826-4FE0-94A6-8EFF656998FB}"
    _reg_progid_= 'PythonLib1.PythonClass1'
    _public_methods_ = ['Greeting']

    def Greeting(self):
        return "Hello world"

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

To get the above code running requires the win32com package to be installed so in Visual Studio 2017 Community Edition go to the Python Environments window, selecting Packages from drop-down and then in the command box type pip install pypiwin32 . Once your packages are installed then the program should run through and you will get a request for admin rights, this is required to register the COM server in the registry. Once done then you can write some client VBA code, here is some

Sub TestPythonClass()
    '* requires Powering up Visual Studio 2017 Community Edition
    '* going to Python Environments window, selecting Packages and then
    '* pip install pypiwin32

    Dim objPython As Object
    Set objPython = CreateObject("PythonLib1.PythonClass1")
    Debug.Print objPython.Greeting()
    
End Sub

In the future, I will include more Python examples now that we've shown they are perfectly callable from VBA.

To avoid the requirement for admin rights one could write the keys to the registry in your own code, see this example

• Python-Programming-on-Win32