Home > Macros > Basics > Troubleshoot

Excel Macro Troubleshooting Tips

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

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.

• Title Bar: Microsoft Visual Basic
• Error Description: Run-time error '9': Subscript out of range

Error Message Buttons

On this error message, there are 3 buttons available -- End, Debug and Help.

• End: To stop the macro, click the End button.
  • Message closes, and the macro stops running.
• Help: To get information about the error, click Help.
  • Error message stays open
  • An information on the Microsoft website opens, in your default web browser
  • For the error message shown above, the help page is: Subscript Out of Range (Error 9)
    • Like most of the Microsoft help pages, this one shows a list of possible causes for the erroe, and suggests solutions for fixing the error
• Debug: To go to the VBE and try to solve the problem, click the Debug button.
  • This is the button that I usually click, because it takes me to the macro's VBA code, at the line were the macro stopped running. See the next section for Debug button details.

Debug an Excel Macro

In this example, the Excel macro was recorded, while I did the following steps:

1. Go to the worksheet named "Main"
2. On that sheet, select cell C3

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:

• Run-time error '9': Subscript out of range

In that message, I followed these steps, to troubleshoot the problem:

• In the error message, click the Debug button
• The Visual Basic Editor (VBE) opens, showing the GoToMainSheet macro
• In the macro, a line of code is highlighted in yellow
• There is a yellow arrow in the margin at the left of the highlighted line of code.

The highlighted line, in the below code, is where the macro stopped running.

• When it tried to run the highlighted line, there was a problem.
• It could not complete the step in that line of code.

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:

• To continue running the macro again, press the F5 key on your keyboard
  • Or, at the top of the VBE window, click the Run menu, then click the Continue command
  • 
• When the error message appears, click the Help button, to go to the Microsoft help page
• In this example, the help page has a list of 4 possible causes and fixes for the error.
  • Note: If you are new to Excel macro programming, that list might not be too helpful to you!
• Two of the causes mention "nonexistent" items, so perhaps Excel can't find the sheet named "Main".
• Next, I'll check the Excel workbook, to see if there is a problem with that sheet name

Get Ready to Fix Code

After you check the Help page, follow these steps, so you can start fixing the problem:

• Go back to Excel, in the VBE
• Click the End button in the error message, so you can check on the sheet name.

OR, if there is still a line of code with yellow highlighting, follow this step:

• At the top of the VBE window, in the toolbar, click the Reset button (a blue square)
• The code stops running, and the yellow highlighting disappears

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.

• First, look in the Project Explorer window, and find the workbook with the GoToMainSheet macro
• Click the plus sign beside the Microsoft Excel Object folder, to see its contents
• In the list of objects, check the sheet names for the workbook.
  • In the screen shot below, there are 3 sheets: "Intro", "Sheet2" and "Sheet3"
  • There is NO sheet named "Main"
  • After the macro was recorded, the "Main" sheet was renamed, as "Intro"
  • That made the "Main" sheet "nonexistent", so the macro can't run the line that refers to the "Main" sheet.
• 

Fix the Code

Next, you'll edit the VBA macro 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 name of the worksheet in the future.
• This is not the best solution

Sheets("Intro").Select

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.

• For example, in the screen shot below, the first sheet shows: Sheet1 (Intro)
  • Code name is Sheet1 - can be changed in VBE window
  • Sheet name is Intro - shown on sheet tab in Excel, easy to change in Excel

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:

• On the VBE Toolbar, click the Save button
• Next, close the VBE window, and return to Excel
• Run the macro again, and it should work correctly, without showing a runtime error.

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:

• In the Visual Basic Editor, on the menu bar, click the Tools menu
• Next, in the drop-down list of commands, click References
• If one of the references is marked as MISSING, remove its check mark
• Then, look at that reference name, and scroll down the list, to see if there is a different version of that reference.
• If you find one, add a check mark to it
• Then click OK, to close the dialog box, and see if that fixes the problem.

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 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:

• Debugging COM Clients and Servers Using RPC Debugging

1) Make a Backup

Before you make any changes to the registry, be sure to do a backup first!

• Close Excel
• Make a backup of the Registry file, then open the Registry
  • There are instructions on the Microsoft website

2) Find Registry Key

Next, open the Registry, and go to the applicable VBA key, based on your version of Office and Window

• For 32-bit Office on 64-bit Window go to registry key:
  • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VBA
• For 32-bit Office on 32-bit Window go to registry key:
  • HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\VBA
• For 64-bit Office on 64-bit Windows go to registry key:
  • HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\VBA

What To Do Next

• If you found the applicable VBA key, go to step 3, where you will create a DWORD.
• If you did NOT find the key, try the solutions in the next section, to search the registry or create the key manually.

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)

• In the Registry, right-click on the HKEY_LOCAL_MACHINE folder
• In the pop-up menu, click Find
• In the Find box, enter: Microsoft\VBA
• Click the Find Next button

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)

• First, find the folder where the VBA key should be located, based on your version of Office and Windows.
  • See the list above, and see the note below, about other locations
• In the list at the left, select that folder, to see its contents
• Next, right-click in the right pane of the window, where the contents are listed.
• Then, in the pop-up menu, click New, then click Key
• Next, rename the new key as VBA
• Then, in the list at the left, select the new VBA key, to see its contents

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:

• Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ Office\ClickToRun\REGISTRY\MACHINE\ Software\Wow6432Node\Microsoft\VBA

3) Create DWORD in VBA Key

Finally, follow these steps, to create the DWORD (or QWORD) in the VBA key.

• First, right-click in the right pane of the window, where the contents are listed.
• Then, in the pop-up menu, click New
• In the pop-up menu, click DWORD or QWORD, depending on your version of Office

• Next, name the DWORD (or QWORD) as DisableOrpcDebugging7
• Right-click the DWORD (or QWORD), and click Modify
• Change Value Data to 1, and leave the other settings as they are
• Then, click the OK button, to complete the changes

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:

• Close the Registry
• Re-open Excel
• Test the F8 key, to see if it now works correctly, stepping through the code, and stopping at each line.

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 name.

To show the objects grouped into folders again, follow these steps:

1. Click on the project name, for an unlocked project
2. 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 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

More Tutorials

Getting Started with Excel Macros

FAQs, Excel VBA, Excel Macros  

Adding Code to an Excel Workbook

Worksheet Macro Buttons

Create a Worksheet List Box