VBA Out of Memory Error

This tutorial will explain the VBA Out of Memory Error.

The VBA Out of Memory error occurs when Excel has used all the resources of your machine while running a macro and literally runs out of memory to carry on calculating or running code. This could occur when you have a lot of applications running and try to run a large macro in Excel, or perhaps when you have created a perpetual loop in Excel in error.

(See our Error Handling Guide for more information about VBA Errors)

Causes of Out of Memory Error

An out of memory error can occur if one is working with a workbook that contains many worksheets and thousands of rows. If we create a loop that works with a great volume of data, an out of memory error could occur. It could also occur if we are working with multiple objects and set each object with a SET statement, but then do not clear the references to the objects between procedures or loops.

For example, the following loop could definitely cause a memory error if you have multiple files open with multiple sheets.

Sub TestMemory()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim i As Single
  For Each wb In Application.Workbooks
    For Each ws In wb.Sheets
      Do Until ActiveCell = "A1048576"
        ActiveCell = 1 + i
        i = i + 1
        ActiveCell.Offset(1, 0).Select
    Loop
  Next ws
 Next wb
End Sub

Preventing an Out Of Memory Error

Release Objects

If we are working with Loops and Objects, we need to make sure that we set the Object to NOTHING once it has been used and it no longer needed – this will release memory.

Make Sure Only one Instance of Excel is Running

If we are working with large files and vast amounts of data, check that you do not have multiple sessions of Excel open – it needs to just be open once.  A way to check this is to go to the Task Manager and see how many instances of Excel are running.

Press Ctl+Alt+Delete on the Keyboard,

Click on Task Manager and make sure that there is only one instance of Excel running.  In the graphic below, there is one instance, with 2 windows.

We can also check in the Task Manager that there are no instance of Excel running in the background (ie not visible).

Scroll down in the Task Manager until you see Background Processes and make sure Excel is not in that list of programs.

 

Check the Size of your Excel file

Often there are rows and columns that have been accessed below the ones in your worksheets that are actually used.  Excel uses memory in these cells – even if those cells are empty.  Check the size of the file by pressing CTRL+SHIFT+END on the keyboard to see where your cell pointer lands.  If it lands well below the last cell that you are using, make sure you delete all the empty rows and columns above the cell pointer and then re-save the file – this will reduce the size of your Excel file.

Other ways to Check Memory

There are various other ways to free memory in Excel.  A good idea is to close Excel if you are not using it, and then open it later – this will free up any memory that Excel is storing as it tends to store memory even when a workbook is not open!  Always make sure your version of Office is up to date by checking for Updates on your PC and check for any VBA add-ins that may be being used, but that you are not using – you can uninstall these to free up even more memory.