Enable Paging in Your Array Formula UDFs

As I alluded to earlier, one way to overcome the limitation that array formulas do not dynamically resize to fit the dimensionality of the data being returned by your UDF is to use paging.

Here is a simple way of enabling paging, without having to modify any existing UDF code (you could also use this same code as part of a helper function, though you'd need to add the page/rows parameters to your UDF signature).

To use it in your workbook just wrap the call to the UDF with the pagination UDF.  So when before you might have had as your array formula:

{=getBDCData("SharedServices1", "AdventureWorksDWInstance", "Product")}

Now you use:

{=paginate(getBDCData("SharedServices1", "AdventureWorksDWInstance", "Product"), 50, 1)} 

Note: As you'll notice this is far from the most efficient/performant way of doing this! But it is the easiest and only one that doesn't require modifying the original UDFs. If you have time, you should consider building the pagination into the UDFs themselves, so you only query the data source for the rows you are going to display instead of grabbing every row and later determining the ones you care about.

/// <summary>

/// Paginate the data in the object array passed in

/// </summary>

/// <param name="data">Object[,] with original data</param>

/// <param name="rows">Number of rows per page</param>

/// <param name="page">Current page number</param>

/// <returns></returns>

 [UdfMethod(IsVolatile = true)]

public object[,] paginate(object[,] data, int rows, int page)

{

    if ((data == null)||(data.Rank != 2))

    return data;

    object[,] results = new object[rows, data.GetLength(1)];

    for (int i = (page - 1) * rows;

    (i < (page * rows)) && (i < data.GetLength(0));

    i++)

    {

        for (int j = 0; j < data.GetLength(1); j++)

        {

            results[i - ((page - 1) * rows), j] = data[i, j];

        }

    }

  return results;

}