Tutorial: Running VBA Code From Python

Let’s say you have some Excel macros that you’d like to integrate with python. If you’ve got the time & know-how, and if it’s something that can be run independently, I’d recommend porting the VBA code to python, but if the functions and methods in VBA are actively used by non-developers, you can’t easily do this without scaling up a brand new application (with all that entails), and re-training the users. And you don’t want to re-write the logic in python, because now you’ve got two code bases to maintain.

So let’s assume that’s the use-case: you’ve got some VBA code that is actively used by clients/customers/non-technical co-workers, etc., and you want to leverage this already written & battle-tested code as part of a python application, perhaps to automate a manual process like “Open workbook, and run a specified macro which will produce a well-formatted report dashboard”, or something like that. Fortunately, it’s pretty easy to integrate.

Modeling the Excel Macro/Function in Python

With a pretty simple python class, you can model an Excel macro or function using this XlMacro class. (I was inspired to make this XlMacro class by a question that I answered on StackOverflow yesterday.)

The XlMacro only opens the Workbook (and Excel Application) when needed, via the Run method. By default, the Run method keeps the workbook/Excel open, this is convenient if you need to make multiple calls to the same method, but this can be overridden with the keep_open and save_changes keyword arguments.

import os
from win32com import client

EXCEL_CLS_NAME = "Excel.Application"

class XlMacro:
    def __init__(self, path, book, module, name, *args):
        self._path = path  # path containing workbook
        self._book = book  # workbook name like Book1.xlsm
        self._module = module  # module name, e.g., Module1
        self._name = name  # procedure or function name
        self._params = args  # argument(s)
        self._wb = None
    @property
    def workbook(self):
        return self._wb
    @property
    def wb_path(self):
        return os.path.join(self._path, self._book)
    @property
    def name(self):
        return f'{self._book}!{self._module}.{self._name}'
    @property
    def params(self):
        return self._params
    def get_workbook(self):
        wb_name = os.path.basename(self.wb_path)
        try:
            xl = client.GetActiveObject(EXCEL_CLS_NAME)
        except:
            # Excel is not running, so we need to handle it.
            xl = client.Dispatch(EXCEL_CLS_NAME)
        if wb_name in [wb.Name for wb in xl.Workbooks]:
            return xl.Workbooks[wb_name]
        else:
            return xl.Workbooks.Open(self.wb_path)
    def Run(self, *args, **kwargs):
        """ 
        Runs an Excel Macro or evaluates a UDF 
        returns None for macro, or the return value of Function
        NB: there is no error-handling here, but there should be!
        """
        keep_open = kwargs.get('keep_open', True)
        save_changes = kwargs.get('save_changes', False)
        self._wb = self.get_workbook()
        xl_app = self._wb.Application
        xl_app.Visible = True
        ret = None
        if args is None:
            ret = xl_app.Run(self.name)
        elif not args:
            # run with some default parameters
            ret = xl_app.Run(self.name, *self.params)
        else:
            ret = xl_app.Run(self.name, *args)
        if not keep_open:
            self.workbook.Close(save_changes)
            self._wb = None
            xl_app.Quit()
        return ret

Here’s some example code to loop over our available macros/functions with some dummy arguments.

# Modify these path/etc as needed.
path = r'c:\debug\\'
book = 'pymacro.xlsm'
module = 'Module1'
macros = ['macro1', 'SayThis', 'AddThings', 'GetFromExcel', 'GetWithArguments']

def default_params(macro):
    """
    mocks some dummy arguments for each Excel macro
    this is required by Excel.Application.Run(<method>,<ParamArray>)
    """
    d = {'macro1': ("hello", "world", 123, 18.45),
        'SayThis': ('hello, world!',),
        'AddThings': [13, 39],
        'GetFromExcel': [],
        'GetWithArguments': [2]
        }
    return d.get(macro)

# Test the macros and their arguments:
for m in macros:
    args = default_params(m)
    if args:
        macro = XlMacro(path, book, module, m, *args)
    else:
        macro = XlMacro(path, book, module, m)
    x = macro.Run()
    print(f'returned {x} from {m}({args})' if x else f'Successfully executed {m}({args})')

If you don’t want to use the mock arguments, you can initialize an XlMacro without *args, and then specify args while calling Run() method directly.

f = XlMacro(path, book, module, 'GetWithArguments' )
f.Run(3)

Example VBA Code

Here’s the example VBA code that you can use with the above. To use, simply drop this code in Module1 of any macro-enabled Excel Workbook, and Save the workbook.

Note: the practical case for this is not limited to the very simple examples included below, but these examples illustrate the basic foundation: if you can send arguments over COM from python to Excel (and vice versa) you can apply the same principle towards more complex use cases. In the real world, you’d send arguments to kick off an Excel procedure that creates a dashboard or report, formats or normalizes data, etc.

Option Explicit

Sub macro1(ParamArray arr())
    Dim msg As String
    msg = "Parameters received with length: " & (UBound(arr) + 1) & vbCrLf & vbTab
    msg = msg + Join(arr, vbNewLine + vbTab)
    MsgBox msg
End Sub

Sub SayThis(words As String)
    Application.Speech.Speak words
End Sub

Sub AddThings(i As Double, j As Double)
    Sheet1.Range("A1").Value = i + j
End Sub

Function GetFromExcel() As Double
    GetFromExcel = CDbl(Sheet1.Range("A1").Value)
End Function

Function GetWithArguments(i As Double) As Double
    GetWithArguments = Sheet1.Range("A1").Value * i 
End Function

What’s Next?

Play around with this code, and use it against some of your own VBA procedures, of course!

A more interesting case will be the reverse: calling python functions from VBA. It’s a little bit more complicated to get up and running, but allows you to retain a UI in familiar Microsoft applications, while taking advantage of python to do the heavy-lifting. I’ll cover that in a future post.