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.