Out Of Memory, Run-Time Error 7

[jgreggain]

Hello,

I have been tasked with creating a program that will parse out data from some control modules on an automated robotics line. They log every movement, and as such can generate some pretty huge ascii text .dat log files. For instance, my current "test" batch of data come in at a whopping 1,023,997 entries!

My work computer is running the 32-bit version of Office 2010, and I can't upgrade it, nor can I expect the group I'm handing this off to to be able to upgrade.

My process is that I first grab each line of text from the .dat files, and parse them out into an array. Actually, I first place the line of text into a 1-D array, and then break that down into a second, 2-D array, and then erase the first. Since I started erasing the first array after parsing it, that subroutine hasn't had a problem.

The problem comes from when I try to populate worksheets. Since putting all that data into one worksheet is not practical or useable, I'm putting data from each day into it's own worksheet. Those worksheets can still hold around 110,000 entries, but that's much more manageable.
To do this quickly and easily, I'm constructing sub-arrays for each day, and then setting a matched range on the target worksheet to the sub-array. This allows Excel to copy the data into the worksheet much more quickly than doing a line-by-line method. I would have just set the range equal to a specific part of the array, but there's not function to do that, that I can find at least. So, I copy the data from the main array into a sub-array, and then set the range equal to that.
I have been getting a consistent "out of memory" error when trying to redim the sub-array around the 11th time the loop runs. It stops at this point no matter what I do.

To attempt to fix this, I've done the following: All objects are set to nothing when they're no longer in use, the sub-array is erased at the end of each loop, and I've even created script where the workbook that holds the results only holds 5 days (5 sheets per workbook). When all five days' data have been placed, it sets the workbook object to nothing, erases the temporary arrays, and closes the workbook. A new workbook is created for the next 5 (or fewer) days. I did this to avoid having a 50mb excel file open when trying to run the macro. I instead get a series of smaller, 15-20mb files, which are closed before the next is created.
Despite having done all this, I'm still getting an Out Of Memory error. The error always happens when I try to ReDim the sub-array. Does anyone have any ideas on how I can fix this? Please let me know.

There is a lot of code associated with this process, but here's the code from where it fails:

Sub printArray(wkbk As Workbook, dateArr, subBreakRows)
    
    Dim i As Long, lineCount As Long, arrCount As Long
    Dim j As Integer
    Dim ws As Worksheet
    Dim subArray()
    
    For i = LBound(dateArr) To UBound(dateArr)
        Set ws = wkbk.Worksheets(i + 1)
        With ws
            .Columns("A:A").NumberFormat = "m/d;@"
            .Columns("B:B").NumberFormat = "h:mm:ss;@"
            .Columns("C:D").NumberFormat = "0"
            .Columns("E:K").NumberFormat = "@"
            .Columns("A:K").Font.Name = "Courier"
            .Columns("A:K").HorizontalAlignment = xlRight
        End With
        ws.Cells(1, 1) = "Date"
        ws.Cells(1, 2) = "Time"
        ws.Cells(1, 3) = "Hour"
        ws.Cells(1, 4) = "Station"
        ws.Cells(1, 5) = "Command"
        ws.Cells(1, 6) = "BCR No."
        ws.Cells(1, 7) = "RFID"
        ws.Cells(1, 8) = "Position"
        ws.Cells(1, 9) = "CID"
        ws.Cells(1, 10) = "Flag 1"
        ws.Cells(1, 11) = "Flag 2"
        
        [COLOR=#800000]ReDim subArray(subBreakRows(i + 1) - 1, UBound(splitArray, 2))[/COLOR]
        arrCount = 0
        For lineCount = subBreakRows(i) To subBreakRows(i + 1) - 1
            For j = 0 To UBound(splitArray, 2)
                subArray(arrCount, j) = splitArray(lineCount, j)
                splitArray(lineCount, j) = 0
            Next j
            arrCount = arrCount + 1
        Next lineCount
        
        ws.Range(ws.Cells(2, 1), ws.Cells(UBound(subArray, 1) + 2, UBound(subArray, 2) + 1)) = subArray
        ws.Columns("A:K").EntireColumn.AutoFit
        Set ws = Nothing
        Erase subArray()
    Next i
End Sub

Dark Red text is where the error occurs.
Breakrows is a small 1D array that holds the starting line of each day. subBreakRows is the appropriate sub-section of that array, for the current 5-day span. dateArr is a 1-D array that holds the dates for the 5-day period. Wkbk is the workbook with the appropriate number of sheets for the days.

Thanks,
Joel

 

[StephenCrump]

Just a very quick observation ...

You have:

ReDim subArray(subBreakRows(i + 1) - 1, UBound(splitArray, 2))

but you only loop:

For lineCount = subBreakRows(i) To subBreakRows(i + 1) - 1

So haven't you given subArray way too many rows that you don't even use?

 

[jgreggain]

Thanks for your response.
I think you may be on to something there. I think need to dim the subarray as:

ReDim subArray(subBreakRows(i+1) - subbreakrows(i) - 1, UBound(splitArray,2))

I'll see if that helps. Thanks!

 

[jgreggain]

Turns out that fixed the problem. Guess I was breaking the data down, but leaving the subarray to grow to match the ~1million size, which kind of defeated the purpose.
Thank you, StephenCrump for that catch!

Now, if they need to go larger than ~1million entries, I'm not positive they won't run into the same problem. I'll cross that bridge if/when we come to it.

Thanks again!