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

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.

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

Sub Whatever()    Dim someLargeObject as SomeObject    'expensive computation    Set someLargeObject = NothingEnd 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).

Answer is you can't explicitly but you should be freeing memory in your routines.

Some tips though to help memory

• Make sure you set object to null before exiting your routine.
• Ensure you call Close on objects if they require it.
• Don't use global variables unless absolutely necessary

I would recommend checking the memory usage after performing the routine again and again you may have a memory leak.