If a macro does not run correctly, here are some steps you can follow to troubleshoot the problem
Excel VBA Master Class: Do your Excel programming skills need a boost? Are you wasting valuable time when you write and troubleshoot your code? Get expert training at the Excel VBA Master Class that will be held in Amsterdam, on October 26-27. This two-day course, led by Jan Karel Pieterse and Tony de Jonker, should quickly pay for itself, in the time that you'll save on your VBA projects.
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, 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
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.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: September 15, 2016 10:56 AM
Contextures RSS Feed