Writing Excel UDFs and Macros in Python with ExcelPython

Introduction

Microsoft Excel's ubiquity in the corporate workplace in large part derives from the ability to extend its functionality through VBA macros and user-defined functions, for automating complex procedures and for use in cell formulas. VBA however, is an old-fashioned language with a lot of subtle idiosyncracies and today is rarely used outside the restricted domain of Office automation. Python, on the other hand, is a widely used modern scripting language, whose greatest strength is the vast available range of mature, open-source libraries for performing all kinds of computing tasks.

ExcelPython is an Excel add-in which makes it incredibly easy to write user-defined functions and macros in Python, without having to touch VBA at all!

Background

A year ago, I wrote an article showing how to call Python code from Excel using an early version of ExcelPython. Since then, the library has come a long way! Back then, it was necessary to write VBA wrapper code to call Python. With the latest versions of ExcelPython, this is all done for you automatically by the add-in, making it quicker and easier than ever to write your UDFs and macros directly in Python.

In this tip, I present some of the new features of ExcelPython which make it incredibly quick and easy to write functions and macros in Python and use them from Excel.

The tip will skim over the various features quickly to show what is possible. For more details, you can follow the in-depth tutorials.

Getting Set Up

To use ExcelPython, you must have Excel and Python installed. ExcelPython is compatible with Python versions 2.6 to 3.4. Also, it is necessary to have installed the PyWin32 library for whichever version of Python you have.

To get set up, get the latest release of ExcelPython and follow these instructions on how to install it.

Writing a User-defined Function in Python

Once you have installed the ExcelPython add-in, save a blank workbook in a new folder as Book1.xlsm (i.e., as a macro-enabled workbook) and click Setup ExcelPython on the ExcelPython toolbar tab. Then open up the file Book1.py which will have been created in the same folder as the workbook and enter the following code:

# Book1.py
from xlpython import *

@xlfunc
def DoubleSum(x, y):
    '''Returns twice the sum of the two arguments'''
    return 2 * (x + y)

Switch back to Excel and click Import Python UDFs to pick up the new code. Then in a blank cell, you can type the following formula:

=DoubleSum(1, 2)

and you should get the correct result displayed in the cell!

Other features, which you can learn more about from the tutorials, include automatic conversion to and from common Python data types such as dictionaries, NumPy arrays as well as dimensionality manipulation and automatic function and parameter documentation.

Writing a Macro in Python

In addition to writing user-defined functions for use in cell formulas, VBA is typically used for defining macros to automate Excel. ExcelPython makes it easy to code these in Python as well.

For example, the following code shows how to write a simple macro which sets the Excel status bar (at the bottom of the window) to the current date and time.

@xlsub
@xlarg("app", vba="Application")
def my_macro(app):
  from datetime import datetime
  app.StatusBar = str(datetime.now())

Once you add this code to Book1.py, all you need to do is click Import Python UDFs again and the macro will be ready to use from Excel, for example you can associate it with a button control.

Workbooks, sheets and ranges can also be manipulated from Python just as you can from VBA. The following sets the value of cell A1 in the active worksheet:

@xlsub
@xlarg("sheet", vba="ActiveSheet")
def my_macro_two(sheet):
    sheet.Range("A1").Value ="Hello World!"

Finally, to facilitate writing macros, ExcelPython integrates seamlessly with xlwings, an excellent Python library which wraps up the Excel object model and makes it even easier to read and write cell values by automatically converting widely-used data types like NumPy arrays and Pandas DataFrames.

from xlwings import *

@xlsub(xlwings=True):
def my_macro_three():
    Range("A1").value = [ 1, 2, 3 ]

Conclusion

In this tip, I exposed some of the main features of ExcelPython. There are however many more! For example:

• It is easy to target a specific (even portable) Python distribution.
• Distributing the worksheet and Python code is as easy as zipping up the containing folder, which contains the ExcelPython runtime itself - no typelib registration or admin rights required!
• It is possible to manipulate Python objects directly from VBA code, just like in previous versions, allowing you to use the extensive range of Python libraries directly from VBA.

If you like ExcelPython, please get in touch and let me know what you are using it for!