|
A computer application is supposed to run as smooth as
possible. Unfortunately, this is not always the case. A form may close
unexpectedly. A control on a form may hide itself at the wrong time. The
application may crash. A calculation may produce unexpected results,
etc. |
You can predict some of these effects and take appropriate
actions. Some other problems are not under your control. Fortunately, both
Microsoft Excel and the VBA language provide various tools or means of dealing
with errors.
|
 Practical
Learning: Introducing Error Handling
|
|
- Open the Georgetown Dry
Cleaning Services1 spreadsheet and click the Employees tab
- Click the Payroll tab
- Click the TimeSheet tab
- To save the workbook and prepare it for code, press F12
- Specify the folder as (My) Documents
- In the Save As Type combo box, select Excel Macro-Enabled Workbook
- Click Save
|
Introduction to Handling Errors |
|
To deal with errors in your code, the Visual Basic language
provides various techniques. One way you can do this is to prepare your code for
errors. When an error occurs, you would present a message to the user to make
him/her aware of the issue (the error).
To prepare a message, you create a section of code in the
procedure where the error would occur. To start that section, you create a label. Here is an example:
Private Sub cmdCalculate_Click()
ThereWasBadCalculation:
End Sub
After (under) the label, you can specify your message. Most of
the time, you formulate the message using a message box. Here is an example:
Private Sub cmdCalculate_Click()
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
End Sub
If you simply create a label and its
message like this, its section would always execute:
Private Sub cmdCalculate_Click()
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
End Sub
To avoid this, you should find a way to interrupt the flow of
the program before the label section. One way you can do this is to add a line
marked Exit Sub before the label. This would be done as follows:
Private Sub cmdCalculate_Click()
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
End Sub
We saw that you can create a label that
would present a message to the user when an error occurs. Before an error
occurs, you would indicate to the compiler where to go if an error occurs. To
provide this information, under the line that starts the procedure, type an
On Error GoTo expression followed by the name of the label where you
created the message. Here is an example:
Private Sub cmdCalculate_Click()
On Error GoTo ThereWasBadCalculation
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
End Sub
The On Error GoTo indicates to the compiler where to
transfer code if an error occurs.
Instead of defining a lettered label where to jump in case of
error, you can create a numeric label:
Private Sub cmdCalculate_Click()
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
28:
MsgBox "There was a problem when performing the calculation"
End Sub
After creating the numeric label, you can ask the compiler to
jump to it if a problem occurs. To do this, type On Error GoTo followed
by the numeric label. The compiler would still jump to it when appropriate. Here
is an example:
Private Sub cmdCalculate_Click()
On Error GoTo 28
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
28:
MsgBox "There was a problem when performing the calculation"
End Sub
Notice that the numeric label works like the lettered label.
In other words, before writing the On Error GoTo expression, you must
have created the label. In reality, this is not a rule. You can ask the compiler
to let you deal with the error one way or another. To do this, use the On
Error GoTo 0 (or On Error GoTo -1) expression. Here is an
example:
Private Sub cmdCalculate_Click()
On Error GoTo 0
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
End Sub
In this case, if/when the error occurs, you must have a way to
deal with it.
In every code we have explored so far, we anticipated that
there could be a problem and we dealt with it. In most cases, after dealing with
the error, you must find a way to continue with a normal flow of your program.
In some other cases, you may even want to ignore the error and proceed as if
everything were normal, or you don't want to bother the user with some details
of the error.
After you have programmatically deal with an error, to resume
with the normal flow of the program, you use the Resume operator. It
presents many options.
After an error has occurred, to ask the compiler to proceed
with the regular flow of the program, type the Resume keyword. Here is an
example:
Private Sub cmdCalculate_Click()
On Error GoTo ThereWasBadCalculation
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
Resume
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
End Sub
Notice that you can write the
Resume operator almost anywhere. In reality, you should identify where
the program would need to resume. Where else than after presenting the error message to the user? If you want the program to
continue with an alternate value than the one that caused the problem, in the
label section, type Resume Next. Here is an example:
Private Sub cmdCalculate_Click()
On Error GoTo ThereWasBadCalculation
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
Resume Next
End Sub
We know that in our code, there was probably a problem, which
is the reason we presented a message to the user. Then, when code resumes, where
should the compiler go? After all, the problem was not solved. One way you can
deal with the problem is to provide an alternative to what caused the problem,
since you are supposed to know what type of problem occurred (in the next
sections, we will analyze the types of problems that can occur). In the case of
an arithmetic calculation, imagine we know that the problem was caused by the
user typing an invalid number (such as typing a name where a number was
expected). Instead of letting the program crash, we can provide a number as an
alternative. The easiest number is 0.
Before asking the compiler to resume, to provide an
alternative solution (a number in this case), you can re-initialize the variable
that caused the error. Here is an example:
Private Sub cmdCalculate_Click()
On Error GoTo ThereWasBadCalculation
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
HourlySalary = 0
Resume Next
End Sub
If there are many variables involved, as is the case for us,
you can initialize each. Here an example:
Private Sub cmdCalculate_Click()
On Error GoTo ThereWasBadCalculation
Dim HourlySalary As Double, WeeklyTime As Double
Dim WeeklySalary As Double
' One of these two lines could produce an error, such as
' if the user types an invalid number
HourlySalary = CDbl(txtHourlySalary)
WeeklyTime = CDbl(txtWeeklyTime)
' If there was an error, the flow would jump to the label
WeeklySalary = HourlySalary * WeeklyTime
txtWeeklySalary = FormatNumber(WeeklySalary)
Exit Sub
ThereWasBadCalculation:
MsgBox "There was a problem when performing the calculation"
HourlySalary = 0
WeeklyTime = 0
Resume Next
End Sub
In our introductions to errors, we mostly anticipated only
problems related to arithmetic calculations. In reality, a program can face
various categories of bad occurrences. The more problems you prepare for, the
least phone calls and headaches you will have. Problems are divided in two broad
categories.
A syntax error occurs if your code tries to perform an
operation that the VBA language does not allow. These errors are probably the
easiest to locate because the Code Editor is configured to point them out at the
time you are writing your code.
If you try typing or try inserting an operator or keyword in
the wrong place on your code, the Code Editor would point it out. Here is an
example:
In this case, if you were trying to use the Do keyword
instead of a data type (probably Double in this case), the
Code Editor would show it right away. This type of error is pointed out for
every keyword and operator you try to use.
Notice that, in the above example, we used a valid keyword but
at the wrong time. If you mistype a keyword or an operator, you would receive an
error. Fortunately, the Code Editor is equipped to know all keywords of the
Visual Basic language. Consider the following example:
The programmer mistyped the Mod operator and wrote MAD
instead.
If you forget to include a necessary factor in your code, you
would get a syntax error. For example, if you are creating a binary arithmetic
expression that expects a second operand after the operator, you would receive
an error. Here is an example:
In this case, the programmer pressed Enter after the
Mod operator, as if the expression was complete. This resulted in an
error.
These are just a few types of syntax errors you may encounter.
As mentioned already, if you work in Microsoft Visual Basic to write your code, most of these errors are
easy to detect and fix.
A run-time error occurs when your application tries to do
something that the operating system does not allow. In some cases, only your
application would crash (Microsoft Excel may stop working). In some other cases,
the user may receive a more serious error. As its name indicates, a run-time
error occurs when the program runs; that is, after you have created your
application.
Fortunately, during the testing phase, you may encounter some
of the errors so you can fix them before distributing your application. Some
other errors may not occur even if you test your application. They may occur to
the users after you have distributed your application. For example, you can
create a car rental application that is able to display pictures 100% of the
time on your computer while locating them from the E: drive. Without paying
attention, after distributing your application, the user's computer may not have
an E: drive and, when trying to display the pictures, the application may
crash.
Examples of run-time errors are:
- Trying to use computer memory that is not available
- Performing a calculation that the computer hardware (for example the
processor) does not allow. An example is division by 0
- Trying to use or load a library that is not available or is not accessible,
for any reason
- Performing an arithmetic operation on two incompatible types (such as trying
to assign to an Integer variable the result of adding a string to a
Double value)
- Using a loop that was not properly initialized
- Trying to access a picture not accessible. Maybe the path specified for the
picture is wrong. Maybe your code gives the wrong extension to the file, even
though the file exists
- Accessing a value beyond the allowable range. For example, using a
Byte variable to assign a performed operation that produces a value the
variable cannot hold
As you may imagine, because run-time errors occur after the
application has been described as ready, some of these errors can be difficult
to identify. Some other errors depend on the platform that is running the
application (the operating system, the processor, the version of the
application, the (available) memory, etc).
