Excel 2007 VBA Array Size Limit Size

Question

Numerous sources that I have found have suggested that the size of the arrays for VBA code depends on the amount of memory in the machine. This, however, was not for me. I run the following very simple code for testing:

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

However, if I resize 600x600x600, I get an error from memory. The device I use has 16 GB of RAM, so I doubt that there is a problem with physical memory.

I am using Excel 2007. Is there a trick to get VBA to use more RAM?

Answer 1

Having run some tests, it seems that for 32-bit VBA there are about 500 MB and about 4 GB for 64-bit VBA (Excel 2010-64).
I do not know if these limitations are limited VBA reduce if you use a large amount of workbook / memory in one instance of Excel.

Answer 2

It would be nice if there was a Application.UseMoreMemory() function, which we could just call :-)

Alas, I do not know anything.

All the documents that I saw say that they are limited by memory, but this is not physical memory, the problem is that this is a virtual address space available to you.

You should keep in mind that although an increase from 500 to 600 only looks like a moderate increase (although 20% is large enough on its own) because you do it in three dimensions, it works close to storage requirements.

From memory Excel 2007 uses short integers (16 bits) for the boolean type, so at least your 500 ³ array will occupy about 250 M (500x500x500x2).

Increasing all sizes to 600 will give you 600x600x600x2 or about 432M.

Everything is within the valid 2G address space that you probably have on a 32-bit machine (I don't know that Excel 2007 had a 64-bit version), but these things are not small, and you need to share this address space with others things.

It would be interesting to see at what point you began to receive errors.

As a first step, I will look for the need for such a large array. This can be done in another way, such as splitting the array so that only part of it is in memory at any time (manual virtual memory).

This is unlikely to make it good for truly random access, but should not be too bad for more consistent access and at least you will go (a slow solution is preferable to a non-working one).

Another possibility is to abstract the processing of bits so that your logical values ​​are actually stored as bits, not words.

You will need to provide functions for getBool and setBool using the bitmask operators in the array of words, and again, the performance will not be so hot, but you can at least go to the equivalent:

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

Answer 3

As @paxdiablo noted, the size of the array is about 400+ MB, a theoretical maximum of 2 GB for 32-bit Excel. Most likely, VBA macros are limited in memory usage. Also, the memory block for the array must be a continuous memory block, which makes its distribution even more difficult. Thus, you can allocate ten arrays of 40 MB in size, but not one of 400 MB. Check it out.