Troubleshoot a simple Excel macro, if an error message appears. Use the Debug button to find and fix a small problem in the code. How to fix the problem in the F8 key stops working while stepping through a macro. What to do if the Project Explorer list is missing
Occasionally, when you try to run a macro, an error message will appear, such as the "Run-Time Error '9': Subscript Out of Range" error shown below.
On this error message, there are 3 buttons available -- End, Debug and Help.
In this example, a macro was recorded, to go to a sheet named "Main", then select cell C3 on that sheet. When the macro is run, the above error message appears.
Here is the VBA code, for the GoToMainSheet macro:
Sub GoToMainSheet() ' GoToMainSheet Macro Sheets("Main").Select Range("C3").Select End Sub
We'll use the Debug button, to troubleshoot the problem:
We'll stop the debugger, and fix the code.
The code stops running, and the yellow highlighting disappears.
Next, you'll edit the code, to change the sheet name.
Solution 1: Not Recommended
Solution 2: Recommended
When debugging Excel VBA code, you can use the F8 key, or the Step Into command, to step through the code, one line at a time.
However, in Excel 2010 or later versions, you might encounter the problem where the F8 key (or Step Into) stops working, part way through a procedure.
Sometimes, this occurs after the code opens another file. Instead of stopping at the next line of code, it runs to the end of the procedure, or to the next breakpoint.
To fix this problem, you can make the following change in the Registry. As always, make a backup of the Registry, before making any changes, and try this at your own risk!
Thanks to Ross Connell, for sharing this solution, that he learned from Microsoft Support.
The registry change affects RPC Debugging, and you can read more about it on the Microsoft website:
Before you make any changes to the registry, be sure to do a backup first!
Next, open the Registry, and go to the applicable VBA key, based on your version of Office and Window
What To Do Next
If you can't find a VBA key in the expected location, try one of the following solutions
1. Search the Registry
Try this solution first - it's easier! (Thanks to Peter Tanner for this tip)
If that didn't work, try the next solution, below.
2. Create the Key Manually
If you can't find the Microsoft\VBA key anywhere in the Registry, follow the steps below, to create the key manually. (Thanks to Ross Connell, for sharing this additional tip)
Finally, follow these steps, to create the DWORD (or QWORD) in the VBA key.
The completed DWORD or QWORD will appear in the Registry.
For example, this screen shot shows a DWORD
This screen shot shows a QWORD, in a manually created VBA folder
After you finish the steps to create the new DWORD or QWORD in the Microsoft\VBA key, follow these steps:
When you work in the Visual Basic Editor, the Project Explorer is usually visible, showing a list of all the open VBA projects.
Click the + or - button at the left of a project name, to open and close the list of objects for that project.
NOTE: If a project is protected, and hasn't been unlocked during the current session, you will be prompted for a password when you click the + button.
Then, click the + or - button at the left of a folder name, to open and close the list of objects for that folder.
Occasionally, the project explorer folders might disappear, and an alphabetical list of the workbook's objects appears below the project name.
To show the objects grouped into folders again, follow these steps:
The folders will reappear for all projects, with objects groups into folders.
Debug Button: To test the VBA debugging steps, get the Error Message Debug Button sample file. The zipped Excel file is in xlsm format, and contains the macro named GoToMainSheet. To test the macro, be sure to enable macros when you open the workbook
Last updated: May 31, 2022 2:21 PM