数组大小限制传递数组参数在VBA [英] Array size limits passing array arguments in VBA

问题描述

Excel的VBA 2007似乎对作为参数传递数组的大小64K的限制。

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?

这里的code：

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

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

从Excel中：

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

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

看里面，FUNA（）工作正常，但传递给funB，参数x是一个错误2015年。

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

推荐答案

这似乎是尽可能接近工作四周，我能找到。做跨功能从VBA调用

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

似乎工作长达N = 2 ^ 24 = 2 ^ 8 ^ 3（这看起来并不像在VBA中的任何数据类型的破发点，这是那里的挂断，但是这是一个pretty大数组）

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)

这篇关于数组大小限制传递数组参数在VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！