PyXLL The Python Excel Add-In

Introduction

You can write an Excel macro (sometimes called ‘Subs’ in VBA) in Python. A Python macro function can do whatever you would previously have used VBA for. Macros work in a very similar way to worksheet functions.

Macro functions are almost identical to worksheet functions in how they’re written. One key difference, however, is that macro functions called by Excel can use the Excel Object Model to automate or script Excel.

PyXLL macro functions written in Python are exactly the same as any other Excel macro function. They can be called from form items such as buttons, or from VBA.

To register a function as a macro you use the xl_macro decorator, which works in almost exactly the same way as the xl_func decorator but is used for macros instead of worksheet functions.

VBA macros are often used to automate tasks in Excel, but using PyXLL we can write equivalent macros entirely in Python without the need for any VBA. The function xl_app can be used to get the Excel.Application COM object (using either win32com or comtypes), which is the COM object corresponding to the Application object in VBA.

See Python as a VBA Replacement for details of how the Excel Object Model can be used from Python.

Writing an Excel Macro Function in Python

If you’ve not installed the PyXLL addin yet, see Installing PyXLL.

To tell the PyXLL add-in to expose a Python function so that we can call it from Excel as a macro, all that is needed is to add the xl_macro decorator to a Python function:

from pyxll import xl_macro

@xl_macro
def hello(name):
    return "Hello, %s" % name

This function takes just a single argument, name, which can be passed in when we call the function from Excel.

PyXLL supports passing arguments and returning values of many different types, which is covered in detail in the Argument and Return Types section.

Configuring PyXLL with your Python Module

Once you have saved that code you need to ensure the interpreter can find it by modifying the following settings in your pyxll.cfg config file:

  • [PYXLL] / modules

    The list of Python modules that PyXLL will import.

  • [PYTHON] / pythonpath

    The list of folders that Python will look for modules in.

If you saved the above code into a new file called my_module.py in a folder C:\Users\pyxll\modules you would add the Python module my_module to the modules list, and C:\Users\pyxll\modules to the pythonpath.

Note that Python module file names end in .py, but the Python module names do not.

[PYXLL]
;
; Make sure that PyXLL imports the module when loaded.
;
; We use the module name here, not the file name,
; and so the ".py" file extension is omitted.
;
modules = my_module

[PYTHON]
;
; Ensure that PyXLL can find the module.
; Multiple modules can come from a single directory.
;
pythonpath = C:\Users\pyxll\modules
© Copyright PyXLL Ltd