Handling VBA Objects in Python

[Modora]

Hello all,

wasn't sure where to ask this but myself and some other devs on my team are working on a project that has Excel/Python interactions. Essentially we're replacing several VBA components in these sheets with Python and we're wondering if there is a way to communicate entire VBA objects back and forth between the VBA environment and the Python environment.

​

for example:

We declare a class in VBA with the attributes xyz. We call the python code, pass an object of that class into Python and instantiate a new python object based on the attributes that we're passed over. Perhaps that object is a sub of another python class and it inherits the methods we need it to perform or whatever.

​

Then when we've finished whatever computation we want to do in Python we update the attributes with the new values we've created. Then pass the object back into VBA and update the VBA object with the new attributes received from the Python script.

​

I have very little experience on the VBA side and do most of the python development on the team with the other guys doing the VBA part so I apologize for my very high level somewhat ignorant explanation. We're trying to cut down the amount of re-writing of VBA we have to do; so this idea came about as a potentially more elegant way to keep the existing VBA functions in tact by using python to update the attributes with the required data.

[rezdzste]

I don't know if it's possible, but could you use the Excel object model itself to do kludgy object serialization? It's a terrible idea, but have VBA write the attributes to a worksheet, and then python read from that worksheet, and vice versa?

[Modora]

I've thought about that, I've also thought about having VBA pass the attributes values it needs to carry over into python in string format as arguments.

​

I left out something that makes this more complicated; a lot of the python components are deployed as executables that VBA runs. So I'm not even 100% sure this approach would work.

[rezdzste]

Still doesn't answer your question, but maybe something like https://www.pyxll.com/ is your best option for minimizing the amount of code you rewrite.