Out of Memory in VBA

In this article, we will discuss the Out of memory error in VBA with the help of some examples.

Out of memory Error in VBA

VBA gives an Out of memory error when Excel has used all of the resources of our device while running the macro. It will run out the memory to calculate and run the code.

It will occur when many of the programs are running on the device, and we are trying to run the extensive macro in Excel, or maybe we can create a perpetual loop when the error has occurred in Excel.

When someone is working with a workbook containing so many worksheets and many rows, an Out of memory error in VBA occurs. An Out of memory error will happen if we create a loop that will work with a significant amount of data.

If we will work with many objects and set everything with the SET statement, but if we do not clear the references to the objects between loops or procedures. Let’s discuss with the help of an example.

The code is shown below in the loop, which could cause an Out of memory error if we open multiple files 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

Release Objects in VBA

When working with loops and objects, we have to make sure that we set the object to NOTHING once we use it. We will release memory if it will no longer be needed.

set str = ""
set num = []

We will make sure that only one instance of Excel is running. If we work with enormous files and a large amount of data, we must check that they do not have many sessions open in Excel.

If it is needed, open it once. To check this, we have to go to the Task Manager and check how many instances of Excel are running at that time.

Press Ctrl+Alt+Delete on the keyboard and then click on the Task Manager to ensure that only one instance of Excel is running.

We can likewise check in the Task Manager that there is no example of Excel running behind the scenes. Look down in the Task Manager until you see Background Processes and ensure Excel isn’t in that frame of mind for projects.

Check the size of your Excel record. The frequent lines and segments have been gotten to underneath the ones in your worksheets utilized.

Succeed involves memory in these cells - regardless of whether those cells are vacant. Check the size of the document by squeezing CTRL+SHIFT+END on the console to see where your cell pointer lands.

Assuming it lands nicely underneath the last cell that you are utilizing, ensure you erase every one of the unfilled lines and sections over the cell pointer and afterward re-save the record. It will lessen the size of your Excel document.

Alternate Ways to Check the Memory in VBA

There are different alternative ways to free the memory in Excel. The brilliant idea is to close Excel on the off chance that you are not using it and afterward open it later.

It will let loose any memory that Excel is stored, and it tends to hold memory even if the workbook is not opened.

Continuously ensure your version of Office is modern by checking for updates on your device and checking for any VBA add-ins you will be using yet that you are not utilizing. You can uninstall these to let loose significantly more memory.