Understand the different type of errors
Trapping and handling errors
Using the VBA on Error and Resume statements
Finding how you can use an error to your advantage
No matter how well you are at writing VBA code, sooner or later you will encounter an error when running your program . For example, you may have type a keyword incorrectly or misspell the syntax. If such an error occurred, you won’t even be able to execute the procedure until you correct it.
When working with VBA, you should be aware of these classes of errors: compile errors, run-time errors, design and logical errors.
a) Design errors
This error consist of syntax errors that occur when you mistype a statement. The statement MsgBox (“Simple Error” , produces a syntax error because you have omitted the closing parenthesis.
b) Compile errors
Compiling is the process of converting or translating the VBA code into a format that the computer can understand. This compiling process happened very fast and you are typically unaware of it happening.
If any errors occur during the compile process, an error message pops up, and the VBE will highlights the location of the error.
c) Runtime errors
This error occurs when your code executes. VBE will displays a message box informing you of the error. A common example will be the TYPE MISMATCH error when you pass the wrong data type value to a variable. If you pass a string to an expression that expects a numeric value, a runtime error occurs.
Generally to avoid your program stop due to runtime error you need to use the On Error Resume Next statement at the beginning of your procedure. Try not to do this as the program still run although there are errors. The bottom line is you still need to handle the errors.
I will discuss more on this error later.
d) Logical errors
A logical error do not produce any type of error message. Simple logical errors include mistyping a value, placing a decimal point in the wrong place.
2) Trapping and handling errors
The primary goal of error handling is to write code that avoids displaying Excel’s error messages as much as possible. You need to anticipate potential errors and deal with them.
So, how do we handle and debug the errors. Let me show you a few methods.
a) Insert break points in a subroutine
You can insert break points in a procedure to stop execution at the specified line of code. This way, you can determine whether a procedure run correctly up to a specified location. To do this:
- activate the VBE and double click on a module to bring up the Code Window
- click on the right side of the Code Window. The break points will be highlight red (Figure 7.1) with a red circle on the right.
- You can clear a break point by clicking it again.
- Remember to clear all break points after you complete debugging your code.
After the break points are set, the procedure executes until it reaches the specified break point, highlight the line and stop the execution. All the variables will be updated. (see Figure 7.2) When you place your cursor on top of the variable, a pop up statement will tell the value. ( a yellow box )
You can continue running your code until it encounter another break point or end the procedure. You can change the code as needed to correct the error.
b) Step through a procedure
You can debug your procedure by stepping through the execution of the code one line at a time. Unlike break points, you trace the codeline by line by clicking the Step Into button. How do you do this?
- First click inside the procedure that you want to execute
- Then click on the Step Into button once. ( see Figure 7.3 )
- The line of code that was highlighted is the active line. ( see Figure 7.4 )
- Click on the Step Out option if you want to exit the procedure
Fig 7.3 - Click for larger image
Fig 7.4 - Click for larger imageAs you step into the current procedure, VBE will selects and highlight the first line of code in the procedure i.e. the Sub or Function statement. Clicking on the Step Into option will execute the next line. The Local Window below (see Figure 7.4) updates the values of the local variables each time there is a value change. ( this is also the same scenario if you use the break points method described above).
Thus you can change what is necessary to fix the error. This error trapping method is very useful if you want to amend any logical error.
3) A further discussion on runtime errors
To get things started, I developed a short VBA macro. Activate the VBE, insert a module, and enter the following code:
Sub ErrorExample_1()
Dim Num As Integer
‘ Prompt for a value
Num = InputBox(“Enter a value”)
‘ Insert the square root
ActiveCell.Value = Sqr(Num)
End Sub
Fig 7.4a - Click for larger image
This procedure asks the user for a value. It then enters the square root of that value into the active cell.
You have enter “1q” which is wrong. The message Run-Time ‘13’ will be display (Fig 7.4b). If you click on the “Debug” button, VBA suspends the macro so you can use the debugging tools. VBA will show you the line of code where the error occur, the highlighted line of code shown in Figure 7.5.
Fig 7.4b - Click for larger image
Fig 7.5 - Click for larger image
To improve the procedure, you have to anticipate this error and handle it more gracefully.
Here’s a modified version of ErrorExample_1()
Sub ErrorExample_1()
Dim Num As Integer
‘ Prompt for a value
Num = InputBox(“Enter a value”)
‘ Make sure the number is nonnegative
If Num <>
MsgBox “You must enter a positive number.”
Exit Sub
End If
‘ Insert the square root
ActiveCell.Value = Sqr(Num)
End Sub
I use an If-Then structure checks the value contained in the Num variable. If the user enter a value other than an integer, the procedure displays a message box containing information that you can actually understand. The procedure ends with the Exit Sub statement, so the error never has a chance to occur.
4) Using the On Error statement
a) On Error Resume Next
The procedure below will demonstrate how this On Error Resume Next work. Insert a module and enter the following code.
Sub ErrorExample_2()
On Error Resume Next
Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
intA = Range("A1").Value
intB = Range("A2").Value
intC = Range("A3").Value
total = intA + intB + intC
MsgBox ("Total is " & total)
End Sub
Fig 7.6 - Click for larger image
When you run the procedure it will produce…
As you can see in Figure 7.6, the value in Range(“A3”) is not an integer but the procedure continue to execute because the On Error Resume Next is entered at the top of the program. If there is no such statement there a runtime error Type Mismatch error will occurs as Range(“A3”) is not an integer and nonnumeric.
With this statement, VBA skip any runtime error that occur during the execution of the procedure and an error message does not inadvertently display on the screen. However, an error still exists in the code and therefore the procedure does not produce the appropriate results.
This statement is very useful if you intend to develop program to be distributed to another user. It ensure that the program that you code does not stop abruptly due to an error encounter in the code.
There are error trapping methods to handle any potential errors where you can inform the user anytime that an error has occur. If you want to execute specific code when an error is encountered, you can modify the On Error statement to be On Error GoTo label
b) On Error GoTo label
With this statement the procedure will jump to the label section of the code whenever an error condition is encountered. Typically this label code is placed at the end of the procedure. You need to enter an Exit Sub statement prior to the labeled section to keep the procedure from executing the code within the label if an error is not encountered.
Let’s look at an example. Modify the example above to this code:
Sub ErrorExample_3()
On Error GoTo errorHandler
Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
intA = Range("A1").Value
intB = Range("A2").Value
intC = Range("A3").Value
total = intA + intB + intC
MsgBox ("Total is " & total)
Exit Sub
errorHandler:
MsgBox ("VBA skip the line of code containing a runtime error")
Resume Next
End Sub
In this example, the program jump to the errorHandler part of the code (see Figure 7.7) as error occurs and display the message box in Figure 7.8. Click the Ok button and the procedure will finish executing the code. Note: don’t forget to put a colon after the errorHandler label.
Fig 7.8 - Click for larger image
As promised above, I will further explain to you how to handle a runtime error. As you can see in procedure ErrorExample_1, a runtime error 13 “Type Mismatch” has occurred.
This error is capture by Excel VBA in the Err object. You can use this information to process the error and often correct the error situation. The number property in this Err object i.e. Err.Number enable us to design an appropriate error processing code to react differently depending upon the specific runtime error encountered.
We have the Run-time error 13 in the procedure i.e. the value passed to the variable intC is not the correct data type. A string was passed instead of an integer.
Enter the code below in a module:
Sub ErrorExample_4()
On Error GoTo errorHandler
Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
intA = Range("A1").Value
intB = Range("A2").Value
intC = Range("A3").Value
total = intA + intB + intC
MsgBox ("Total is " & total)
Exit Sub
errorHandler:
Select Case Err.Number
Case 13
MsgBox ("Make sure an integer is entered")
Case Else
Resume Next
End Select
End Sub
If runtime error 13 is encounter in this procedure, then the message box in Figure 7.9 will be displayed to the user and when user click the OK button, the procedure will end.
Fig 7.9- Click for larger image
This is only one run time error example. There are many others but the most common one is shown in Fig 7.9a below;
Fig 7.9a- Click for larger image
When you use an On Error statement in your VBA code, Excel will bypass its built-in error handling and use your own error-handling code. In the previous example, a run-time error causes macro execution to jump to the statement labeled errorHandler. As a result, you prevent Excel’s unfriendly error messages and you can display your own friendlier message to the user like using the Err.Number statement in the procedure ErrorExample_4.
Generally, you can use the On Error statement in three ways, (Fig 7.9b)
Fig 7.9b- Click for larger image
Sometimes its alright to ignore errors. In fact we treat them as “friendly error”. In the example below, we need to divide the value in the Range(“A1:A6”) with another value
Sub ErrorExample_5()
On Error Resume Next
Dim totalSales As Integer
Dim branchSales As Single
Dim sales As Range
Set sales = Range("A1:A6")
totalSales = 100
For Each cell In sales
branchSales = totalSales / cell.Value
Next cell
End Sub
Fig 7.10- Click for larger image
If the On Error Resume Next is not used, then there will be a runtime error 11, “Division By Zero” as you can see Range(“A5”) has zero sales in Figure 7.10. But the procedure continue to run ignoring this run time error as the On Error Resume Next statement already entered.
5) Summary
An error-handling routine has the following characteristics:
- It starts immediately after the label specified in the On Error statement.
- You must use a statement such as Exit Sub or Exit Function immediately before the label because this error handling code will only be execute if an error occurs.
- A Resume statement is use if you need to continue with the main code even though an error has occurred.
Error handling can be a tricky proposition, after all we are only human that we can’t anticipate them all.
No comments:
Post a Comment