ustcer: Error Handling

http://www.functionx.com/vbaexcel/Lesson26.htm

Handling Errors

Introduction to Errors

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

In Case of Error

Jump to a Label

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.

Go to a Numbered Label

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 theOn 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.

Resume the Code Flow

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

Types of Error

Introduction

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.

Syntax Errors

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 Doublein 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.

Run-Time Errors

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).

The Err Object

Introduction

To assist you with handling errors, the Visual Basic language provides a class named Err. You don't have to declare a variable for this class. An Err object is readily available as soon as you you start working on VBA code and you can directly access its members.

The Error Number

As mentioned already, there are various types of errors that can occur to your program. To assist you with identifying them, the Err object is equipped with a property named Number. This property holds a specific number to most errors that can occur to your program. When your program runs and encounters a problem, it may stop and display the number of the error. Here is an example:

As you can see, this is error number 13. Because there are many types of errors, there are also many numbers, so much that we cannot review all of them. We can only mention some of them when we encounter them.

When a program runs, to find out what type of error occurred, you can question the Numberproperty of the Err object to find out whether the error that has just occurred holds this or that number. To do this, you can use an If...Then conditional statement to check the number. You can then display the necessary message to the user. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo WrongValue

    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

WrongValue:
    If Err.Number = 13 Then
        MsgBox "You typed an invalid value"
        HourlySalary = 0
        WeeklyTime = 0
        Resume Next
    End If
End Sub

The Error Message

As mentioned already, there are many errors and therefore many numbers held by the Numberproperty of the Err object. As a result, just knowing an error number can be vague. To further assist you with decrypting an error, the Err object provides a property named Description. This property holds a (usually short) message about the error number. This property works along with the Number property holding the message corresponding to the Number property.

To get the error description, after inquiring about the error number, you can get the equivalentDescription value. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo WrongValue

    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

WrongValue:
    If Err.Number = 13 Then
        MsgBox Err.Description
        HourlySalary = 0
        WeeklyTime = 0
        Resume Next
    End If
End Sub

In some cases, the error message will not be explicit enough, especially if a user simply reads it to you over the phone. The alternative is to create your own message in the language you easily understand, as we did earlier. If you want, you can also display a message that combines both the error description and your own message. Here is an example:

Private Sub cmdCalculate_Click()
    On Error GoTo WrongValue

    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

WrongValue:
    If Err.Number = 13 Then
        MsgBox Err.Description & ": The value you typed cannot be accepted."
        HourlySalary = 0
        WeeklyTime = 0
        Resume Next
    End If
End Sub

Practical Learning: Handling an Error

Make sure the TimeSheet worksheet is displaying.
On the Ribbon, click Developer
In the Controls section, click Insert and, in the Form Controls section, click Button (Form Control)
Click an empty on the TimeSheet worksheet
On the Assign Macro dialog box, set the Macro Name to btnSubmitTimeSheet_Click
Click New

Implement the event as follows:

Sub btnSubmitTimeSheet_Click()
    On Error GoTo btnSubmitTimeSheet_Error
    
    ' This variable will help us check the rows
    Dim CurrentRow As Integer
    ' This variable will get the employee # from the payroll
    Dim PayrollEmployeeNumber As String
    ' This variable will get the employee # from the time sheet
    Dim TimeSheetEmployeeNumber As String
    ' These 2 variables will get the date values from the time sheet
    Dim StartDate As Date, EndDate As Date
    
    ' These variables represent the time worked from the time sheet
    Dim Week1Monday As Double, Week1Tuesday As Double
    Dim Week1Wednesday As Double, Week1Thursday As Double
    Dim Week1Friday As Double, Week1Saturday As Double
    Dim Week1Sunday As Double, Week2Monday As Double
    Dim Week2Tuesday As Double, Week2Wednesday As Double
    Dim Week2Thursday As Double, Week2Friday As Double
    Dim Week2Saturday As Double, Week2Sunday As Double

    ' We will check the records starting at Row 8
    CurrentRow = 8
    
    ' Get the employee number from the time sheet
    TimeSheetEmployeeNumber = Worksheets("TimeSheet").Range("C6")

    ' Get the starting date from the time sheet
    StartDate = CDate(Worksheets("TimeSheet").Range("C8"))
    ' Add 2 weeks to the starting date
    EndDate = DateAdd("d", 13, StartDate)
    
    ' Get the time worked for each day
    Week1Monday = CDbl(Worksheets("TimeSheet").Range("C11"))
    Week1Tuesday = CDbl(Worksheets("TimeSheet").Range("D11"))
    Week1Wednesday = CDbl(Worksheets("TimeSheet").Range("E11"))
    Week1Thursday = CDbl(Worksheets("TimeSheet").Range("F11").Value)
    Week1Friday = CDbl(Worksheets("TimeSheet").Range("G11").Value)
    Week1Saturday = CDbl(Worksheets("TimeSheet").Range("H11").Value)
    Week1Sunday = CDbl(Worksheets("TimeSheet").Range("I11").Value)
    Week2Monday = CDbl(Worksheets("TimeSheet").Range("C12").Value)
    Week2Tuesday = CDbl(Worksheets("TimeSheet").Range("D12").Value)
    Week2Wednesday = CDbl(Worksheets("TimeSheet").Range("E12").Value)
    Week2Thursday = CDbl(Worksheets("TimeSheet").Range("F12").Value)
    Week2Friday = CDbl(Worksheets("TimeSheet").Range("G12").Value)
    Week2Saturday = CDbl(Worksheets("TimeSheet").Range("H12").Value)
    Week2Sunday = CDbl(Worksheets("TimeSheet").Range("I12").Value)
    
    ' Get ready to check each employee number from the payroll
    Do
        ' To process a payroll, an employee from the Accounting department
        ' enters an employee's employee number
        ' Get that employee number from the payroll
        PayrollEmployeeNumber = Worksheets("Payroll").Cells(CurrentRow, 8).Value
    
        ' Check all records from the Payroll
        ' If you find an empty cell in the columns for the eemployee number,
        ' this means that there is no record in that row.
        ' If there is no record, ...
        If PayrollEmployeeNumber = "" Then
            ' ... fill out that record with values from the time sheet
            Worksheets("Payroll").Cells(CurrentRow, 2) = TimeSheetEmployeeNumber
            Worksheets("Payroll").Cells(CurrentRow, 3) = StartDate
            Worksheets("Payroll").Cells(CurrentRow, 4) = EndDate
            Worksheets("Payroll").Cells(CurrentRow, 5) = Week1Monday
            Worksheets("Payroll").Cells(CurrentRow, 6) = Week1Tuesday
            Worksheets("Payroll").Cells(CurrentRow, 7) = Week1Wednesday
            Worksheets("Payroll").Cells(CurrentRow, 8) = Week1Thursday
            Worksheets("Payroll").Cells(CurrentRow, 9) = Week1Friday
            Worksheets("Payroll").Cells(CurrentRow, 10) = Week1Saturday
            Worksheets("Payroll").Cells(CurrentRow, 11) = Week1Sunday
            Worksheets("Payroll").Cells(CurrentRow, 12) = Week2Monday
            Worksheets("Payroll").Cells(CurrentRow, 13) = Week2Tuesday
            Worksheets("Payroll").Cells(CurrentRow, 14) = Week2Wednesday
            Worksheets("Payroll").Cells(CurrentRow, 15) = Week2Thursday
            Worksheets("Payroll").Cells(CurrentRow, 16) = Week2Friday
            Worksheets("Payroll").Cells(CurrentRow, 17) = Week2Saturday
            Worksheets("Payroll").Cells(CurrentRow, 18) = Week2Sunday
            Exit Do
        End If
        
        ' If you found a record, increase the row count by 1 ...
        CurrentRow = CurrentRow + 1
        ' ... and check the next record
        ' Continue until the next 93 records
    Loop While CurrentRow <= 93
    
    ' If there was a problem, get out of this procedure
    Exit Sub
    
btnSubmitTimeSheet_Error:
    ' If there was an error, check what type of error this was.
    ' If the error is 13, it means the user entered a bad value.
    ' Let the user know
    If Err.Number = 13 Then
        MsgBox "You entered an invalid value." & vbCrLf & _
               "Check all the values on your time sheet."
    End If
    
    Resume Next
End Sub

Close Microsoft Visual Basic
Adjust the button to your liking
Process a timesheet and click the button
Click the Payroll tab to see the result

The Source of the Error 

Most of the time, you will know what caused an error, since you will have created the application. The project that causes an error is known as the source of error. In some cases, you may not be able to easily identify the source of error. To assist you with this, the Err object is equipped with a property named Source.

To identify the application that caused an error, you can inquire about the value of this property.

Debugging and the Immediate Window

The Immediate Window

Debugging consists of examining and testing portions of your code or parts of your application to identify problems that may occur when somebody is using your database. Microsoft Visual Basic provides as many tools as possible to assist you with this task.

The Immediate window is an object you can use to test functions and expressions. To display the Immediate window, on the main menu of Microsoft Visual Basic, you can click View -> Immediate Window. It's a habit to keep the Immediate window in the bottom section of the Code Editor but you can move it from there by dragging its title bar:

Probably the simplest action you can perform in the Immediate window consists of testing an expression. For example, you can write an arithmetic operation and examine its result. To do this, in the Immediate window, type the question mark "?" followed by the expression and press Enter. Here is an example that tests the result of 275.85 + 88.26:

One of the most basic actions you can perform in the Immediate window consists of testing a built-in function. To do this, type ? followed by the name of the function and its arguments, if any. For example, to test the UCase$ function, in the Immediate window, you could type:

? UCase("République d'Afrique du Sud")

After typing the function and pressing Enter, the result would display in the next line:

The Debug Object

The Immediate window is recognized in code as the Debug object. To programmatically display something, such as a string, in the Immediate window, the Debug object provides the Printmethod. The simplest way to use it consist of passing it a string. For example, imagine you create a button on a form, you name it cmdTestFullName and initialize it with a string. Here is an example of how you can display that string in the Immediate window:

Private Sub cmdTestFullName_Click()
    Dim strFullName$
    
    strFullName$ = "Daniel Ambassa"
    Debug.Print strFullName$
End Sub

When you click the button, the Immediate window would display the passed string:

In the same way, you can create a more elaborate expression and test its value in the Immediate window. You can also pass a value, such as a date, that can easily be converted to a string.

ustcer

Friday, February 13, 2015

Error Handling

No comments:

Links