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 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.
Fortunately, we can write Excel 2010 VBA macro to handle those errors efficiently.
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.
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:
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