Array size limits passing array arguments in VBA

arrays, excel, vba

Solution

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)

Problem

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.

Original source