Array Functions¶

Array Functions in Python¶

Any function exposed to Excel using the xl_func decorator that returns a list of values is an array function.

If a function returns a list of simple values (not lists) then it will be returned to Excel as a column of data. Rectanguler ranges of data can be returned by returning a list of lists, eg:

from pyxll import xl_func

@xl_func
def array_function():
    return [
        [1, 2, 3],
        [4, 5, 6],
        [7, 8, 9]
    ]

An optional function signature passed to xl_func can be used to specify the return type. The suffix [] is used for a 1d array (column), e.g. float[], and [][] is used for a 2d array, e.g. float[][].

For example, the following function takes 1d array (list of values) and returns a 2d array of values (list of lists):

from pyxll import xl_func

@xl_func("float[]: float[][]")
def diagonal(v):
    d = []
    for i, x in enumerate(v):
        d.append([x if j == i else 0.0 for j in range(len(v))])
    return d

NumPy arrays and Pandas types (DataFrames, Series etc) can also be returned as arrays to Excel by specifying the relevant type in the function signature. See NumPy Array Types and Pandas Types for more details.

When entering an array formula in Excel it should be entered as a Ctrl+Shift+Enter (CSE) formula, or if using Dynamic Arrays or PyXLL’s array auto-sizing feature then they can be entered in the same way as any other formula.

Array Types¶

New in PyXLL 5.7

As well as specifying the argument or return type as an array using the type[] syntax in the xl_func signature or to xl_arg or xl_return decorators, you can specify if you want the Python type to be a list (same as type[]), tuple or set.

Python type hints can also be used.

These array types have the following type parameters:

• list and tuple

  • T Type used for the item type.

  • ndim 1 or 2, for 1d or 2d collections.

• set

  • T Type used for the item type.

For example, the following function accepts a tuple of floats:

from pyxll import xl_func

@xl_func("tuple<float> x: str")
def tuple_func(x):
    # x is a tuple of strings
    return str(x)

This could also be specified using a Python type hint as follows:

from pyxll import xl_func

@xl_func
def tuple_func(x: tuple[float]):
    # x is a tuple of floats
    return str(x)

Two dimensional lists and tuples can also be used, for example:

from pyxll import xl_func

# Note: This is equivalent to 'int[][]'
@xl_func("list<int, ndim=2> x: str")
def tuple_func(x):
    # x is a list of lists of strings
    return str(x)

This could also be specified using a Python type hint as follows:

from pyxll import xl_func

@xl_func
def tuple_func(x: list[list[[int]]):
    # x is a list of lists of strings
    return str(x)

Ctrl+Shift+Enter (CSE) Array Functions¶

Ctrl+Shift+Enter or CSE formulas are what Excel used for static array formulas in versions of Excel before Dynamic Arrays were added. PyXLL has an array auto-sizing feature that can emulate dynamic arrays in earlier versions of Excel that do not implement them.

To enter an array formula in Excel you should do the following:

• Select the range you want the array formula to occupy.

• Enter the formula as normal, but don’t press enter.

• Press Ctrl+Shift+Enter to enter the formula.

Note that unless you are using Dynamic Arrays or PyXLL’s array auto-sizing feature then if the result is larger than the range you choose then you will only see part of the result. Similarly, if the result is smaller than the selected range you will see errors for the cells with no value.

To make changes to an array formula, change the formula as normal but use Ctrl+Shift+Enter to enter the new formula.

Auto Resizing Array Functions¶

Often selecting a range the exact size of the result of an array formula is not practical. You might not know the size before calling the function, or it may even change when the inputs change.

PyXLL can automatically resize array functions to match the result. To enable this feature you just add ‘auto_resize=True’ to the options passed to xl_func. For example:

from pyxll import xl_func

@xl_func("float[]: float[][]", auto_resize=True)
def diagonal(v):
    d = []
    for i, x in enumerate(v):
        d.append([x if j == i else 0.0 for j in range(len(v))])
    return d

You can apply this to all array functions by setting the following option in your pyxll.cfg config file

[PYXLL]
;
; Have all array functions resize automatically
;
auto_resize_arrays = 1

If you are using a version of Excel that has Dynamic Arrays then the auto_resize option will have no effect by default. The native dynamic arrays are superior in most cases, but not yet widely available.

Dynamic Array Functions¶

Dynamic arrays were announced as a new feature of Excel towards the end of 2018. This feature will be rolled out to Office 365 from early 2019. If you are not using Office 365, dynamic arrays are expected to be available in Excel 2022.

If you are not using a version of Excel with the dynamic arrays feature, you can still have array functions that re-size automatically using PyXLL. See Auto Resizing Array Functions.

Excel functions written using PyXLL work with the dynamic arrays feature of Excel. If you return an array from a function, it will automatically re-size without you having to do anything extra.

If you are using PyXLL’s own auto resize feature, PyXLL will detect whether Excel’s dynamic arrays are available and if they are it will use those in preference to its own re-sizing. This means that you can write code to work in older versions of Excel that are future-proof and will ‘just work’ when you upgrade to a newer version of Office.

If you want to keep using PyXLL’s auto resize feature even when dynamic arrays are available, you can do so by specifying the following in your pyxll.cfg config file

[PYXLL]
;
; Use resizing in preference to dynamic arrays
;
allow_auto_resizing_with_dynamic_arrays = 1

Dynamic arrays are a great new feature in Excel and offer some advantages over CSE functions and PyXLL’s auto-resize feature: