Home > Macros > Basics > Troubleshoot Excel Macro Troubleshooting TipsTroubleshoot 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 |
Error Message Debug ButtonOccasionally, 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 ButtonsOn this error message, there are 3 buttons available -- End, Debug and Help.
|
Debug an Excel MacroIn 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 StepLater, 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 PageNow 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 CodeAfter 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 NamesNext, to check the workbook's sheet names, you can use the Project Explorer, at the left side of the VBE window.
|
Fix the CodeNext, you'll edit the VBA macro code, to change the sheet name. Solution 1: Not Recommended
Sheets("Intro").Select Solution 2: RecommendedInstead 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 NameThe 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 MacroAfter you finish the macro changes, follow these steps to make sure the revised code is working correctly now:
|
Fix Missing References in VBESome 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:
|
1) Make a BackupBefore you make any changes to the registry, be sure to do a backup first!
2) Find Registry KeyNext, 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 RegistryIf 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 KeyFinally, follow these steps, to create the DWORD (or QWORD) in the VBA key.
|
View New Item in RegistryThe 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 RegistryAfter you finish the steps to create the new DWORD or QWORD in the Microsoft\VBA key, follow these steps:
|
Get Sample FileDebug 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 More TutorialsGetting Started with Excel Macros |
Last updated: May 31, 2023 10:41 AM