Writing UDFs in Excel With Python

My last post looked at using Python to plot equations in Excel with DataNitro.

In this one I want to look at writing UDFs in Python.with xlwings.

Why Use Python for UDFs ?

This is a question not just about UDFs but why use Python at all in Excel?

I guess this depends on what you are doing. VBA will be fine in a lot of (most) cases but if you want to take advantage of the numerous libraries for Python to perform things like scientific computing, machine learning, web scraping, and more, then go with Python.

Bear in mind that Python is cross platform but VBA is tied to MS Office. If you can write an Excel UDF in Python, then take that code, and with with some minor modifications, use it elsewhere like in SQL Server, that's useful.

Of course if you already know Python, and don't want to learn VBA, that's a valid reason too.

Installing xlwings

Before we go any further let's install xlwings.

The first thing you need is an installation of Python and xlwings recommends using a distribution like Anaconda which includes a lot of the extra libraries you'll need.

You can use xlwings without installing the add-in, but the files that need to be distributed for your workbook to work are much bigger.

Installing the add-in is the recommended way to go and that's what I did.

Once installed, you get a new section on your Ribbon

Next, in Excel, go into File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings and check the box beside 'Trust access to the VBA project object model' and confirm the change.

Writing Python UDFs

Create a workbook using the Python command line method xlwings quickstart my_udf where my_udf is the name of your new workbook.

xlwings will create a new workbook called my_udf.xlsm and a Python file called my_udf.py.

You can now write your Python code in my_udf.py and import this to your workbook.

Sample Python UDFs

xlwings provide a number of sample UDFs and the syntax of a UDF is explained in their documentation.

I decided to write my own UDFs, none of them will make you gasp in awe. They're simply exercises in how to write a Python UDF in Excel.

The first one returns the cube of a number

The second returns a friendly greeting, based on two string arguments passed to it, which are the time of day, e.g. afternoon, and someone's name.

The third function queries a SQL database for some data and returns that data. This is a very basic SQL query and you can do a lot more powerful things than this.

Please note that the right hand edge of this image is truncated just so I can fit it onto the screen. The full code will be available to download towards the bottom of the post.

Getting the UDFs into Excel

Once you've written and saved the Python code, go back to Excel and from the xlwings area on the Ribbon, click on Import Functions

This will create some references to the functions in a VBA module called xlwings_udfs. If you go into the VBA editor (ALT + F11) and look for that module, you can see this code

Calling the UDFs

You use them just as you would with any other function in your sheet

Summing Up

It might seem a bit complicated to get this set up, but like a lot of things, once you've done it the first time, it's pretty straight forward to continue writing more Python UDFs.

Undoubtedly using Python for certain things has its advantages over VBA, and with the world seemingly scrambling to learn Python (and R) for data science and machine learning applications, why not start by writing your own Python UDFs?

File Downloads