In the previous lesson you learnt how to tell a procedure to ignore a run-time error when one occurs. In most situations you won't be able to get away with such a simple approach! Instead, you'll want to tell your procedure to perform a different set of instructions in the event of an error. You can achieve this by creating a custom error handler, as this part of the lesson explains!
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.
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 search for hit and flop movies:
Click the Find Hit Film button to search for a financially successful film.
After clicking one of the two buttons, you'll be asked to enter part of the title of the film you want to find:
You can enter part of a film title. This is not case-sensitive.
After clicking OK on the input box, the procedure selects the appropriate worksheet and attempts to select a cell which contains the text you have entered:
When the text you have entered is found, the procedure selects the cell and displays a message box providing details of the film.
In this part of the lesson we'll add code to deal with what happens when the title entered does not exist in the worksheet.
Identifying the Problem
The existing system has one main source of error: when the film title cannot be found, a run-time error is generated. Return to the Menu sheet in Excel and click the Find Hit Film button:
Click this button on the Menu worksheet.
Enter some text into the input box which you would not expect to find in the list of hit movies:
A great movie but not exactly a box office smash hit.
Click OK on the input box to search for the film you have entered. If the film title can't be found in the appropriate worksheet, you'll see a run-time error message:
You'll see this error message if the film title cannot be found.
Click Debug on the error message to identify the line which has caused the error:
The offending line is highlighted in yellow.
The instruction we have written attempts to apply the Select method to the range object that is returned by the Find method. When the requested text cannot be found, the Find method does not return a reference to a range object, it returns Nothing. The Select method cannot be applied to Nothing and so a run-time error is generated. Reset the procedure by choosing Run | Reset from the menu.
Redirecting Code using GoTo
When the Find method fails it would be useful to inform the user that the requested film could not be found and then end the procedure. We can achieve this by redirecting the code to a different section within the same subroutine in the event of an error. Add an On Error statement above the instruction highlighted in yellow above:
Begin an On Error statement above the line which generated the run-time error.
To redirect the code to another location in the same subroutine we can use the GoTo statement followed by the name of a line label that we want to jump to. We haven't created a line label yet but we can enter a label which describes the problem we are attempting to handle:
Enter GoTo followed by the name of the line you want to jump to. You need to use this name again later so try to make it descriptive.
We should also make sure to disable the error handler after the instruction which caused the problem. Add another On Error statement below the offending line:
Use On Error GoTo 0 to disable the custom error handler that you enabled.
Now, when a run-time error is caused by the line containing the Find method, the subroutine will look for a line label called FilmNotFound and immediately jump to it. Now we need to create the error handling section!
Creating an Error Handling Section
To create the error handling section we need to enter the name of the line label that we used in the GoTo statement earlier. By convention, this is added at the bottom of the procedure:
Enter FilmNotFound followed by a colon to create a line label.
When you move the cursor to another line, the label will move to the left hand edge of the code window:
You can't indent a line label.
We can now enter the instructions we want the procedure to execute if the Find method fails. For this example we can display a simple message:
You can make your custom error message as simple or detailed as you like.
The instruction we have just added is the last one in the procedure. This means that when the user reads the message and clicks OK the subroutine will immediately end.
Testing the Error Handler
To check that the error handler behaves as expected, return to Excel and click the Find Hit Film button on the Menu worksheet:
You can search for the same film as previously or a different one if you prefer.
After clicking OK on the input box, if the film cannot be found you'll see your custom error message:
Your custom error message is much more user-friendly than the default run-time error dialog box!
You can click OK on the message box to end the subroutine.
Exiting Before an Error Handler
When you add an error handler you should test that your code still behaves normally when no errors are created. We have inadvertently added some strange behaviour to our subroutine by adding the error handling section to it. Return to the Menu sheet and click the Find Hit Film button:
Enter the name of a film that you would expect to find in the list of hit films.
Click OK on the input box and confirm that you see a message providing information on the film you have searched for:
You should see a message like this one.
When you click OK on the message box you will see a slightly strange message appear:
We don't want to see this message when we have found the film we were looking for!
This apparently odd behaviour occurs because the subroutine treats our error handling message as just another instruction in the procedure. It will be executed at the end of the subroutine regardless of whether a run-time error has occurred. To prevent this, we need to ensure that the subroutine will exit before it reaches the error handling section. We can achieve this by adding an Exit Sub statement above the line label which indicates the start of the error handling section:
Add Exit Sub above the FilmNotFound line label.
Now the code can only reach the error handling section of the subroutine if our custom error handler is activated by a run-time error. It's worth testing that the system behaves as expected both when you search for a film which does exist and one which doesn't.
To practise creating a custom error handler:
- Click the Find Flop Film button on the Menu worksheet:
Click this button on the Menu sheet.
- Enter some text that you would not expect to find in the list of flop films:
Any string of characters will do if you can't think of a film name.
- Click OK on the input box and confirm that you see an error message:
This is the error message you will see if the film you searched for could not be found.
- Click Debug on the error message and identify the line which caused the error:
The line which generated the error will be highlighted in yellow.
- Choose Run | Reset from the menu and then add on On Error statement above the line which caused the error:
Use the On Error statement to GoTo a line label in the same procedure.
- Add an On Error statement to disable the custom error handler below the line which caused the error:
Use On Error GoTo 0 to disable the error handler.
- At the bottom of the subroutine, add a line label which matches the one used in the On Error GoTo statement:
Enter the name of the label followed by a colon.
- Add an instruction below the line label to display a message box:
Use the message box to inform the user what has gone wrong.
- Add an Exit Sub statement above the line label:
Add Exit Sub above the line label to ensure that the error handling section can only be reached when a run-time error occurs.
- Return to the Menu sheet and click the Find Flop Film button. Enter some text that you would not expect to find in the list of flop films and check that you see your custom error message:
Ensure that you see a user-friendly error message like this.
- Check that you don't see your custom error message when the code finds the film you have searched for:
When the film is found you should only see one message like this one.
- Save and close the workbook.