Excel Macros have made it possible for users to automate tasks that would otherwise require a lot of time and effort to execute. They can handle manual tasks in Excel, thereby enabling users to work more efficiently. However, many users complain about running into Runtime error 7 Out of memory when the Microsoft VBA tries to execute an automated task in Excel. If you are experiencing this same problem, then you are on the right page of the internet. This piece will help you understand why the error is occurring and what to do to resolve the problem.
What is Run-time error 7 out of memory in VBA?
Run-time error 7 out of memory disrupts the execution of a program in Visual Basic Application programming. Usually, when this occurs in Excel, it indicates that the Microsoft spreadsheet software cannot find enough memory to run VBA macros on the system. VBA macros in Excel use the internal Microsoft programming language to automate manual tasks and create user-generated custom functions.
The Runtime error can be caused by several factors, including compromised Excel files, too many loaded drivers, running Windows in standard mode, hardware component failure, and insufficient storage capacity. It is worth noting that when an Excel VBA macro’s program encounters a 64K segment boundary, the out-of-memory runtime error 7 also occurs.
How to fix Run-time error 7 Out of memory in Excel Macro
There are a number of fixes that you can implement to get rid of the out-of-memory runtime error in Excel VBA. However, endeavor to try out all until the problem is completely fixed. Check the following proven fixes highlighted and discussed as follows:
1. Change Macro Settings on Excel
2. Run Windows in Enhanced mode
3. Increase Virtual Memory size
4. Remove Unnecessary Device Driver
5. Defragment Hard Disk Drive
6. Increase RAM size
1] Change Macro Settings on Excel
This is the first solution you should implement to get rid of the out-of-memory runtime error 7 in Excel macros. The option you will be enabling will allow macros in any macro-enabled Excel document to access the core objects, methods, and properties necessary for execution. Follow the steps outlined below:
- Open the Microsoft Excel application on your PC, and go to File > Options.
- On the list of options on the right side of the window, select Trust Center and click on Trust Centre Settings.
- Select Macro Settings from the list of options on the right side of the window.
- Under Developer Macro Settings, ensure that the “Trust access to the VBA project object model” option is checkmarked.
- Click on OK to save the change.
2] Run Windows in Enhanced mode
As we stated earlier, running Microsoft Windows in standard mode could be responsible for the out-of-memory runtime error 7 in Excel macro. To solve the problem, restart Microsoft Windows in Enhanced mode. Follow the steps outlined below:
- Press the Windows key + I on your keyboard to open the Windows Settings.
- Navigate to Privacy & setting > Searching Windows.
- Under “Find My Files“, select the Enhanced option.
- Restart your computer afterward.
3] Increase Virtual Memory size
We have also explained that the runtime error 7 in Excel macro can also be a result of your computer running out of virtual memory. To fix the out-of-memory error, increase the virtual memory size on your computer. Follow the steps outlined below:
- Go to Control Panel > System and Security > System.
- Click on Advanced system settings on the top left side of the window, and navigate to the Advanced tab.
- Click on the Settings button under Performance.
- Go to the Advanced tab, then click on the Change button under Virtual memory.
- Uncheck the “Automatically manage paging file size for all drivers” option at the top of the window.
- Select your C: drive where the paging file will be created.
- Select Custom size and set values for both the Initial size and Maximum size.
- Click on the Set button, then save the changes by clicking OK.
- Restart your PC afterward to implement the settings.
However, the Initial size should be 1.5 times your RAM storage size (MB). For example, if you are using 8GB RAM, the Initial size will be 1024 x 8 x 1.5 = 12288. The Maximum size is 3 times the RAM storage size. Although it is not advisable to use up the maximum size, ensure that you do the calculations properly to prevent system instability.
Related: Runtime Error 76: Path not found in Excel
4] Remove Unnecessary Device Drivers
Another proven solution is to remove unnecessary device drivers. Too many device drivers loaded on your computer system could be the reason you run into the out-of-memory runtime error 7 in Excel macro. Follow the steps outlined below:
- Right-click on the Start menu and select Device Manager.
- From the list of devices on your computer, expand the ones that you consider unnecessary.
- Right-click on their drivers, then select Uninstall device.
- Restart your PC afterward.
5] Defragment Hard Disk Drive
If you are using a Hard Disk Drive (HDD), and not a Solid State Drive (SSD), you may need to defragment your drive to solve the out-of-memory runtime error 7 in Excel macro. Follow the steps outlined below:
- Open Command Prompt on your computer as an administrator.
- Type defrag C: /a and then hit the Enter key on your keyboard to analyze your drive.
The analysis will show you fragmented space on your hard drive and also indicate whether you should defragment the volume or not.
Type defrag C: to defragment the volume. Restart your computer after the process is completed.
6] Increase RAM size
If you keep getting the out-of-memory runtime error 7 in Excel macro after you have exhausted all the fixes discussed earlier, then you should consider increasing your RAM storage size.
Read: How to Prevent or Block Macros from running in Microsoft Office files
In conclusion, endeavor to implement all the fixes discussed in this article until the runtime error 7 in Excel macro has been resolved completely on your computer. We hope that you are able to get help with this piece. Good luck.
What are the three types of errors in VBA?
The three types of errors relevant to Visual Basic for Application programming are Syntax errors, Runtime errors, and Logical errors. Runtime errors like the out-of-memory error 7 pass the interpreter’s syntax checks, but they occur as a problem in the program during execution.
What is Out of memory error in VBA macro?
Out of memory error in VBA is a runtime error that happens during the execution of a statement in a program. It is a result of the Microsoft Excel application on your computer running out of system resources to complete the program execution.
