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

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

Ive 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 = Nothing
End Sub

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

However this wont 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).

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

Answer is you cant 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.
  • Dont 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.

Leave a Reply

Your email address will not be published.