Posted in:

How to Fix VBA Runtime Error 7 in MS Excel?

© by Shutterstock

When you open the Excel application, it uses a part of system resources. However, when the system runs out of resources, the VBA Runtime Error 7 (Out of Memory) occurs. When you try to run a macro in Excel and the resources are not sufficient, it shows this runtime error. Due to this, MS Excel cannot carry out even simple functions, like calculations or running codes. Here, we will see what causes this error and the ways to fix this error.

Causes of VBA Runtime Error 7 in MS Excel

Though the lack of memory is the primary reason for the VBA runtime error, it can also occur due to the following reasons:

  • Corruption of the FM20.dll file, which is used for encoding specific functions and settings within MS Excel.  
  • Corruption of the comctl32.ocx file. 
  • When you are working with a workbook with multiple worksheets and countless rows.
  • When the loop is created that works with a large volume of data.
  • The space is not enough on the hard drive for file submissions.
  • There are too many documents, applications, and source files are opened on your system.
  • Microsoft Windows is running in Standard Mode.

Methods to Fix VBA Runtime Error 7 in MS Excel

Here are some methods you can try to fix this error.

Method 1: Release Objects

If you are working with objects and loops, ensure that the objects are set to NOTHING once they have been used and are not required further. It will release the memory and resolve the error. 

Method 2: Ensure That a Single Instance of Excel is Running

If there are multiple sessions of Excel are running, then the application will consume more memory, resulting in the Out of Memory error. So, check the running instances of Excel and close the ones that are not required. Follow these steps:

Step 1 – Press Ctrl+Alt+Del to open Task Manager.

Step 2 – Check the instances of Excel that are running. 

Step 3 – If there are too many instances running, close the unnecessary instances to free up the system memory.

Step 4 – Scroll down to the Background Processes and check if any instance of Excel is not running in the background.

Method 3 – Run SFC Scan (System File Checker)

Any issues with the system files can also lead to runtime error 7. To fix this, you can run the sfc scannow command. It will scan the system files and replace the damaged or corrupted files (if any) with the cached copy. Follow the below steps to run the scan:

  • Step 1: Open Command Prompt as an administrator.
  • Step 2: Type the following command and press Enter.

sfc /scannow

  • Step 3: It will verify the integrity of the protected operating system files.  
  • Step 4: Once the verification process is completed, the Command Prompt window will display something like this:

Windows Resource Protection found corrupt files and successfully repaired them. Details are included in the CBS.Log windir\Logs\CBS\CBS.log. For example, C:\Windows\Logs\CBS\CBS.log. Note that logging is currently not 

supported in offline servicing scenarios.

  • Step 5: Restart your computer and see if the issue is fixed.

Method 4 – Replace Comctl32.ocx File

This is an important Windows file used to control multiple components of Visual Basic. If this file is corrupted or damaged, you may encounter issues or errors. You can replace this file by following the below-mentioned steps:

  • Step 1 – Press Windows+R, type cmd in the Run window, and click OK.

  • Step 2 – When the command prompt is open, type regsvr32 -u comctl32.ocx and then press Enter.

  • Step 3 – Once the file is unregistered, delete the file. For this, press Windows+R, type C:\Windows\System32, and then click on OK.

  • Step 4 – When the System32 folder is loaded, locate the Comctl32.ocx file. Right-click on the file and press the Delete option.

  • Step 5 – When the file is removed, replace it with a working version of the file. Download a working version of Comctl32.ocx from a reputable site and copy the new file to the C:\Windows\System32 folder.

  • Step 6 – Open the Command Prompt, type the regsvr32 Comctl32.ocx command to register the replaced file and press Enter.

Additional Methods to Resolve the Error 7

You can also try the following fixes to resolve the VBA runtime error 7:

  • Close your Excel application if you are not using it. After some time, open it. This will help to free up the memory that Excel application is storing.
  • Make sure your Excel (MS Office) version is up to date by clicking on Updates on your system.
  • Check for any VBA add-ins that you are not using. You can also uninstall these add-ins to free up some memory.
  • If there are many lengthy codes in the same module, then try to use different modules for all the codes.
  • If there are too many applications are running, close the unnecessary applications.
  • If Microsoft Windows is running in the Standard Mode, then restart your Windows in the Enhanced Mode.
  • If there are several device drivers are loaded, remove the unnecessary device drivers.

Conclusion

The common reason for VBA runtime error 7 is the lack of system resources or memory. This error can be prevented by releasing the unnecessary occupied memory in the system and ensuring that a single instance of Excel is running. Sometimes, lack of memory and storage space can lead to corruption in Excel files. In such a case, the best way to repair the corrupt Excel file is by using a powerful Excel repair software, such as Stellar Repair for Excel. It can easily repair even severely corrupted Excel file and recover all the objects, with complete integrity. This software is compatible with all Excel versions.