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

by

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

More Related Contents:

  1. How to clear memory to prevent “out of memory error” in VBA?
  2. Excel VBA – How to add dynamic array formula
  3. Compare double in VBA precision problem
  4. Row count on the Filtered data
  5. Excel VBA Macro: User Defined Type Not Defined
  6. Automate IE via Excel to fill in a dropdown and continue
  7. Run-Time error 1004 Excel 2013
  8. VBA – Write all possible combinations of 4 columns of data [duplicate]
  9. Remove duplicates from array using VBA
  10. Controlling Internet Explorer local intranet using Excel VBA
  11. Return RGB values from Range.Interior.Color (or any other Color Property)
  12. excel VBA run macro automatically whenever a cell is changed
  13. Translate text using vba
  14. “Can’t find Project or Library” for standard VBA functions
  15. How do I issue an HTTP GET from Excel VBA for Mac
  16. Copy sheet and get resulting sheet object?
  17. Sending mail using Outlook where the Send method fails
  18. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  19. Using a variable for row in (row,column) for a range in VBA
  20. Unicode string literals in VBA
  21. How to get the path of current worksheet in VBA?
  22. Excel VBA to match and line up rows
  23. Change font color for a part of text in cell
  24. Create a new object using the text name of the class
  25. How to open a workbook specifying its path
  26. GET pictures from a url and then rename the picture
  27. VBA: Modify chart data range
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment