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.

Some background here [Max Array size in Excel 2007 Worksheet Function?](http://windowssecrets.com/forums/showthread.php/128704-Max-Array-size-in-Excel-2007-Worksheet-Function). What do you actually need to do with the UDF? That will guide our suggestions. — brettdj, Jun 28 '12 at 04:46
The reference looks like a variant of the problem. What I need to do with the udf is pretty much what you see except that the array will be filled with numbers (a sample distribution) and funB does fun and exciting things with it. — Marc Thibault, Jun 28 '12 at 13:48

score 4 · Answer 1 · answered Jun 28 '12 at 03:38

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.

I take that back. It works fine in VBE - dn't know why it failed the first time I tried it. — Marc Thibault, Jun 28 '12 at 14:11

score 3 · Accepted Answer · edited Jun 28 '12 at 07:44

3

This seems to be as close to a work around as I can find. Do the inter-function calls from VBA

If you make something like this

Public Function funBA(n As Variant) As Variant
    funBA = funB(funA(n))
End Function

it seems to work up to n=2^24=2^8^3 (which doesn't look like any data type break point in VBA which is where the hang up is, but that's a pretty big array)

edited Jun 28 '12 at 07:44

Jean-François Corbett

37,420
30
139
188

answered Jun 28 '12 at 02:51

Brad

11,934
4
45
73

Yee-Hah! That does it. Nice catch. – Marc Thibault Jun 28 '12 at 13:59

score 3 · Answer 3 · answered Jun 28 '12 at 08:16

3

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.

answered Jun 28 '12 at 08:16

Charles Williams

23,121
5
38
38

1

A 2-D column array dim'd (n,1) is in fact what the original code uses and it exhibits exactly the same symptoms. I dropped it to one dimension to simplify the example. Maybe I shouldn't have. – Marc Thibault Jun 28 '12 at 14:02
You are correct for a VBA function. This does not happen with a C XLL function, therefore it looks like the bug is in the code that converts the VBA variant array back to Excel cells: this has not been updated for the big grid. – Charles Williams Jun 29 '12 at 08:30

score 2 · Answer 4 · edited May 23 '17 at 11:48

It's not a VBA issue because you can run this and get no errors

Public Sub test()

  x = funB(funA(2 ^ 16 - 1))
  y = funB(funA(2 ^ 16))

  Debug.Print x; y

End Sub

It seems to be an issue passing it back to Excel - not much documentation but it seems to be a Excel limit.

Here is another link but no solution WorksheetFunction array size limit

and another http://answers.microsoft.com/en-us/office/forum/office_2007-excel/passing-arrays-to-excel-worksheet-functions-in/56d76732-9a15-4fd2-9cad-41263a4045d4

Array size limits passing array arguments in VBA

4 Answers4

Linked