Excel 2007 VBA 数组大小限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7817427/
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
Excel 2007 VBA Array Size Limit
提问by Farthingworth
Numerous sources I have found have suggested that the size of arrays for VBA code depends upon the amount of memory in the machine. This however hasn't been the case for me. I'm running the following, very simple, code to test:
我发现的许多来源都表明 VBA 代码的数组大小取决于机器中的内存量。然而，这对我来说并非如此。我正在运行以下非常简单的代码进行测试：
Sub test6()
Dim arr(500, 500, 500) As Boolean
End Sub
However, if I change the size to be 600x600x600, I get an out of memory error. The machine I'm using has 16Gb of RAM, so I doubt that physical RAM is the issue.
但是，如果我将大小更改为 600x600x600，则会出现内存不足错误。我使用的机器有 16Gb 的 RAM，所以我怀疑物理 RAM 是问题所在。
I'm using Excel 2007. Is there a trick to getting VBA to use more RAM?
我正在使用 Excel 2007。是否有让 VBA 使用更多 RAM 的技巧？
回答by paxdiablo
It would be nice if there was an Application.UseMoreMemory()function that we could just call :-)
如果有一个Application.UseMoreMemory()我们可以调用的函数就好了:-)
Alas, I know of none.
唉，我不知道。
All the docs I've seen say that it's limited by memory, but it's not physical memory that's the issue, it's the virtualaddress space you have available to you.
我见过的所有文档都说它受内存限制，但问题不是物理内存，而是您可以使用的虚拟地址空间。
You should keep in mind that, while the increase from 500 to 600 only looks like a moderate increase (though 20% is large enough on its own), because you're doing that in three dimensions, it works out to be close to double the storage requirements.
你应该记住，虽然从 500 到 600 的增加看起来只是适度的增加（尽管 20% 本身就足够大了），因为你是在三个维度上这样做的，结果接近两倍存储要求。
From memory, Excel 2007 used short integers (16 bits) for boolean type so, at a minimum, your 5003array will take up about 250M (500x500x500x2).
在内存中，Excel 2007 使用短整数（16 位）作为布尔类型，因此，您的 500 3数组至少将占用大约 250M（500x500x500x2）。
Increasing all dimensions to 600 would give you 600x600x600x2, or about 432M.
将所有尺寸增加到 600 将为您提供 600x600x600x2，或大约 432M。
All well within the 2G usable address space that you probably have in a 32-bit machine (I don't know that Excel 2007 hada 64-bit version), but these things are notsmall, and you have to share that address space with other things as well.
在 32 位机器上可能拥有的 2G 可用地址空间内都很好（我不知道 Excel 2007有64 位版本），但是这些东西不小，您必须共享该地址空间其他东西也是如此。
It'd be interesting to see at what point you startedgetting the errors.
看看您从什么时候开始收到错误会很有趣。
As a first step, I'd be looking into the need for such a large array. It may be doable a different way, such as partitioning the array so that only part of it is in memory at any one time (sort of manual virtual memory).
作为第一步，我将研究对如此大数组的需求。它可能以不同的方式可行，例如对数组进行分区，以便在任何时候只有一部分在内存中（类似于手动虚拟内存）。
That's unlikely to perform that well for truly random access but shouldn't be too bad for more sequential access and will at least get you going (a slow solution is preferable to a non-working one).
对于真正的随机访问来说，这不太可能表现得那么好，但对于更多的顺序访问来说应该不会太糟糕，并且至少会让你前进（缓慢的解决方案比不工作的解决方案更可取）。
Another possibility is to abstract away the bit handling so that your booleans are actually stored as bits rather than words.
另一种可能性是抽象掉位处理，以便您的布尔值实际上存储为位而不是字。
You would have to provide functions for getBooland setBool, using bitmask operators on an array of words and, again, the performance wouldn't be that crash-hot, but you would at least be able to then go up to the equivalent of:
您必须为getBooland提供函数setBool，在单词数组上使用位掩码运算符，而且性能不会那么火爆，但您至少可以达到以下效果：
' Using bits instead of words gives 16 times as much. '
Dim arr(8000, 8000, 8000) As Boolean
As always, it depends on what you need the array for, and its usage patterns.
与往常一样，这取决于您需要数组的用途及其使用模式。
回答by Charles Williams
Having run some tests it looks like there is a limit of about 500MB for 32-bit VBA and about 4GB for 64-bit VBA (Excel 2010-64).
I don't know if these VBA limits decrease if you are using a lot of workbook/pivot memory within the same Excel instance.
运行一些测试后，32 位 VBA 的限制约为 500MB，64 位 VBA（Excel 2010-64）的限制约为 4GB。
如果您在同一个 Excel 实例中使用大量工作簿/枢轴内存，我不知道这些 VBA 限制是否会降低。
回答by Petr Abdulin
As @paxdiablo mentioned the size of array is about 400+ Mb, with theoretical maximum of 2 Gb for 32 bit Excel. Most probably VBA macroses are limited in memory usage. Also memory block for array must be a contiguous block of memory which makes it allocation even harder. So it's possible that you can allocate ten arrays of 40 Mb size, but not one of 400 Mb size. Check it.
正如@paxdiablo 提到的，数组的大小约为 400+ Mb，32 位 Excel 的理论最大值为 2 Gb。很可能 VBA 宏在内存使用方面受到限制。数组的内存块也必须是连续的内存块，这使得分配更加困难。因此，您可以分配 10 个 40 Mb 大小的数组，但不能分配 400 Mb 大小之一。核实。
