Clear Memory in VBA

The Excel VBA Out of memory error (Run-time error 7) occurs when Excel runs out of system resources to continue running the macro.

Causes of Out of memory error

Excel can run out of system resources to run macros in the following situations:

  • When we work with a workbook that contains many worksheets and thousands of rows. This error can occur faster when we are using the 32-bit version of Excel that can use only up to a maximum of 2GB of available physical memory on the machine.
  • When we create a loop in Excel VBA that works with a very large dataset. This worsens if we mistakenly created a perpetual loop in the code.
  • When we work with multiple objects that are set with the Set keyword and we do not clear the references to the objects between loops or procedures.
  • This error could happen when we run a macro when we have many other applications running in addition to Excel.
  • When we set a worksheet’s used range that is too large to fit into memory.

Clear memory in Excel VBA

It is good practice to clear the memory in Excel VBA after running procedures to ensure optimal system performance.

The following are methods we can use to clear system memory in Excel VBA:

Method 1: Nullify objects when they are no longer needed

If we work with Objects and Loops in our procedures, we need to ensure that the Objects are set to Nothing once the Objects have been used and we no longer need them. We can follow the example below:

Sub nullifyObject()
    	Dim largeObject As Object
	Set largeObject= Application.Worksheets( "Sheet1").Range("A1:D400")
    		'loop construct
    Set someLargeObject = Nothing
End Sub

Setting Object variables to Nothing will free up the memory taken up by the Objects.

Method 2: Clear Excel clipboard

If our subroutine copies data from and to ranges, it is good practice that we clear the Excel clipboard before exiting the procedure. This is achieved by placing the Application.CutCopyMode = False statement just before ending the procedure as in the example code below.

Sub clearClipboard()
    Range("A1:A5").Select
    Selection.Copy
    Range("C1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Prevent an Out of memory error

The following are some measures we can take to prevent the Out of memory error in Excel VBA.

Measure 1: Ensure only one instance of Excel is running

Many instances of Excel running at the same time take up more memory resources. The way to check if there are multiple instances of Excel running is by:

  1. Press Ctrl + Alt + Delete on the keyboard to open the Task Manager. In the example below 2 instances of the Excel file are running.
  1. To close the instances we do not need, select that instance and click End Task.

Measure 2: Close all unnecessary applications

If in addition to Excel we have several other programs open, this reduces the amount of memory available to Excel. Closing these other programs can prevent the Out of memory error.

Measure 3: Reduce the size of the Excel file

Sometimes our procedure may be accessing rows and columns that are outside the range that contains data. These empty rows and columns take up memory. We can check for this by pressing Ctrl + Shift + End on the keyboard. If the cell selector lands way below the last cell that has data, then we have to delete all the empty rows and columns that are above the cell selector and save the file. This reduces the size of the Excel file and hence takes up less memory when it is set to an object variable.

Another way of reducing the size of the Excel file is to remove unused sheets, conditional formatting, validation, filtering, and so on.

Measure 4: Uninstall Add-ins that we do not use

Add-ins take up memory. We can uninstall the ones that we do not use.

We use the following steps:

  1. Click on File.
  1. Select Options on the sidebar.
  1. In the Excel Options dialog box select the Add-ins category, select Excel Add-ins or COM Add-ins in the Manage drop-down list, then click Go.
  1. Deselect the Add-ins we want to be uninstalled in the Add-ins available list box and click OK.

Measure 6: Do not use Global variables unless necessary

Whereas procedure-level variables are destroyed at the end of the procedure thus freeing up memory, Global variables remain in memory throughout the runtime of Excel. It is therefore recommended that we do not use Global variables if persistence between procedure calls is not necessary.

Measure 7: Change the structure of the VBA code

If we have lengthy procedures in the same module, we can change the structure of the code such that batches of it occupy different modules.

If our code is using arrays, we can use the Erase function to free the memory of dynamic arrays and reset the values of fixed arrays.

Measure 8: Set Application.ScreenUpdating to False

If we have a macro that is updating the screen constantly when it is not necessary, we can set Application.ScreeenUpdating = False. This will not only speed up the execution of the code but also save on memory usage.

We must remember to set the Application.ScreeenUpdating = True at the end of the macro.

Measure 9: Switch to the 64-bit version of Excel

The 32-bit version of Excel only allows the use of a maximum of 2GB of all the physical memory available to the device. Large excel files that need resources above 2GB will cause an Out of memory error.

If we need to use more than 2GB of memory then we can switch to Excel 64-bit version. This version allows the use of all the available memory available on the machine.

Measure 10: Increase the RAM on the device

Increasing the amount of physical RAM on our device can prevent the Out of memory error. It is important to remember that because of the 2GB limit on Excel 32-bit version, this measure can only have a significant effect on the 64-bit version of Excel.

Conclusion

The Excel VBA Out of memory error (Run-time error 7) occurs when Excel runs out of available system resources to continue running the macro.

In this tutorial we looked at some methods we can use to clear the memory in Excel VBA and several measures we can take to prevent this error.

Posted in vba