Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

memory

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

like image 504
Marc L Avatar asked Jan 18 '13 10:01

Marc L


People also ask

How do I fix out of memory error in VBA?

To correct this errorClose unnecessary applications, documents and source files. Reduce the number of Public variables. Check available disk space. Increase the available RAM by installing additional memory or reallocating memory.

How do I clear memory cache in Excel VBA?

Double-click on ThisWorkbook in the Project pane to the left to open the code window. To start the code, press F5. This action will clear the pivot tables' cache in the active workbook.

Why does VBA say out of memory?

VBA's Error 7 (Out of Memory) occurs when your system runs out of resources to back up Excel to execute the macro. When you open an application in your system that takes a part of the resource and when you have to try to execute a macro and the resource that you have is not sufficient, you get the error 7.


2 Answers

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.

like image 186
Arne Larsson Avatar answered Sep 17 '22 14:09

Arne Larsson


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

like image 24
assylias Avatar answered Sep 16 '22 14:09

assylias