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
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
Enter your email address below to download the Excel workbook and Python code with my UDFs.
Excel Workbook my_udf.xlsm
Python UDFs my_udf.py
PGCodeRider
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
Philip Treacy
Hi,
Accurate to the first digit? Or did you mean something else?
Excel’s number precision is 15 digits and the largest positive number it supports is 9.99999999999999E+307
Excel Specs and Limitations
Cheers
Phil
KingTamo
Thanks a lot for this tutorial
When I clicked on “Import Functions” in the Addin ribbon I got an error
Catalin Bombea
Did you tried the instructions from the image? Excel Options-Trust Center-Macro Settings
Catalin
KingTamo
Yes I did ..
Also I have downloaded the ready files in this topic and get no correct results
Philip Treacy
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