Excel Macro Troubleshooting Tips
If a macro does not run correctly, here are some steps you can follow to troubleshoot the problem
Error Message Debug Button
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
- To stop the macro, click the End button.
- To get information about the error, click Help. In this case,
you would end up on page on the Microsoft website, with a list of
possible causes and solutions for the Subscript
Out of Range error.
- To go to the VBE and try to solve the problem, click the Debug
Debug the Macro
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:
' GoToMainSheet Macro
We'll use the Debug button, to troubleshoot the problem:
- In the error message, click the Debug button
- The VBE opens, showing the GoToMain Sheet macro, and a line of
code is highlighted in yellow, with a yellow arrow n the margin
at the left.
- Excel showed the error message, because it can't complete the
highlighted line of code.
- Look in the Project Explorer window, and check the list of sheets
for the active workbook.
- There are 3 sheets, named "Intro", "Sheet2"
and "Sheet3" -- there isn't a sheet named "Main"
We'll stop the debugger, and fix the code.
- On the VBE Toolbar, click the Reset button
The code stops running, and the yellow highlighting disappears.
Fix the Code
Next, you'll edit the code, to change the sheet name.
Solution 1: Not Recommended
- In the line of code, you could replace "Main" with "Intro",
which is the current name of the first sheet in the workbook.
- However, with that solution, the code could show an error again,
if anyone changes the sheet name in the future.
- This is not the best solution
Solution 2: Recommended
- Instead of using the name from the Sheet tab, we'll use the sheet's
Code Name. It is much less likely to be changed, by anyone other
than the programmer..
- In the Project Explorer, you can see the sheet's Code Name, followed
by the name that appears on the sheet tab.
- The first sheet's Code Name is "Sheet1", so change the
code to use that name.
- This solution is better, because the code will continue to run,
even if the name on the sheet tab is changed.
Save the Changes and Test the Macro
- On the VBE Toolbar, click the Save button, then close the VBE
window, and return to Excel
- Run the macro again, and it should work correctly, without showing
F8 Key Stops Working
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, 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.
The registry change affects RPC Debugging, and you can read more
about it on the Microsoft website: Debugging
COM Clients and Servers Using RPC Debugging
- Close Excel
- Make a backup of the Registry file, then open the Registry
-- there are instructions
on the Microsoft website
- Go to the applicable Registry Key:
- For 32-bit Office on 64-bit Window go to registry key:
- For 32-bit Office on 32-bit Window go to registry key:
- HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\VBA
Right-click in the window at the right, and click New
Click DWORD (In the screen shot below, the DWORD is for 32-bit
Office running on a 64-bit computer)
- For 64-bit Office on 64-bit Windows go to registry key:
- HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\VBA
- NOTE: If you can't find those keys, do a search in the Registry Editor (Edit>Search) for "Microsoft\VBA" (Thanks to Peter Tanner for that tip)
Name the DWORD as DisableOrpcDebugging7
Right-click the DWORD, and click Modify
Change Value to 1, and click OK.
The completed DWORD will appear in the Registry
Close the Registry, and re-open Excel, where the F8 key should
now work correctly, stepping through the code.
Project Explorer Folders Missing
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
To show the objects grouped into folders again, follow these steps:
- Click on the project name, for an unlocked project
- In the toolbar at the top of the Project Explorer window, click
the Toggle Folders button.
The folders will reappear for all projects, with objects groups into
Getting Started with Excel
VBA, Excel Macros
Adding Code to an Excel Workbook
Create a Worksheet List
Don't Miss Our Excel Tips
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Search Contextures Sites