Newton Excel Bach, not (just) an Excel Blog

Most  of the  user defined functions (UDFs) presented on this blog return a multi-cell array, that must be entered by selecting the output range then pressing Ctrl-shift-enter to display all the results.  This process has several inconveniencies:

• You need to know the size of the array, or work by trial and error.
• It is easy to increase the size of the output range, by selecting the new range and re-entering, but there is no easy way to reduce the size.  You need to delete the array and re-enter.
• If the size of the output array changes it must be re-sized manually, or it will display #N/A results, or only part of the array.

These problems can be resolved, at least partially, using VBA macros, or when using Python and xlwings, using the xlwings dynamic array functions.  Both methods are illustrated below using the Python Numpy spreadsheet from the previous post.

To use the VBA macros, enter the function in the top-left cell of the output range, and enter in the usual way:

Then press Ctrl-Shift-S and all of the UDF results will be displayed.  Note that any existing data within the output range will be deleted.

To re-size the array, select the required range and press Ctrl-Shift-R:

These VBA functions can be added to any Excel file containing UDFs that return array functions.  The code may be copied from the xlw_Numpy spreadsheet:

xlNumpy.zip

When using xlwings, dynamic arrays are available.  Simply add an @xw.ret(expand =’table’) decorator to each function where a dynamic array is required:

@xw.func
@xw.arg("x", np.array, ndim = 2, dtype = np.float64)
@xw.ret(expand='table')
def xl_isfinite(x):
    return np.isfinite(x)

The function may then be entered in the top left cell of the output range:

and the results will automatically be generated over the full extent of the table:

The results will automatically write over any data within the output range, and also the column to the right and the row below.   The data will automatically resize to show the full extent of the array, but the output range cannot be adjusted to show only part of the array (other than by re-sizing the array returned from Python).

At the time of writing (1st June 2018), the code does not always fully recalculate after a global recalculation of the sheet or workbook:

and if results are deleted or overwritten the arrays do not always recalculate:

These problems are a recognised bug,  which should be fixed in the next release.