Python - VBA - Com Interop - Factory Method Pattern

Python can interop with VBA via COM. One can create a Python class and expose it to VBA for scripting, this allows VBA developers to access the massive Python ecosystem. Sometimes, if you want to build an object hierarchy then you want some classes to be only creatable via another class, this is sometimes called the Factory Method Pattern. It can frequently occur in parent-child object relationships. In this post I show how to do this in Python.

Here is the Python code which must be run at least once with admin privilege to ensure COM registration (thereafter not required).

import win32com.client


class MyParent(object):
    _reg_clsid_ = "{C61A7C6E-B657-4D55-AD36-8850B2E501AC}"
    _reg_progid_ = 'PythonInVBA.MyParent'
    _public_methods_ = ['Greet', 'GetChild']

    def __init__(self):  # Rules of Com say parameterless constructors
        self.child = win32com.client.Dispatch("PythonInVBA.MyChild")
        self.child.SetName("foo")

    def Greet(self):
        return "Hello world"

    def GetChild(self):
        return self.child


class MyChild(object):
    _reg_clsid_ = "{15DAAEE2-3A37-4DE1-9973-CCD011DF4888}"
    _reg_progid_ = 'PythonInVBA.MyChild'
    _public_methods_ = ['Initialize', 'GetName', 'SetName']

    def __init__(self):  # Rules of Com say paramerless constructors
        pass

    def GetName(self):
        return self.name

    def SetName(self, sName):
        self.name = sName

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

The trick is to create the child class with win32com.client.Dispatch("PythonInVBA.MyChild") and not the standard constructor MyChild() . My thanks to Stackoverflow user Kunif for solving this.

And so the VBA client code looks like this

Sub Test_MyParent_Returning_MyChild()
    On Error GoTo ErrHand:

    Dim objMyParent As Object
    Set objMyParent = VBA.CreateObject("PythonInVBA.MyParent")

    Dim objMyChild As Object

    Set objMyChild = objMyParent.GetChild()
    Debug.Print objMyChild.GetName  '* prints foo

    Exit Sub
ErrHand:
    Debug.Print Err.Description
End Sub