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 lists of hit and flop films made in a particular year:

Select a year from the drop down list and click the Get Hit List button on the Menu sheet.

The procedure will clear the contents of the existing Hit Results worksheet and then populate it with a new set of results using the selected year:

The code uses the existing Hit Results worksheet to produce the results shown here.

In this part of the lesson we'll add code to deal with the possibility that the Hit Results worksheet may not exist.

Identifying the Problem

To demonstrate the problem caused if the Hit Results worksheet does not exist, delete the worksheet from the workbook:

Right-click the Hit Results worksheet tab and choose Delete.

Confirm that you want to delete the worksheet:

Click Delete to confirm that you want to remove the worksheet.

Return to the Menu worksheet and click the Get Hit List button:

It doesn't matter which year you select.

After clicking the button you'll see a run-time error dialog box appear:

The error message will appear as shown here.

Click Debug on the dialog box shown above to see which line has cause the error:

The error is caused by the line which attempts to select a worksheet which no longer exists.

Reset the procedure by choosing Run | Reset from the menu.

Creating an Error Handler

To solve this problem, we'll create an error handler which will create the Hit Results worksheet if it doesn't already exist.  Start by adding an On Error statement above the line which attempts to select the worksheet:

Use the On Error statement to go to a line label which we'll create shortly.

Add another On Error statement to disable the error handler after the line which selects the worksheet:

Use On Error GoTo 0 to disable the error handler.

At the bottom of the subroutine, add an Exit Sub statement followed by a line label which matches the one you have referenced in the On Error statement:

The Exit Sub statement ensures that the error handler can only be reached when a run-time error occurs.

Add instructions within the error handling section to create a new worksheet called Hit Results and copy the column headings from the Hits worksheet:

Add two instructions as shown to create and rename a new worksheet, then copy the column headings from the Hits sheet into it.

Resuming a Procedure After an Error

At this point, the error handler will successfully set up a new Hit Results worksheet if it doesn't already exist, but then the procedure will simply end.  We'd like to tell the code to return to the instruction which originally caused the run-time error and continue running from that point.  We can achieve this by adding the Resume statement to the error handling section:

Once the Hit Results worksheet has been created, the procedure will return to the instruction which attempted to select the worksheet and execute it again.

The Resume statement instructs the code to execute the instruction which originally caused the error:

The line which caused the original error attempts to select the Hit Results worksheet.

In this example, we don't need to select the Hit Results worksheet because the error handler creates a new worksheet which will be selected automatically.  Instead of using Resume, we can use Resume Next to return to the instruction immediately after the one which caused the original error:

Change the Resume statement to Resume Next.

Now the code will continue from the line after the one which originally caused the error:

When the code resumes, it won't attempt to select the Hit Results worksheet again.

Testing the Code

Return to Excel and make sure that you have deleted the Hit Results worksheet.  On the Menu sheet, select a year from the drop down list and click the Get Hit List button:

Make sure that the Hit Results worksheet doesn't exist and then test the system.

You should find that a new Hit Results worksheet is created automatically:

The new worksheet will be created and populated automatically.

Test the system again when the Hit Results worksheet exists to ensure that you haven't introduced any unusual behaviour into the system.

To practise resuming from an error:

1. Delete the Flop Results worksheet:

Right-click the worksheet tab and choose Delete.  Click Delete on the dialog box which appears to confirm that you want to delete the sheet.

2. Click the Get Flop List button on the Menu sheet:

Choose any year and click the button shown.

3. Click Debug on the run-time error dialog box which appears:

Click the button shown here to reveal the instruction which caused the error.

4. Identify the line which caused the error and then choose Run | Reset from the menu:

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

5. Add on On Error statement to redirect the code to an error handling section in the event of an error:

Use a different line label if you prefer.

6. Add an On Error statement to disable the error handler after the line which caused the error:

Use On Error GoTo 0 to disable the error handler.

7. At the bottom of the subroutine, add an Exit Sub statement followed by a line label which matches the one you referenced in the On Error statement:

Enter the line label followed by a colon.

8. Add instructions to create and rename a worksheet, then copy and paste the column headings from the Flops worksheet:

Create and set up the new worksheet using the instructions shown here.

9. Add an instruction to resume running the subroutine at the instruction after the one which caused the original error:

Use Resume Next to instruct the code to continue from the line after the one which caused the run-time error.

10. Return to the Menu sheet in Excel and click the Get Flop List button:

The Flop Results worksheet will be created automatically.

11. Check that the system still works when the Flop Results worksheet exists then save and close the workbook.