Excel VBA 2010 Lesson 14: Errors Handling


 [Lesson 13][Table of Contents][Lesson 15]

14.1 Introduction to Error Handling

Errors often occur due to incorrect input from the user. For example, the user might make the mistake of attempting to ask the computer to divide a number by zero . Another example is the user might enter a text (string) to a box that is designed to handle only numeric values such as the weight of a person, the computer will not be able to perform 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 VBA 2010 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 create 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.1Private Sub CommandButton1_Click()
On Error GoTo err_handler
num1 = InputBox(“Enter first number”)
num2 = InputBox(“Enter second number”)
MsgBox num1 / num2
Exit Sub

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.2Nested 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
MsgBox firstNum / secondNum
Exit Sub ‘To prevent errror handling even the inputs are valid


MsgBox ” Error!You attempt to divide a number by zero!Try again!”
Exit Sub
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

  [Lesson 13][Table of Contents][Lesson 15]