In late 2019, Microsoft added a new feature to Excel - Dynamic Arrays. If you are an Office 365 user you may have already received this update, or you will soon, depending on what release channel of Excel you are on. As of Jan 2020, Dynamic Arrays are being rolled out to most Excel users.
The Dynamic Arrays feature automatically resizes functions that return an array of data. If there is not enough space to show the full array the new #SPILL! error will be shown. This makes array functions in Excel much easier to use.
What does this mean for PyXLL users?
PyXLL has it's own "auto-resize" feature that will automatically resize a formula to match the size of the returned array. With the addition of Dynamic Arrays to Excel this feature is no longer needed, but it can still be used for old style "Ctrl+Shift+Enter" arrays.
Array functions are written as normal, and when you enter the formula in Excel the result will automatically be expanded by Excel's own Dynamic Arrays feature.
You can find out more about how to write array functions using PyXLL in our Array Functions section of the user guide.
I see the @ symbol added to my formulas
If you open a workbook saved from a previous version of Excel that did not have the Dynamic Arrays feature you may notice that some formulas now start with "@".
This is a new feature of Excel that will pick the first element from an array and return only that. If your function returns (or could return) an array but is not saved as an "array formula", then to retain compatibility with older versions of Excel the "@" symbol is added so that only the first item of the array is shown, as it would have been in earlier versions of Excel.
If this is not what you want, you can remove the @, or simply re-enter the formula.
Also see Problems using Range.Formula property below.
Why are some functions surrounded by "{}"?
Since the addition of Dynamic Arrays, Excel uses "{}" to mean old-style non-dynamic array functions. When opening a workbook from an earlier version of Excel, old-style array functions are written this way so they do not resize automatically as Dynamic Arrays do. This is for backwards compatibility so that the sheet behaves in the same way that Excel thinks it would have have done previously (Excel does not know about PyXLL's auto-resize feature).
NOTE: In PyXLL 4.4.0 and later versions CSE (or "{}") formulas using the auto-resize feature will still be resized automatically even if your Excel version supports dynamic arrays.
To convert an old style array function to a Dynamic Array function, edit the formula and copy the portion between the braces {}. Then delete the whole array formula and re-enter it in a single cell by entering "=" followed by pasting the formula without the braces/
For example, if you had an array formula "{=FOO(A1)}" you would delete the whole array formula and enter "=FOO(A1)" in the top left cell of where you want the result to appear.
Enabling PyXLL's auto_resize when Dynamic Arrays are available
Using Excel's own Dynamic Arrays is preferable to using PyXLL's auto-resizing, but if you want all array functions to use PyXLL's auto-resizing while you transition your sheets to use Dynamic Arrays you can do so by adding the following setting to the PYXLL section of your pyxll.cfg file:
[PYXLL]
allow_auto_resizing_with_dynamic_arrays = 1
Problems using Range.Formula property
Another side effect of this new feature is that if you're using the Range.Formula property, either in VBA or in Python, then you may see it adds '@'s to you formulas where you don't expect it to. This is because the Formula property tries to keep the same behaviour as before Dynamic Arrays were introduced.
For example, if you pass a Dynamic Array into a formula the "Formula" property will automatically add a '@' to the beginning of the dynamic array range to just get the top left cell since that is closest to the behaviour you would have got in an earlier version of Excel.
To get around this you should use the new "Range.Formula2" property to set formulas that involve Dynamic Arrays. This will let you set formulas that reference dynamic arrays without it 'fixing' them by adding any '@'s to your formula.