• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Writing UDFs in Excel With Python

You are here: Home / Excel VBA / Writing UDFs in Excel With Python
writing udfs in excel with python
November 24, 2017 by Philip Treacy

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.
Please enter a valid email address.

Excel Workbook my_udf.xlsm
Python UDFs my_udf.py

writing udfs in excel with python

More Python Posts

charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
using jitter to avoid over plotting

Using Jitter to Avoid Over Plotting in Power BI

Plotting data that has one variable where values are similar, can result in points that are plotted over each other. Use jitter to avoid this overplotting.
Real time data in Excel using sockets

Real Time Data in Excel Using Sockets

Get real time data into Excel using Python web sockets. Once established, sockets stay open and require little overhead to send messages.
real time data in excel

Real Time Data in Excel

Get real time data like stock or currency prices into your Excel workbook using a little bit of Python code. Sample workbook and code available.
Plot an equation in Excel using Python

Plot an Equation in Excel Using Python

How to plot an equation in Excel using Python. Easily plot your functions or data with just a few lines of code. Sample workbook and code available.

More User Defined Function Posts

Return an array from a udf

Return An Array From A UDF

If you can return an array from a udf you are able to return multiple pieces of data and place them into multiple cells
Return a range from a UDF

Return a Range from a UDF

If you return a range from a UDF this allows you to use that range in other functions and calculations.
create an excel add-in for user defined functions udfs

Create an Excel Add-In for User Defined Functions (UDF’s)

How to create an Excel add-in for user defined functions (UDF's) to make them easier to use.
creating a reference to personal.xlsb for user defined function udf

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

How to create a reference to PERSONAL.XLSB so that using User Define Functions (UDF's) is easier
Creating Multi-Function UDF's

Creating Multi-Function UDF’s

Combine separate functions into one, multi function UDF (user defined function)

Creating a UDF (User Defined Function) in Excel

Create your own functions in Excel

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBATag: Python, user defined function
Previous Post:Plot an equation in Excel using PythonPlot an Equation in Excel Using Python
Next Post:Visualizing Parts to a Whole in Excel Charts

Reader Interactions

Comments

  1. PGCodeRider

    April 4, 2018 at 7:07 pm

    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
    • Philip Treacy

      April 5, 2018 at 11:32 am

      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

      Reply
  2. KingTamo

    November 25, 2017 at 3:52 pm

    Thanks a lot for this tutorial

    When I clicked on “Import Functions” in the Addin ribbon I got an error

    python error

    Reply
    • Catalin Bombea

      November 26, 2017 at 3:50 pm

      Did you tried the instructions from the image? Excel Options-Trust Center-Macro Settings
      Catalin

      Reply
      • KingTamo

        November 26, 2017 at 9:12 pm

        Yes I did ..
        Also I have downloaded the ready files in this topic and get no correct results

        Reply
        • Philip Treacy

          November 27, 2017 at 12:46 pm

          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 Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.