Working Around Dimensional Limitations of Using Array Formulas for UDF Return Values.

As you have noticed, many of my samples below use an Array Formula in Excel to retrieve an array of data returned by your UDF code.

The biggest issue with taking this approach is that you need to know ahead of time the exact dimensions of the data being returned, which is not always the case (like the Query Table example below, or the consuming SharePoint Lists case).

The workaround to this is to use the idea of "paging" through your data if the data is likely to grow beyond the dimension of your Array Formula.

To implement this you would modify the existing UDF code to return only a preset number of Rows (say 100 rows) at a time.  Then you could create an Array Formula of 100 rows height and always have all of the data fit in it. When you need data past the first 100 rows you can use another parameter into the UDF as the "page number".  For example, if you call the UDF with the page parameter as 1 you get rows 1-100, but when you call the UDF with page parameter 2 you get rows 101-200... and so on.

You could use the same analogy in the Columns case as well if your data can grow width-wise.

By parameterizing the Page you can allow the end user to pick the page they are using through an Excel Services parameters, and thus allowing them to see all of the data n-rows at a time.

If you'd like to see some sample code for this send me an email, if I get enough requests I'll modify one of my solutions to include this workaround.

Comments

• Anonymous
  April 23, 2007
  Could you please put up some sample code. Thanks