Excel VBA Out of Memory Error during Array Initialization

Question

I’m encountering an error in Excel 2010 (32 bit version) when trying to initialize an array. The error specifically says: “Out of Memory” I should also note that I’m coding on a 64 bit machine with 32 GB of RAM.

This is the code I am using:

   Sub ArrayTest()
   Dim C As Variant
   ReDim C(50257, 768) As Variant
   Dim row As Integer
   Dim column As Integer

   For row = 1 To UBound(C, 1)
       For column = 1 To UBound(C, 2)
           C(row, column) = rnd
       Next
   Next
   End Sub

The error occurs when I try and ReDim C().

What I tried so far:

I thought maybe I had hit the limitations of the Variant data type. So I tried to ReDim an array of type Double. I encountered the same error. I had success using a data type of Integer, but the issue is I need to capture floating point data with this array (as the Rnd functions shows).

Somebody had recommend I try using a Byte array. However Byte arrays can only capture integer data. So that didn’t work either. Another option I considered was using the Win32 API to see if it were possible to allocate the array in memory outside of Excel. Specifically the VirtualAlloc() function. I haven’t actually tried this yet as I’m not sure it will work. I’ve been told that if the underlying program you are using is 32 bits, then it won’t work.

Anyway, at this point I am perplexed as to what I should I do. I may consider using an Access database to store the data (50257 x 768 = 38,597,376 elements of data). This will slow down the code significantly though.

Are there any ways around this?

Use Long instead of Integer. Also throw Randomize in there too - well maybe at least give the docs a read and then decide whether you want it or not. — BigBen

JNevill JNevill · Accepted Answer · 2020-02-07T19:51:35

I believe Excel has a limit on memory usage at 4gb. Variant type takes up 16 bytes.

So essentially you have 50257*768*16 bytes allocated just to redim this monster. That's 6,175,580,288 or 6.1gb.

If instead you declare that array as a Double you will half that size, so ~3gb or declare it as a Single to quarter the size at ~1.5gb and it may work out.

Excel VBA Out of Memory Error during Array Initialization

1 Answers