[Lesson 13]<<[Table of Contents]>>[Lesson 15]
14.1 Introduction to Error Handling
Errors often occur when the user
Another example is the user might enter a text (string) to a box that is designed to handle only numeric values, the computer will not be able to perform an arithmetic calculation for text, therefore, will create an error. These errors are known as synchronous errors. Writing errors handling code should be considered a good practice for Excel 2010 VBA programmers, so do not try to finish a program fast by omitting the errors handling code. However, there should not be too many errors handling code in the program as it creates problems for the programmer to maintain and troubleshoot the program later.
14.2 Writing the Errors Handling Code
We shall now learn how to write errors handling code in Visual Basic. The syntax for errors handling is
On Error GoTo program_label
where program_label is the section of code that is designed by the programmer to handle the error committed by the user. Once an error is detected, the program will jump to the program_label section for error handling. You also need to add the statement Exit Sub to prevent the program from jumping to error handling section even though the inputs were correct.
Example 14.1
Private Sub CommandButton1_Click()
On Error GoTo err_handler
num1 = InputBox("Enter first number")
num2 = InputBox("Enter second number")
MsgBox num1 / num2
Exit Sub
err_handler:
MsgBox "Invalid division, please try again"
End Sub
The program will display the error message “Invalid division, please try again” if the user enter letters instead of numbers or enter the second number as zero, as shown below:
Example 14.2: Nested Error Handling Procedure
By referring to Example 14.1, it is better to alert the user the types of error he or she has committed, such as entering non-numeric data like letters or enter zero as denominator. It should be placed in the first place as soon as the user input something in the input box. And the error handler label error_handler1 for this error should be placed after the error_handler2 label. This means the second error handling procedure is nested within the first error handling procedure. Notice that you have to put an Exit Sub for the second error handling procedure to prevent to execute the first error handling procedure again. The code is as follow:
Private Sub CommandButton2_Click()
Dim firstNum, secondNum As Double
On Error GoTo error_handler1
firstNum = InputBox("Enter first number")
secondNum = InputBox("Enter second number")
On Error GoTo error_handler2
M sgBox firstNum / secondNum
Exit Sub ‘To prevent error handling even the inputs are valid
error_handler2:
MsgBox " Error!You attempt to divide a number by zero!Try again!"
Exit Sub
error_handler1:
MsgBox " You are not entering a number! Try again!"
End Sub
The errors are shown in message boxes below:
Finally, you can use the keyword Resume Next to prevent error message from appearing and branch back to the section of the program where error occured.
Private Sub CommandButton1_Click()
On Error Resume Next
num1 = InputBox("Enter first number")
num2 = InputBox("Enter second number")
MsgBox num1 / num2
End Sub
