CODEWITHSUNDEEP
excelxll
Maury Markowitz
Maury Markowitz

Reputation: 9279

Can XLL return arbitrary sized arrays from a single-cell formula?

I've looked over many threads about XLL's and arrays here on SO, but I'm more confused now than ever. Sorry if the following is completely noob, but that's why I'm here...

I've been told that it is not possible to write an XLL function that can return arbitrary arrays of data. You can return arrays from your code, but they must be called from an array formula (CSE). This implies that the size of the return space has to be pre-defined.

In our case the function will return N rows (and/or cols) of data, and we have no idea in advance how many there will be. Ideally we would just place a single formula in one cell and have it use as many cells to the left and down as needed.

The code examples I've found here don't strongly suggest one or the other, but it seems like XLOPER12 (and maybe XLOPER) have no limits on the size that is returned. Does Excel impose a limit after it is returned?

Upvotes: 2

Views: 458

Answers (1)

Charles Williams
Charles Williams

Reputation: 23520

An XLL UDF (also a VBA UDF)or can return a variable size array - but Excel will only assign the result(s) to the cell(s) that the UDF is entered into. To handle an unknown number of results you can:

enter the UDF into the maximum number of cells that would be needed

or return a handle to an internal array and build other UDFs that can interpret the handles

or build a resizer platform: see
http://colinlegg.wordpress.com/2014/08/25/self-extending-udfs-part-1/
https://groups.google.com/forum/#!topic/exceldna/oBKpr0BCgmU

Upvotes: 3

Related Questions