How to clear memory to prevent "out of memory error" in VBA?

excel, memory, vba

Solution

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).

Problem

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

Original source