Lesson 14 Errors Handling

14.1 Introduction To Errors Handling

Errors often occur when the user enter incorrect values into a cell of an Excel spreasheet . For example, an error occurs when instruct 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, 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 VBA 365 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.

Fortunately, we can write Excel VBA 365 macro to handle those errors efficiently.

14.2 Writing The Errors Handling Code

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 Errors Handling

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 error handling when 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

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