Excel, VBA: Clear memory

Question

I have a macro for Excel, nothing too fancy, no objects, just plain old cycles with formulas. Basically it's this:

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.

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.

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.

Code is like this (don't have it on me right now, but it's a general simplified version of what is in it):

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

Basically, I color them white so I don't see the messy function text, add function, execute it, save as values, color text black. Important addition: I use SUMPRODUCT to sum some cells from closed files.

Yeah, sorry, added simplified code example :) All other code is basically clearing contents on some cells, message boxes and a couple of ifs, that shouldn't add much to the subject. — waterplea, Oct 22 '13 at 09:46
Could you try replacing the `SUMPRODUCT` calls with VBA code? That way, you can have a lot more control over when the external workbooks are opened and closed, or you can even sequester them off into a separate instance. Bonus: no need for `.Value = .Value`. — Chel, Oct 22 '13 at 10:08
I did try getting the values with pure VBA at first but it was extremely slow with lots of rows that I'm having. — waterplea, Oct 22 '13 at 11:05
The way I like to get around long calculation times is by setting up an array in VBA to store the values until all the calculations are done. Changing the values on excel is a lot faster if you do it all at once as a single output. (at least in my experience) — Ratafia, Oct 22 '13 at 14:14

score 6 · Accepted Answer · answered Oct 22 '13 at 10:11

This isn't really enough code to help you improve the efficiency. I can point out a few tips for you now though.

The first thing to remember is to switch application screen updating to false at the start of the macro.

Application.ScreenUpdating = False

then switch it back just before the end of the macro code

Application.ScreenUpdating = True

Changing the font color is unnecessary now. Your screen will be locked for refreshing while the macro is being executed so you will not be seeing the changes until the macro has finished working.

Consider, disabling Events too.

Application.EnableEvents = False and Application.EnableEvents = true same idea as with the screen updating.

The second thing is to make use of Evaluate() function. Please google it and read about how efficient that function can be.

Instead of putting a function into cell and then doing .Value = .Value you can just Evaluate() an expression and return the result straight to the cell which is much faster!

So you could do something like

for i = 1 to 12
    ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value = Evaluate("=SUM(blah)")
next i

I hope you get the idea.

Here are a few links with tips on how to speed up your macro:

Thanks for the tips, I will try them! At first I tried getting the values I need with VBA, but it was slowly iterating cell by cell, while functions, if I calculate them all at once were quite fast, so I had to make it the way I described. I will definitely try Evaluate now, looks this could help in my case, thank you very much! — waterplea, Oct 22 '13 at 11:09
As for the overall title question, there is no way to clear memory used by Excel without closing it? — waterplea, Oct 22 '13 at 11:10
no, not really there is no way to *clear* memory. Have a look at the `Set objName = Nothing` but this will not really help if you need to reuse objects later in your code. — , Oct 22 '13 at 11:16
another alternative would be to split your macro into smaller parts and call them separately from a [**`master workbook`**](http://stackoverflow.com/questions/17461935/vba-macro-to-mass-update-multiple-files-in-same-location/17470628#17470628). — , Oct 22 '13 at 11:19
Master workbook is a nice idea, thank you! I might end up using it if Evaluate will not help. — waterplea, Oct 22 '13 at 11:58
yep, no problem and dont forget to leave some feedback when youre done. — , Oct 22 '13 at 11:59

score 2 · Answer 2 · answered Apr 27 '16 at 12:19

I know this is an old question but a way that seems to work is saving the Workbook. Run ThisWorkbook.Save every once in a while and if you monitor EXCEL in the Task Manager you will notice that it clears up a significant amount of memory.

It seems as though Excel may be caching certain function calls or something and saving clears this cache.

Excel, VBA: Clear memory

2 Answers2