The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a workbook which allows you to create a list of hit and flop movies released in a particular year:

You can select a year from the drop down list on the Menu sheet. Clicking the Get Hit Results button should create a new worksheet called Hit Results and copy all of the films made in the selected year from the Hits worksheet.

Identifying the Problem

The subroutines attached to each button on the Menu sheet will generate a run-time error when attempt to run them.  Try clicking the Get Hit Results button:

You'll see this error message when you click the Get Hit Results button.

Click Debug on the dialog box shown above to identify the line which has caused the error:

The line highlighted in yellow attempts to delete an existing worksheet called Hit Results.

The line highlighted in yellow above fails because the Hit Results worksheet doesn't exist.  Stop the procedure by choosing Run | Reset from the menu.

Ignoring the Error

The easiest way to avoid this problem is to add an On Error statement which will ignore the error  - if the worksheet doesn't exist, it doesn't need to be deleted in the first place!  Add an instruction to do this above the line which attempts to delete the Hit Results worksheet:

Use On Error Resume Next to instruct the code to ignore any run-time errors and continue from the next instruction.

Return to Excel and click the Get Hit Results button again to check that the code now works:

After clicking the button you should see a list of films from the year that you selected on the Menu worksheet.

You can return to the Menu sheet and choose a different year then click the button again to show that the Hit Results sheet is being deleted and recreated properly.

Other Potential Problems

The error-handling code seems to be working correctly to solve the first problem we encountered, however, there is another potential issue.  When you add an On Error statement to a procedure, it affects every subsequent instruction in the procedure:

Everything below the On Error Resume Next statement is affected by it.

We've told our procedure to ignore any run-time error that occurs until the end of the subroutine!  To demonstrate why this might be a problem, return to Excel and change the name of the Menu worksheet to Main Menu:

Alter the name of the worksheet to something other than Menu.

Now click the Get Hit Results button again and look at the result:

Regardless of which year you have selected, you'll end up with an empty list.

This problem occurs because code in the Get_Hit_Results subroutine refers to the Menu worksheet by name:

This instruction refers to a worksheet which no longer exists.

The instruction shown above causes a run-time error, but our On Error statement has told the subroutine to ignore them and so it continues to execute the code as though nothing has gone wrong!

Disabling an Error Handler

When an error handler is enabled using an On Error statement, it remains enabled until either the end of the procedure or until it is explicitly disabled.  In our example, we'd like to disable the error handler after the instruction which attempts to delete the Hit Results worksheet.  To do this, we can add another On Error statement after this instruction:

Begin a new On Error statement below the line which deletes the Hit Results worksheet.

To disable the error handler in VBA, you add GoTo 0 to the end of the On Error statement:

Complete the instruction by adding GoTo 0.

You can now click the Get Hit Results button again to check the effect of your changes:

You'll encounter another run-time error message.

Click Debug on the dialog box to check which line has caused the error:

This time, it's the instruction which refers to the Menu sheet that has caused the problem.

You can solve this problem in several ways but perhaps the best choice is to assign a code name to the worksheet using the Properties window:

Select the Main Menu worksheet in the Project Explorer and then type a code name into the (Name) property in the Properties window.

You can then use this new code name to reference the menu worksheet, regardless of what name appears on the worksheet tab in Excel:

Use the code name to refer to the worksheet.

To practise disabling an error handler:

1. In Excel, click the Get Flop Results button:

Click this button on the worksheet formerly known as Menu.

2. Click Debug on the error message that appears:

Click Debug to show the line that caused the error.

3. Identify the instruction which caused the error and then choose Run | Reset from the menu:

The line which caused the error will be highlighted in yellow.

4. Add an On Error statement above the line which caused the error:

Use On Error Resume Next to ignore errors caused the instructions below.

5. Return to Excel and click the Get Flop Results button again:

This time the code will execute without errors but won't produce any results.

6. Return to the code and add an On Error statement to disable the error handler after the line which attempts to delete the Flop Results worksheet:

Use On Error GoTo 0 to disable the error handler.

7. Return to Excel and click the Get Flop Results button.  Click Debug on the dialog box which appears and identify the line which has caused the run-time error:

The line which refers to the Menu worksheet has caused an error this time.

8. Reset the code and edit the line which caused the error to use the code name of the menu worksheet:

Use the code name that you assigned to the worksheet earlier.

9. Click the Get Flop Results button again and check that you see a list of results:

The code will now produce results as expected.

10. Save and close the workbook.