title: Excel, VBA: Clear memory
body: <p>I have a macro for Excel, nothing too fancy, no objects, just plain old cycles with formulas. Basically it's this:</p>
  
  <p>I cycle through rows in one column, assigning pretty big SUMPRODUCT functions to the cells without executing them. Once I cycled through the row I do Range.Value = Range.Value twice to execute the formulas and to save result as value. Then I go for the next column.</p>
  
  <p>With each column memory used by Excel increases significantly and after the macro is done and I save the file - the memory is still used (if I can trust Task Manager). After I close and reopen the file - all memory is, of course, freed and I can continue to work. I've reached a point where I can't process all the columns I need on a 32 bit Excel due to memory limit.</p>
  
  <p>I was wondering, maybe there's some trick I could implement between the columns to make Excel forget whatever it keeps remembering? I don't really care if it will hit performance (in case it has cached anything useful for further calculations), I just need to keep memory usage from growing too huge to process. I googled a bit but all the advises are related to setting big objects to nothing - I don't really set much of anything to anything, just cycle through, so that probably won't apply to my case.</p>
  
  <p>Code is like this (don't have it on me right now, but it's a general simplified version of what is in it):</p>
  
  <pre><code>for i = 1 to 12
      ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Font.Color = vbWhite
  
      for m = 1 to x
          ThisWorkbook.ActiveWorksheet.Cells(m, i).Formula = "'=SUMPRODUCT(blah)"
      next m
  
      ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value = ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value
      ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value = ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value
  
      ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Font.Color = vbBlack
  next i
  </code></pre>
  
  <p>Basically, I color them white so I don't see the messy function text, add function, execute it, save as values, color text black.
  <strong>Important addition: I use SUMPRODUCT to sum some cells from closed files.</strong></p>