Search code examples
arraysvbamultidimensional-arrayruntime-errorexcel-2010

VBA Writing Array to Range Causing Run-Time Error '7': Out Of Memory

The Task

I'm working on a VBA project wherein I store the data from a sheet in a Variant Array, do a bunch of calculations with the data in the Array, store the results of those computations in a few other Arrays, then finally I store all the "results arrays" in another Variant Array and write that array to a range in a new sheet.

Note: this process has been working fine ever since I first wrote the procedure.

The Issue

After a very minor logic change, my final Array will no longer write to the specified range. I get Run-time error '7': Out of memory when I try. I changed the logic back in case that was my issue, but I still get the error.

After doing some research, I tried to erase all the arrays (including the one containing all the sheet data) before writing to the range, but that didn't work either. I've checked Task Manager and that shows my RAM as having a lot of free space (almost 6K MBs available and over 1k Mbs "free"). (Task Manager shows the Excel process as using around 120 MBs.)

The Code (Snippet)

Dim R As Range  
Dim ALCount As Long  
Dim All(5) As Variant  
Dim sht As Worksheet
Dim Arr1() As Long
Dim Arr2() As Date
Dim Arr3() As Date
Dim Arr4() As Long
Dim Arr5() As String  

All(1) = Arr1
All(2) = Arr2
All(3) = Arr3
All(4) = Arr4
All(5) = Arr5      

Set R = sht.Cells(2,1)  
R.Resize(ALCount - 1, 5).Value = Application.Transpose(All)

More Details

My device: Win 7 64-Bit  
Excel: 2010 32-Bit  
File Size: <20 MBs  
Macro Location: Personal Workbook  
Personal WB Size: < 3 MBs

Edit: Array Size: 773x5

Any thoughts on why this might be happening?

Solution

  • I figured out the issue.

    Arr5 (a string array) was the culprit. Each element in that array is set like so:

    StrVariable = StrVariable & "--" & StrVariable    
Arr5(ALCount) = StrVariable

    I forgot to include this line to reset StrVariable after each loop:

     StrVariable = vbNullString

    The result meant that by the 773 iteration, Arr5(773) had a massive string length...so massive, in fact, that even though my code could process the array, the sheet could not.

    I discovered the error by stopping at iteration 200 and checking the output of All into the sheet (at iteration 200 the character count of Arr5(200) was already almost 3k).

    Thank you for your time and help with this problem!