Any function that returns an array (or range) of data in Excel is called an array function.
Depending on what version of Excel you are using, array functions are either entered as a Ctrl+Shift+Enter (CSE) formula, or as a dynamic array formula. Dynamic array formulas have the advantage over CSE formulas that they automatically resize according to the size of the result.
To help users of older Excel versions, PyXLL array function results can be automatically re-sized .
The #SPILL!
error indicates that the array would overwrite other data.
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.
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.
PyXLL Type | Python Type | Python Type Hint |
---|---|---|
list | list |
list or typing.List |
tuple | tuple |
list or typing.Tuple |
set | set |
set or typing.Set |
These array types have the following type parameters:
T
Type used for the item type.ndim
1 or 2, for 1d or 2d collections.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 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:
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.
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.
Warning
Auto-resizing is not available for RTD functions. If you are returning an array from an RTD function and need it to resize you can use ref:Dynamic Arrays <dynamic> in Excel from Excel 2016 onwards.
If you are not able to update to a newer version of Excel, another solution is to return the array from your RTD function as an object, and then have a second non-RTD function to expand that returned object to an array using PyXLL’s auto-resize feature.
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:
Characteristic | Advantage |
---|---|
Native to Excel | Dynamic arrays are deeply integrated into Excel and so the array resizing works with all array functions, not just ones written with PyXLL. |
Spilling | If the results of an array formula would cause data to be over-written you will get a new #SPILL error to tell you there was not enough room. When you select the #SPILL error Excelwill highlight the spill region in blue so you can see what space it needs. |
Referencing the spill range in A1# notation | Dynamic arrays may seamlessly resize as your data changes. When referencing a resizing dynamic arrays you can reference the whole array in a dependable, resilient way by following the cell reference with the # symbol. For example, the reference A1# references the entire spilled range for a dynamic array in A1. |