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.
