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.

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