数组大小限制在 VBA 中传递数组参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11237195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Array size limits passing array arguments in VBA
提问by Marc Thibault
Excel-VBA 2007 appears to have a 64k limit on the size of arrays passed as arguments.
Excel-VBA 2007 似乎对作为参数传递的数组大小有 64k 限制。
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:
来自 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.
往里面看，funA() 工作正常，但是，传递给 funB，参数 x 是一个错误 2015。
采纳答案by Brad
This seems to be as close to a work around as I can find. Do the inter-function calls from VBA
这似乎与我能找到的解决方法非常接近。从 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)
它似乎可以工作到 n=2^24=2^8^3 （这看起来不像 VBA 中的任何数据类型断点，这是挂断的地方，但这是一个非常大的数组）
回答by Michael Davies
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.
我认为这是电子表格单元格本身的限制，而不是 VBA。Excel 可以在函数之间传递大于 2^16 的数组，但显然它不能在单元格中包含该大小的数组。
As an experiment, highlight funA(2^16)in the cell formula and hit F9 - it'll give you a '#VALUE!'error.
作为实验，funA(2^16)在单元格公式中突出显示并按 F9 - 它会给你一个'#VALUE!'错误。
Because the formula has already calculated the result of funAbefore it initiates funB, it's then trying to run funBon a function that's already calculated to an error.
因为公式funA在启动之前已经计算了 的结果funB，所以它会尝试funB在一个已经计算出错误的函数上运行。
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.
这似乎是一种解决方法，就像 Brad 发布的一个（即funB(funA(n))在其内部进行计算的第三个函数）将单元格保持在等式之外，直到计算完成，所以它工作正常。
回答by Charles Williams
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).
由于一维数组作为一行列传回 Excel，因此您已达到 Excel 2007 对列数 (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.
或者，您可以使用 Transpose 将数组从一行旋转到一列，但这可能比首先创建二维数组效率低。
回答by DJ.
It's not a VBA issue because you can run this and get no errors
这不是 VBA 问题，因为您可以运行它并且不会出错
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.
将其传递回 Excel 似乎是一个问题-文档不多，但似乎是 Excel 限制。
Here is another link but no solution WorksheetFunction array size limit
这是另一个链接，但没有解决方案WorksheetFunction 数组大小限制
