CODEWITHSUNDEEP
excelout-of-memoryvba
Ruut
Ruut

Reputation: 1161

After upgrade from Excel 2010 to Excel 2013 large arrays in VBA give 'Out of memory' error

I recently upgraded from Excel 2010 to Excel 2013. The VBA code below worked fine in Excel 2010, but gives an error in Excel 2013. 

private a() As Variant
private b() As Variant

public sub x()
  ReDim a(19, 112, 194, 10) As Variant '19x112x194x10 x 16 bytes = 63 MB
  ReDim b(177, 112, 194) As Variant '177x112x194 x 16 bytes = 59 MB
end sub

When I reduce the array size the error disappeares. To make the error disappear the array size must be like this: 

  ReDim a(19, 112, 194, 5) As Variant '19x112x194x5 x 16 bytes = 31 MB
  ReDim b(177, 112, 60) As Variant '177x112x60 x 16 bytes = 19 MB

I am using Excel 2013 32 bit.

Would be great if I could get some advise on how to keep the original array sizes. Upgrade to 64 bit? Use a different datatype?

Any help would be appreciated.

Upvotes: 1

Views: 858

Answers (1)

Ruut
Ruut

Reputation: 1161

The problem was solved by upgrading to Excel 2013 64 bit.

In Excel 2010 the last line of the following code will cause an error:

ReDim a(19, 112, 194, 50) As Variant '19x112x194x50 x 16 bytes = 315 MB
ReDim b(19, 112, 194, 50) As Variant '19x112x194x20 x 16 bytes = 315 MB
ReDim c(19, 112, 194, 20) As Variant '19x112x194x20 x 16 bytes = 126 MB
ReDim d(19, 112, 194, 20) As Variant '19x112x194x20 x 16 bytes = 126 MB
ReDim e(19, 112, 194, 20) As Variant '19x112x194x20 x 16 bytes = 126 MB -> error in Excel 2010

Conclusion in Excel 2010 you can allocate at least 882 MB of array memory and you get an out of memory error when you allocate 1008 MB of memory

In Excel 2013 32 bit the last line of the following code will cause an error:

ReDim a(19, 112, 194, 5) As Variant '19x112x194x5 x 16 bytes = 31 MB
ReDim b(19, 112, 194, 5) As Variant '19x112x194x2 x 16 bytes = 31 MB
ReDim b(19, 112, 194, 2) As Variant '19x112x194x2 x 16 bytes = 13 MB -> error in Excel 2013 32 bit

(I uninstalled Excel 2013 32bit so the numbers above are from my memory)

Conclusion in Excel 2013 32bit you can allocate at least 62 MB of array memory and you get an error when you allocate 75 MB of memory

In Excel 2013 64 bit the last line of the following code will not give an error (but will make it slow because I run out of physical memory):

ReDim a(19, 112, 194, 300) As Variant '19x112x194x50 x 16 bytes = 1890 MB
ReDim b(19, 112, 194, 300) As Variant '19x112x194x20 x 16 bytes = 1890 MB
ReDim c(19, 112, 194, 300) As Variant '19x112x194x20 x 16 bytes = 1890 MB
ReDim d(19, 112, 194, 300) As Variant '19x112x194x20 x 16 bytes = 1890 MB
ReDim e(19, 112, 194, 300) As Variant '19x112x194x20 x 16 bytes = 1890 MB

Conclusion in Excel 2013 64bit you can allocate at least 9449 MB (!) of array memory and still do not get in trouble.

Update:

To find out how much memory is available to VBA copy/paste the code below and call availableMemoryInMB()

Function allocateMB(intNumMB As Integer) As Boolean
    On Error Resume Next
    Dim a As Variant
    ReDim a(intNumMB, 256, 256) As Variant 'intNumMB x 256 x 256 x 16 bytes = intNumMB MB
    allocateMB = (Err.Number = 0)
    Err.Clear
    Erase a
End Function

Function availableMemoryInMB() As Integer
    Dim intLow As Integer, intHigh As Integer, intTest As Integer
    intTest = 1: intHigh = 0
    Do
        If allocateMB(intTest) Then
            intLow = intTest
            If intHigh = 0 Then
                intTest = intTest * 2
            Else
                intTest = (intLow + intHigh) / 2
            End If
        Else
            intHigh = intTest
            intTest = (intLow + intHigh) / 2
        End If
    Loop Until intHigh - intLow <= 1 And intHigh > 0
    availableMemoryInMB = intLow
End Function

Execution of the code takes 2-20 seconds. By placing calls to availableMemoryInMB before and after certain function calls you can identify the functions which consume most memory.

Upvotes: 1

Related Questions