Excel 2007 VBA阵列大小限制 [英] Excel 2007 VBA Array Size Limit

问题描述

我发现许多资料表明，VBA代码的数组大小取决于计算机中的内存量.但是，对我而言并非如此.我正在运行以下非常简单的代码进行测试:

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:

Sub test6()
Dim arr(500, 500, 500) As Boolean
End Sub

但是，如果将尺寸更改为600x600x600，则会出现内存不足错误.我正在使用的计算机具有16Gb的RAM，所以我怀疑物理RAM是问题所在.

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.

我正在使用Excel2007.是否有使VBA使用更多RAM的技巧?

I'm using Excel 2007. Is there a trick to getting VBA to use more RAM?

推荐答案

如果有一个我们可以直接调用的Application.UseMoreMemory()函数，那就太好了:-)

It would be nice if there was an Application.UseMoreMemory() function that we could just call :-)

A，我一无所知.

我见过的所有文档都说它受内存的限制，但这不是物理内存，而是可用的虚拟地址空间.

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 virtual address space you have available to you.

您应该记住，虽然从500增加到600仅看起来是一个适度的增加(尽管20％本身就足够大了)，但是因为您是从三个维度进行操作，所以可以得出几乎使存储需求增加了一倍.

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.

Excel 2007从内存中将短整数(16位)用于布尔类型，因此，至少500个 ³ 数组将占用约250M(500x500x500x2).

From memory, Excel 2007 used short integers (16 bits) for boolean type so, at a minimum, your 500³ array will take up about 250M (500x500x500x2).

将所有尺寸都增加到600可以得到600x600x600x2或大约432M.

Increasing all dimensions to 600 would give you 600x600x600x2, or about 432M.

在32位计算机(我不知道Excel 2007 具有 64位版本)中可能拥有的2G可用地址空间中，一切都很好，但是这些都是< .

All well within the 2G usable address space that you probably have in a 32-bit machine (I don't know that Excel 2007 had a 64-bit version), but these things are not small, and you have to share that address space with other things as well.

很高兴看到您从什么时候开始 得到错误.

It'd be interesting to see at what point you started getting 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.

您将必须为getBool和setBool提供功能，在字数组上使用位掩码运算符，同样，性能也不会那么糟糕，但是至少您可以做到这一点达到等于:

You would have to provide functions for getBool and 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:

' 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.

这篇关于Excel 2007 VBA阵列大小限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！