How to clear memory to prevent "out of memory error" in excel vba?

I am running VBA code on a large spreadsheet. How do I clear the memory between procedures/calls to prevent an "out of memory" issue occurring?

Thanks

Solution 1:

I've found a workaround. At first it seemed it would take up more time, but it actually makes everything work smoother and faster due to less swapping and more memory available. This is not a scientific approach and it needs some testing before it works.

In the code, make Excel save the workbook every now and then. I had to loop through a sheet with 360 000 lines and it choked badly. After every 10 000 I made the code save the workbook and now it works like a charm even on a 32-bit Excel.

If you start Task Manager at the same time you can see the memory utilization go down drastically after each save.

Solution 2:

The best way to help memory to be freed is to nullify large objects:

Sub Whatever()
    Dim someLargeObject as SomeObject

    'expensive computation

    Set someLargeObject = Nothing
End Sub

Also note that global variables remain allocated from one call to another, so if you don't need persistence you should either not use global variables or nullify them when you don't need them any longer.

However this won't help if:

• you need the object after the procedure (obviously)
• your object does not fit in memory

Another possibility is to switch to a 64 bit version of Excel which should be able to use more RAM before crashing (32 bits versions are typically limited at around 1.3GB).