Fix: Excel Out of Memory Error

While running a VBA Macro, Excel will send you an “Out of Memory Error” when the application does not have enough memory to loop that code. 

It could be due to the large workbooks, huge Macro size, heavy processes running in the system, typos in the code, etc.

Regardless of the causes, this error is solvable with these given 6 fixes. 

End Background Apps

You’ll get an “Out of Memory” error if too many files and sheets are open. This is also true when other applications running in the background consume your computer’s memory. 

So, firstly, kill the processes from the Task Manager to free up the memory usage. On Windows, enter Ctrl + Shift + Esc to open the Task Manager window. Select Excel and hit End Task. 

Now, repeat the same to close all other applications. Restart your PC and launch only one Excel workbook where you need to run Macro. The error should be gone. 

If it does not, try running the code on a new workbook.

Edit Code

Sometimes, a simple typo in the VBA code can confuse Excel to identify the cell to loop the formula. For Instance, it could be a plural in the variable like Cells.Value3 instead of cell.value3.

Or, maybe you’ve entered an incorrect path in the code. Either way, I suggest you check the code and edit it if needed. 

Check out our other article, Modify Macros in Excel, to learn how to change code.

Clear Memory

This fix is especially for users who generated the code themselves and have knowledge of Macro codes.

Another main reason you will encounter an “Out of Memory” error is when the Microsoft VBA fails to run or save the large Macro Codes in the workbook. 

For example, attempting to run Macro with 25+ functions and subroutines in every worksheet multiple times. During such instances, the VBA may also crash when the code is stuck on the loop while saving such workbooks. 

Moreover, it is possible that you have a code that is looping itself. Or, a code with large arrays/strings that’s expanding the Macro Size. 

So, to fix this, clear the memory. Here are some of the tips on how to clear memory for Large Macro. 

• Limit the use of Public Variables in the VBA code. Use them only when required.
• Assign Nothing to your declared object variable before you close such that the memory does not hold that variable. For example, Set YourObject = Nothing. Then, End Sub. 
  
• Use Call Statement to close variables when needed.
• Consider assigning an Erase vArray statement to free up memory for an array that is no longer required.
• Check and Shrink the Excel File Size. 

Use Manual Calculation Mode

When your Calculation option is set to automatic, Excel will automatically refresh all the formulas in each recalculation. 

Although it is a good thing to keep formulas updated and saved at all times, every recalculation for large formulas in Sheet would result in “Excel ran out of resources while attempting to calculate issues.”

This is because when you run a VBA code, the formulas in the Sheets also get recalculated—especially the Volatile Formulas. 

So, change the Excel Calculation mode to Manual such that the formula updates only when you want them to. By doing so, you can save up the memory usage and also reduce the file size. 

On your Excel Workbook, navigate to the Formulas Tab. Expand Calculation Options and set it to Manual. 

Upgrade to 64-bit Excel Version

In case reducing the Excel file and macro size does not fix the error, consider upgrading your Excel to a 64-bit version. But, this is only for users who are using the 32-bit version and need to work on 2GB+ file sizes. 

If you aren’t sure about the version, launch Excel > Account. Click on About Excel and see the Version Build. 

To upgrade your Office version, firstly, uninstall the current 32-bit Office. Then, download and install the 64-bit. 

1. Open Settings > Apps.
2. Click on Installed Apps. Then, next to Microsoft 365, expand the More icon > Uninstall.
   
3. Click Uninstall. 
4. Now, download the Office from the official site. 

Update System RAM

If you still get the error, your last resort is to check the system RAM and add extra memory. For Instance, you can upgrade from 4GB RAM to 8GB.

To check the existing memory, open Settings > System. Click on About and look for Installed RAM in the device specifications.