Array size limits passing array arguments in VBA

Question

Excel-VBA 2007 appears to have a 64k limit on the size of arrays passed as arguments.

Is anyone aware of a fix or work-around?

Here's the code:

Public Function funA(n)
    Dim ar()
    ReDim ar(n)
    funA = ar
End Function

Public Function funB(x)
    funB = UBound(x)
End Function

From Excel:

=funB(funA(2^16-1))   '65536 as expected

=funB(funA(2^16))    'Gives a #VALUE

Looking inside, funA() works fine but, passed to funB, the argument x is an Error 2015.

Answer 1

I think it's a limitation of the spreadsheet cell itself, rather than VBA. Excel can pass arrays bigger than 2^16 between functions, but apparently it can't contain an array of that size within a cell.

As an experiment, highlight funA(2^16) in the cell formula and hit F9 - it'll give you a '#VALUE!' error.

Because the formula has already calculated the result of funA before it initiates funB, it's then trying to run funB on a function that's already calculated to an error.

It seems a work-around like the one Brad posted (i.e. a third function that calculates funB(funA(n)) within itself) keeps the cell out of the equation until the calculation's completed, so it works fine.

Answer 2

Because single-dimensional arrays are passed back to Excel as a row of columns you have hit the Excel 2007 limit on the number of columns (64K).

If you make your array 2 dimensional and return as rows it should work:

Public Function funA(n)
    Dim ar()
    ReDim ar(n,1)
    funA = ar
End Function

Alternatively you can use Transpose to rotate the array from a row to a column, but this is probably less efficient than creating a 2-dimensional array in the first place.