7

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

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.

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

Improve this question

3 Answers 3

Reset to default
7

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

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 virtual address 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.

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

Increasing all dimensions to 600 would give you 600x600x600x2, or about 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 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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The increase from 500 to 600 was just a rough working I did. Basically we're trying to use excel for something that it really wasn't designed for unfortunately, but the deadline is next week so we can't really change now! Thanks for the thoughts, I'll keep plugging away to see where things go.
Before you keep plugging away, you might want to ensure management is notified of this potential delay. They don't really like surprises :-)
Quick note on your idea to use bits as opposed to booleans, I was using booleans only for my test, in our actual work we're using structs that are holding a bunch of data about the process, so we get much less than what we can get with bools. I have already cut these down as much as possible sadly =(
More precisely it will be 536.870911875 MB or 2^32-1 Bytes. Tbh it's really sad that Excel stores a pointer to each byte of an array... It also makes little sense, it should be a pointer to each cell...
4

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.

Improve this answer

2 Comments

That seems to be about what I was getting as well, thanks. Might try and see if we can get 64-bit version - make use of the 64-bit machine that was bought but rarely used!
Just adding as this is over a decade old now, this seems to still be accurate for 64-bit at least. The max I could do was 1289 3 ways Dim arr(1289, 1289, 1289) As Boolean before running out. This was about 4GB which allows for over 2.1 billion boolean values in the array.
0

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.

Improve this answer

1 Comment

shouldn't allocator shift memory blocks to create a contiguous space? it make take time, but I don't think that it will throw out-of-memory error

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.