Troubleshoot a simple Microsoft 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.
Error Message Buttons
On this error message, there are 3 buttons available -- End, Debug and Help.
Debug an Excel Macro
In this example, the Excel macro was recorded, while I did the following steps:
Here is the VBA code, for the GoToMainSheet macro:
Sub GoToMainSheet() ' GoToMainSheet Macro Sheets("Main").Select Range("C3").Select End Sub
Debug Macro Step by Step
Later, when I ran that recorded macro, using the above code, Excel showed me an error message, with this text:
In that message, I followed these steps, to troubleshoot the problem:
The highlighted line, in the below code, is where the macro stopped running.
Check the Help Page
Now that you know which line of code caused the problem, you can check the Help page, to see if it gives you any clues about the cause of the error, and fixing the problem.
To go to the Help page, follow these steps:
Get Ready to Fix Code
After you check the Help page, follow these steps, so you can start fixing the problem:
OR, if there is still a line of code with yellow highlighting, follow this step:
Check Sheet Names
Next, to check the workbook's sheet names, you can use the Project Explorer, at the left side of the VBE window.
Fix the Code
Next, you'll edit the VBA macro code, to change the sheet name.
Solution 1: Not Recommended
Solution 2: Recommended
Instead of using the sheet name that you can see on the Sheet tab, we'll use the sheet's Code Name. The sheet's Code name is much less likely to be changed, by anyone other than the programmer.
In the Project Explorer, you can see each sheet's Code Name, followed by the name that appears on its sheet tab.
Change Macro to Use Code Name
The first sheet's Code Name is "Sheet1", so change the macro's code to use that name, as shown below.
This solution is better, because the code will continue to run, even if someone changes the name on the worksheet's tab in Excel.
Here is the revised code, for the GoToMainSheet macro:
Sub GoToMainSheet() ' GoToMainSheet Macro Sheet1.Select Range("C3").Select End Sub
Save Changes and Test Macro
After you finish the macro changes, follow these steps to make sure the revised code is working correctly now:
Fix Missing References in VBE
Some Excel errors, such as "Run-time error 1004: Application-defined or object defined error", can be caused by missing VBA References on your computer.
To find and fix that problem, follow these steps:
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.
Change the Registry
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:
1) Make a Backup
Before you make any changes to the registry, be sure to do a backup first!
2) Find Registry Key
Next, open the Registry, and go to the applicable VBA key, based on your version of Office and Window
What To Do Next
No VBA Key in Registry
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 VBA 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)
Note: Other Locations
Thanks to Lutz Fricke, who let me know that other locations in the registry might work too. Lutz couldn't find a Microsoft\VBA key, and created one in this location, which solved the F8 key problem on that computer:
3) Create DWORD in VBA Key
Finally, follow these steps, to create the DWORD (or QWORD) in the VBA key.
View New Item in Registry
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
4) Close Registry
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.
Show Folder Contents
Then, click the + or - button at the left of a folder name, to open and close the list of objects for that folder.
Folders Are Missing
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.
Get Sample File
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, 2023 10:41 AM