Can I really make python work with VBA

Question

I'm a heavy VBA user that when has the chance to do a little project in python it's like going from hell to haven when it comes to programming itself. Still, I know that my users want reports in excel (for good reasons) and I'm just not ready to give up how amazingly easy is to deploy a new version in excel: just save a new workbook with new version name.

These workbooks have something like 30 users from different departments/countries. I have a new version at least once a week and often 2/3 in the same day. Deployment must be practical.

Trying to combine the best of both worlds I came up with the following question:

https://stackoverflow.com/questions/39175926/python-com-server-with-vba-late-biding-skip-win-register-no-admin-rights

However what gave me hopes was the 'Way 2' here:

https://stackoverflow.com/a/9880276/4797660

which led me here:

https://setuptools.readthedocs.io/en/latest/setuptools.html

As I'm going down this rabbit hole I thought if it wouldn't be wise to ask around for other that have tried something similar. What I'm afraid is this will be one these situation with so many extra complications that, even after the initial learning curve, the costs will outrun the benefits.

Any words on the risks I'm taking by trying to come up with a very practical build-in-python-use-it-in-VBA approach?

The user interacts heavy with the workbook via worksheet events (like click on this data to see details). That basically turn the workbook into a hybrid of report and application. Also need things like change cell format and print formula to a cell. Often a pivot will be put on top of the data and the user will play with it. This all adds up to the impression that I will always need a VBA layer that would call the python objects.

• jpmc26: "how amazingly easy is to deploy a new version in excel" It might be easy to save a new version, but how hard is it to make sure everyone keeps up to date? You'll see plenty of horror stories about trying to manage who is on what version.
• BuckTurgidson: @jpmc26. Indeed this statement cannot be generalized. It's true in my case.
• Doc Brown: Deploying a compiled executable or a python script is definitely as simple as deploying an Excel document, once the target machine contains the required run time environment. However, it seems you want to use Python from VBA by COM??? Is that really necessary? That is what it makes harder in your case. Can't you just run your Python script in a separate process by utilizing VBA's Shell command and implement the I/O through files?
• Doc Brown: @jpmc26: if the deployment of new Excel VBA programs is simple or not depends heavily on if there is a clear separation between the program and user's data, so the first one can be updated without overwriting the second. I guess the OP has made this separation, otherwise he would already be in big trouble.
• Doc Brown: @jpmc26: ok, now I got you. One can perfectly write VBA programs as part of one Excel document, and keep the user's data in a second document. The VBA macro then can create reports as a third document. All that is pretty simple, one has just to care for this separation, no magic involved. This is mandatory if you ever want to be able to deploy a version 2.0 after version 1.0. Actually this is what I always told people in the first 30 minutes of my introductory VBA lessons ;-)

Answer 1

If it is more important to you to use Python as a language, and not because you need the full stack of available modules for CPython, maybe a combination of Excel-DNA and IronPython maybe an option for you.

Excel-DNA will allow you to develop Excel addins using any .NET language, which don't need any registration or installation on the target machine. The only prerequisite is the specific .NET framework version installed there, which is typically the case on most modern Windows systems. Moreover, Excel-DNA includes a packing tool to combine all files and DLLs needed to a single file. The result will be an XLL file which can be as easily deployed as an XLSM Excel VBA macro file.

IronPython, as you might know, is a .NET implementation of Python, it will allow you to produce .NET assemblies written in Python. So far, I used the Excel-DNA framework only with VB.NET and C#, not with IronPython, but in principle when you bind a .NET assembly to Excel-DNA, the latter will not be aware in which language the assemblies' source code was written. The IronPython docs give some hints how to use it for creating assemblies which can be loaded from other .NET assemblies like the Excel-DNA framework, however, there might be some obstacles to come around. You have to try it out by yourself. I would recommend, however, to start with VB.NET addin first before you switch to Python.

Answer 2

You can keep it loose with Python scripts and call them with what I call a gateway class. So called gateway because it opens the rich world of the Python ecosystem to the Excel VBA Developer http://exceldevelopmentplatform.blogspot.com/2018/06/python-vba-curve-building.html

In fact, on my blog June 2018 is Python month where I show off Python features and make them available to Excel VBA Developers to expand their horizons.

I reproduce the code my from my article here some code which gives VBA developers access to Python's CubicSpline class (as an example of a cool Python feature that a VBA dev could use instead of coding their own).

import numpy as np
from scipy.interpolate import CubicSpline
class PythonCubicSpline(Object):
 _reg_clsid_ = "{F48006B8-42B5-4D89-8D3C-C3C3E5E24C8D}"
 _reg_progid_= 'SciPyInVBA.PythonCubicSpline'
 _public_methods_ = ['Initialize','Interpolate']
 def Initialize(self, x1, y1):
 # calculate natural cubic spline polynomials
 x = np.Array(x1)
 y = np.Array(y1)
 self.baseCubicSpline = CubicSpline(x,y,bc_type='natural')
 return str(self.baseCubicSpline)
 def interpolate(self, x):
 return self.baseCubicSpline(x).tolist()
if __name__=='__main__':
 Print ("Registering COM server...")
 Import win32com.server.register
 win32com.server.register.UseCommandLine (PythonCubicSpline)

and some calling Excel code

Option Explicit
Sub TestPythonCubicSpline()
 Dim cubSpline As Object
 Set cubSpline = CreateObject("SciPyInVBA.PythonCubicSpline")
 Call cubSpline.Initialize(Array(0, 1, 2, 3, 4, 5), Array(12, 14, 22, 39, 58, 77))
 Debug.Print cubSpline.Interpolate(1.25) '* passing single, outputs 15.203125
 Debug.Print Join(cubSpline.Interpolate(Array(1.25, 1.5)), ";") '# passing an array, outputs 15.203125;16.875
End Sub