Writing UDFs in Excel With Python

Philip Treacy

November 24, 2017

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.

Note

xlwings is available for Excel 2016 on Mac and allows you to write macros in Python, but it does not support UDFs.

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

xkwings 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.

trust center macro settings

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

cubed python udf code

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.

text greeting python udf code

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.

sql query python udf code

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

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

xlwings_udf module code

Calling the UDFs

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

calling cubed udf

calling greeting udf

calling sql udf

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

Enter your email address below to download the Excel workbook and Python code with my UDFs.

By submitting your email address you agree that we can email you our Excel newsletter.

6 thoughts on “Writing UDFs in Excel With Python”

  1. Are you still limited by Excel’s numeric characters? Example in Python if you can do large computations like 2^256 power and it’s accurate down to the first digit. Excel only carries 14 numbers I believe

    Reply
        • Hi,

          The error message is saying that the VBA project object module isn’t trusted. Can you please take a screenshot of the Macro Settings in your Trust Center and send us a link to it.

          If the Import isn’t working then none of the sample Python code will run.

          Please start a Helpdesk ticket so we can more easily communicate about this.

          Thanks

          Phil

          Reply

Leave a Comment

Current ye@r *