Module 7 - Error Handling
Lesson 7.2 - Custom Error Handlers
Topic 7.2.2 - Resuming After an Error

In the previous part of the lesson we created an error handler which informed the user what had gone wrong and then ended the procedure.  In some cases it can be useful to continue running the procedure once the error handler has finished and this part of the lesson explains how to achieve that.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

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:

Click button

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:

Hit Results

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:

Delete sheet

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

 

Confirm that you want to delete the worksheet:

Confirm

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

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

Click

It doesn't matter which year you select.

 

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

Run time error

The error message will appear as shown here.

 

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

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:

On error

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:

Disable error handler

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:

Error handler

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:

Create sheet

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:

Resume

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:

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

Resume next

Change the Resume statement to Resume Next.

 

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

resume next

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

 

You can also resume at a specific line label.  You'll learn how to do that in the next part of the lesson.

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:

Test

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:

New sheet

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

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.

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

Choose any year and click the button shown.

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

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

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

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

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

Use a different line label if you prefer.

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

Use On Error GoTo 0 to disable the error handler.

 
  1. 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:
Line label

Enter the line label followed by a colon.

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

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

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

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

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

The Flop Results worksheet will be created automatically.

 
  1. Check that the system still works when the Flop Results worksheet exists then save and close the workbook.
This page has 0 threads Add post