564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
You can click here to download the file for this part of the lesson.
You can click here to download a file containing the sample code.
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.
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.
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.
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.
You can also resume at a specific line label. You'll learn how to do that in the next part of the lesson.
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:
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.
Choose any year and click the button shown.
Click the button shown here to reveal the instruction which caused the error.
The line which generated the error will be highlighted in yellow.
Use a different line label if you prefer.
Use On Error GoTo 0 to disable the error handler.
Enter the line label followed by a colon.
Create and set up the new worksheet using the instructions shown here.
Use Resume Next to instruct the code to continue from the line after the one which caused the run-time error.
The Flop Results worksheet will be created automatically.
25 Aytoun Street