Array size limits passing array arguments to VBA

Question

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

Does anyone know of a fix or workaround?

Here is 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 a 2015 error.

+5

Marc thibault Jun 28 '12 at 2:05

4 answers

, , VBA. Excel 2 ^ 16 , , -, .

funA(2^16) F9 - '#VALUE!'.

funA , funB, funB , .

, , (.. , funB(funA(n)) ) , .

+3

Excel , Excel 2007 (64K).

2 , :

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

Transpose , , , , .

+2

VBA,

Public Sub test()

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

  Debug.Print x; y

End Sub

, Excel - , , , Excel.

+1

DJ. 28 . '12 3:00

Brad · Accepted Answer · 2012-06-28T02:51:07+0000

It seems to work like I can find. Make cross-functional calls from VBA

If you do something like this

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

seems to work up to n = 2 ^ 24 = 2 ^ 8 ^ 3 (which doesn't look like a data type breakpoint in VBA where it hangs, but it's a rather large array)