none
How Can I Clear Excel's Memory or Cache? RRS feed

  • Question

  • Is there a way to clear Excel's memory or cache or some such thing?  I need to run a massive process on around 5,000 rows and 1,200 columns.  It keeps crashing, and the error message I'm getting is 'out of memory'.  Can I run a loop, like 500x, and then clear the cache, and then run another 500 iterations, and then clear it again, and so on and so forth?

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, August 25, 2015 9:50 PM

Answers

  • Is there a way to clear Excel's memory or cache or some such thing? 

    I need to run a massive process on around 5,000 rows and 1,200 columns. 

    a) No.

    b) That's big... a little, not massive. Read the data into an array and process that.

    Make a new file, run the code below. The write process needs around 10 seconds, the read process just one... on my machine.

    Andreas.

    Option Explicit

    Sub Setup()
    Dim Data(1 To 5000, 1 To 1200)
    Dim i As Long, j As Long
    For i = 1 To UBound(Data)
    For j = 1 To UBound(Data, 2)
    Data(i, j) = i
    Next
    Next
    Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
    End Sub

    Sub Demo()
    Dim Data
    'Read in
    Debug.Print Now
    Data = Range("A1").CurrentRegion.Value
    Debug.Print Now

    'Write Back
    Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
    Debug.Print Now
    End Sub

    Wednesday, August 26, 2015 7:16 AM
  • Your range is not massive but I would normally process something that size in a series smaller chunks where possible, particularly 'write'.

    There may be some other reason for your memory error or something related what your process involves. It might not directly be because of lack of memory, 'out of memory' seems to be a generic error sometimes.

    The memory used will be VBA's and Excel's which are managed separately. VBA's is normally managed very efficiently and should clear automatically as variables loose scope, that is unless you introduce any circular references.

    • Marked as answer by ryguy72 Wednesday, August 26, 2015 4:16 PM
    Wednesday, August 26, 2015 10:26 AM
    Moderator
  • It just takes sooooooooooo long for all the recalculations to finish. 

    Can I use your concept, Andreas, to do this task?

    Well, yes, but you can not put a formula into an array, you can theoretically use the WorksheetFunction Object and/or call Application.Evaluate

    That might be a bit faster, but only when you optimize the process, means when you cache some data, e.g. there is no need to get "INDEX($2:$2,0,COLUMN()" for every cell.

    I don't know what your formula does, I suggest that you study this article, it was where helpful for me to understand what is fast and what is slow and how to measure things in Excel.

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    I guess the calculation can be much faster when you use VBA only.

    Andreas.

    • Marked as answer by ryguy72 Wednesday, August 26, 2015 4:16 PM
    Wednesday, August 26, 2015 2:37 PM

All replies

  • Is there a way to clear Excel's memory or cache or some such thing? 

    I need to run a massive process on around 5,000 rows and 1,200 columns. 

    a) No.

    b) That's big... a little, not massive. Read the data into an array and process that.

    Make a new file, run the code below. The write process needs around 10 seconds, the read process just one... on my machine.

    Andreas.

    Option Explicit

    Sub Setup()
    Dim Data(1 To 5000, 1 To 1200)
    Dim i As Long, j As Long
    For i = 1 To UBound(Data)
    For j = 1 To UBound(Data, 2)
    Data(i, j) = i
    Next
    Next
    Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
    End Sub

    Sub Demo()
    Dim Data
    'Read in
    Debug.Print Now
    Data = Range("A1").CurrentRegion.Value
    Debug.Print Now

    'Write Back
    Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
    Debug.Print Now
    End Sub

    Wednesday, August 26, 2015 7:16 AM
  • Your range is not massive but I would normally process something that size in a series smaller chunks where possible, particularly 'write'.

    There may be some other reason for your memory error or something related what your process involves. It might not directly be because of lack of memory, 'out of memory' seems to be a generic error sometimes.

    The memory used will be VBA's and Excel's which are managed separately. VBA's is normally managed very efficiently and should clear automatically as variables loose scope, that is unless you introduce any circular references.

    • Marked as answer by ryguy72 Wednesday, August 26, 2015 4:16 PM
    Wednesday, August 26, 2015 10:26 AM
    Moderator
  • I tried to do this in chunks, and that didn't really work.  It just takes sooooooooooo long for all the recalculations to finish.  Basically, this is what I need to do.  Put this function in cell E11.

    =HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()+37,FALSE)*IFERROR(VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0),0)

    Then, fill down to E5496.  Next, fill right to ATM5496.  So, the range is E11:ATM5496.  Finally recalculate the Worksheet (not the entire Workbook).  I believe an array is the way to go.  Can I use your concept, Andreas, to do this task?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, August 26, 2015 12:41 PM
  • It just takes sooooooooooo long for all the recalculations to finish. 

    Can I use your concept, Andreas, to do this task?

    Well, yes, but you can not put a formula into an array, you can theoretically use the WorksheetFunction Object and/or call Application.Evaluate

    That might be a bit faster, but only when you optimize the process, means when you cache some data, e.g. there is no need to get "INDEX($2:$2,0,COLUMN()" for every cell.

    I don't know what your formula does, I suggest that you study this article, it was where helpful for me to understand what is fast and what is slow and how to measure things in Excel.

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    I guess the calculation can be much faster when you use VBA only.

    Andreas.

    • Marked as answer by ryguy72 Wednesday, August 26, 2015 4:16 PM
    Wednesday, August 26, 2015 2:37 PM
  • Got it.  Thanks guys!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, August 26, 2015 4:16 PM
  • One more question.  Why wouldn't one of these work?

    Range(Cells(11, 1), Cells(Range("A65536").End(xlUp).row, 5)).SpecialCells(xlCellTypeConstants) _
    .Offset(0, 5).FormulaR1C1 = "=HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()+37,FALSE)*IFERROR(VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0),0)"

    That gives:  Application-defined or object-defined error.

    Range("E11:E5496").SpecialCells(xlCellTypeConstants).FormulaR1C1 = _
    "=HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()+37,FALSE)*IFERROR(VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0),0)"

    That gives:  No cells were found


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, August 26, 2015 4:34 PM
  • I got it working.

    Range(Cells(11, 1), Cells(Range("A65536").End(xlUp).row, 5)).SpecialCells(xlCellTypeConstants).Offset(0, 2).Formula = _
        "=HLOOKUP(INDEX($2:$2,0,COLUMN()),CP_lookup_range,ROW()+37,FALSE)*IFERROR(VLOOKUP(CONCATENATE(INDEX($A:$A,ROW()),INDEX($1:$1,0,COLUMN())),FISCALIZATION_DARK,2,0),0)"

    FormulaR1C1

    must be

    Formula

    It's still slow!!!

    Oh well......


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, August 26, 2015 7:59 PM
  • It's still slow!!!

    Well it would be. Even after applying the formulas I wouldn't want to be managing a workbook like that!
    Thursday, August 27, 2015 12:24 PM
    Moderator