Calling Python code from Excel with ExcelPython

Introduction

ExcelPython is a lightweight COM library which enables you to call Python code and manipulate Python objects from Excel VBA (or indeed any language supporting COM). In this article I will show you how this is done. 

Background

Though its supremacy is being challenged by free (OpenOffice) or cloud (Google Sheets) alternatives, Excel remains the most widely used application for user-friendly computation and data processing in an office environment. Being able to call Python code from Excel vastly expands Excel's capabilities by leveraging the vast collection of libraries and powerful language features.

ExcelPython is an in-process COM library which enables you to load Python modules, call methods and manipulate objects. Other Python/Excel interfaces, such as PyXLL and Python-Excel, which are both excellent tools, differ from ExcelPython in a few subtle ways and hence have slightly different domains of applicability. The advantages of ExcelPython are that it's  

• free (both cost and license),
• small and lightweight,
• highly embeddable: it's possible to use a packaged up Python distribution so that the spreadsheet can be distributed without installing Python,
• transparent to the end user: Python is launched from the Excel VBA call so no additional end-user intervention is required once the spreadsheet code has been developed,
• generic: any existing Python module or function can be called and any Python object manipulated, indeed ExcelPython can be used to call Python libraries directly without writing any additional Python code to wrap them in a callable function. 

Using the code 

If you want a very basic tutorial showing you how to use the functions provided by the library, head over to the ExcelPython page on Sourceforge, where you'll find in the wiki section a step-by-step guide to get started.

In this tutorial we're going to look at how to use ExcelPython to merge a collection of lists, sort their values and remove any duplicates. This is a very straight-forward task in Python which requires only a few lines of code, whereas the equivalent VBA code would be somewhat more verbose.

Here's the Python function we're going to call from Excel:

def merge_sort_unique(lists):
  s = set()
  for L in lists:
    s.update(L)
  return sorted(s)

Create a new folder in a location of your choosing, and save this script in a file called Methods.py.

Let's look step-by-step at how to we call this method from an Excel worksheet. Fire up Excel and start by making up some input data which we'll pass to the Python script, and save the workbook in the same folder as the Python script. Your worksheet should look like this:

The next step is open up VBA and add a reference to ExcelPython. To open VBA press Alt+F11 then go to Tools | References... and if you've installed ExcelPython properly you should be able to select it in the dialog box.

To check that you can now reference the ExcelPython library, try typing ?PyVar(PyEval("1+2")) in the Immediate Window (Ctrl+G if it's not already visible) and you should see the result.

Now we're ready to call the Python method. Create a new module (Insert | Module) and write the following VBA code:

Function msu(lists As Range)
    Set methods = PyModule("Methods", AddPath:=ThisWorkbook.Path)
    Set result = PyCall(methods, "merge_sort_unique", PyTuple(lists.Value2))
    msu = WorksheetFunction.Transpose(PyVar(result))
    Exit Function
End Function

Let's look at this code line-by-line.

Set methods = PyModule("Methods", AddPath:=ThisWorkbook.Path)

This statement is the equivalent of import Methods in Python. Here we load up the Methods module, which is defined in Methods.py following the convention that Python modules have the name of the file or folder they are defined in, and store it in the VBA methods variable. This means that now the VBA function has a local variable called methods which actually points to the Python module object. Note the AddPath parameter, which enables us to insert extra folders at the beginning of the Python path before trying to load the module - in this case we add the folder that contains the workbook so that it finds our Methods.py file.

Next we call the merge_sort_unique method in the Methods module:

Set result = PyCall(methods, "merge_sort_unique", PyTuple(lists.Value2))

The ExcelPython PyCall method takes an ordered argument list, constructed using the PyTuple method, and optionally a keyword argument list which can constructed using PyDict method. Here the result is stored in the VBA result variable.

The result variable now points to a Python list object, and hence cannot be placed directly into the worksheet (a type error would occur). Therefore, as a final step, we convert the Python list object to a VBA array using the PyVar method.

msu = WorksheetFunction.Transpose(PyVar(result))

The PyVar method generically converts Python objects to their VBA equivalents, where possible, with the option of hinting the desired dimensionality for converting Python list-of-lists to 2D arrays. Further details can be found in the ExcelPython sourceforge wiki.

Now we're ready to see if it works! In the worksheet create an array block (by selecting multiple cells, entering the formula and pressing Ctrl-Shift-Enter) with the formula =msu(A1:C16). You should see the following:

If you don't, then don't worry, ExcelPython is very easy to debug. Make the following modification to the VBA code

Function msu(lists As Range)
On Error GoTo do_error
    Set methods = PyModule("Methods", AddPath:=PyPath)
    Set result = PyCall(methods, "merge_sort_unique", PyTuple(lists.Value2))
    msu = WorksheetFunction.Transpose(PyVar(result))
    Exit Function
do_error:
    msu = Err.Description
End Function

so that any errors will be caught and shown on the worksheet, and you can analyse what's wrong.

Finally note that the function returned a 0 at the beginning of the list even though it wasn't present in any of the input lists. This is actually the result of the Python None object begin pasted into the worksheet. Why does our function return None among the merged elements? 

The reason lies in the fact that the input lists were of different lengths, but were passed as a block, hence contained VBA Empty values. These get marshaled into None in Python, so the input lists did in fact contain None elements. One quick fix, is to modify the behavior of merge_sort_unique in Python so that it eliminates None from the return value:

def merge_sort_unique(lists):
  s = set()
  for L in lists:
    s.update(L)
  s.remove(None)
  return sorted(s)

Note that all you have to do is modify Methods.py, save it and press F9 to recalculate the worksheet - ExcelPython will take care of automatically reloading the module, which greatly facilitates interactive development of your Excel-interfaced Python scripts.