# User Defined Functions (UDFs)¶

Note

This functionality is currently only available on Windows.

## Workbook preparation¶

1. Create a new Excel file from the template: xlwings template open (see Command Line Client) or just import the xlwings VBA module (xlwings.bas) manually, see VBA: Calling Python from Excel.
2. Save the Workbook as Excel Macro-Enabled Workbook (*.xlsm).

## Simple User-Defined Functions¶

The default settings (see VBA settings) expect a Python source file:

• in the same directory as the Excel file
• with the same name as the Excel file, but with a .py ending instead of .xlsm.

Alternatively, you can point to a specific source file by setting the UDF_PATH in the VBA settings.

Let’s assume you’ve got a Workbook Book1.xlsm, then you would create a file Book1.py in the same directory with the following sample function:

from xlwings import xlfunc, xlarg

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

• Now click on Import Python UDFs in the xlwings tab to pick up the changes made to Book1.py. If you don’t want to install/use the add-in, you could also run the ImportPythonUDFs macro directly (one possibility to do that is to hit Alt + F8 and select the macro from the pop-up menu).

• Enter the formula =double_sum(1, 2) into a cell and you will see the correct result:

Note that the formula can be used in VBA, too.

## Array Formulas I: without NumPy¶

You can pass an Excel Range as a function argument, as opposed to a single cell and it will show up in Python as tuple of tuples.

For example, you can write the following function to add 1 to every cell in a Range:

@xlfunc
return [[cell + 1 for cell in row] for row in data]


To use this formula in Excel,

• Click on Import Python UDFs again
• Fill in the values in Range("A1:B2")
• Select Range("D1:E2")
• Type in the formula =add_one("A1:B2")
• Press Ctrl+Shift+Enter to create an array formula. If you did everything correctly, you’ll see the formula surrounded by curly braces as in this screenshot:

### Number of array dimensions: ndim¶

The above formula has the issue that it expects a “two dimensional” input, e.g. a nested list of the form [[1, 2], [3, 4]]. Therefore, if you would apply the formula to a single cell or a row/column, you would get the following error: TypeError: 'float' object is not iterable.

To force Excel to always give you a two-dimensional array, you can extend the above formula like this:

@xlfunc
@xlarg('data', ndim=2)
return [[cell + 1 for cell in row] for row in data]


Now, you can use the formula with single cells, rows/columns and two-dimensional ranges. Accordingly, you can use ndim=1 to force a single cell to arrive as tuple.

## Array Formulas II: with NumPy¶

Most of the time, you’ll want to use NumPy arrays as this unlocks the full power of Python’s ecosystem for scientific computing.

To define a formula for matrix multiplication, you would define the following function:

@xlfunc
@xlarg('x', 'nparray', ndim=2)
@xlarg('y', 'nparray', ndim=2)
def matrix_mult(x, y):
return x @ y


Note

If you are not on Python >= 3.5 with NumPy >= 1.10, use x.dot(y) instead of x @ y.

## Macros¶

On Windows, as alternative to calling macros via RunPython, you can also use a decorator based approach that works the same as with user-defined functions:

from xlwings import Workbook, xlsub

@xlsub
def my_macro():
"""Writes the name of the Workbook into Range("A1") of Sheet 1"""
wb = Workbook.caller()
Range(1, 'A1').value = wb.name


After clicking on Import Python UDFs, you can then use this macro by executing it via Alt + F8 or by binding it e.g. to a button. To to the latter, make sure you have the Developer tab selected under File > Options > Customize Ribbon. Then, under the Developer tab, you can insert a button via Insert > Form Controls. After drawing the button, you will be prompted to assign a macro to it and you can select my_macro.