VBA Error Handling
The VBA code is usually prone to errors, the developer can always make mistakes whether its syntaxial or logically. It is common to make errors and mistakes as the code can never be always flawless.
Error Handling Standard is a professional coding method by which we can account and adjust the errors in our code. It a technique to predict how the errors and finds the ways by which we can automatically react to them so that the procedure does not crash. It involves programing behavior to prevents the failure or properly report to it (either by informing the user or exiting the program) in case any failure occurs.
Before moving for ward firstly let’s understand what are errors and its types.
What is an error?
“Errors are defined as programming conditions that interrupts the flow of the code or creates a problem while running any programming.”
Types of Error
There are three types of errors that occurs in VBA programming: (a) Syntax Errors, (b) Compile Errors (c) Runtime Errors, and (d) Logical Errors.
Syntax errors
Syntax errors, also called as compile errors or parsing errors. They occur at the interpretation time for VBScript. For instance, the below program will throw a syntax error because it because we have misspelled the object ‘worksheets’ as ‘workshee’.
Program
Compile Errors
Compile errors occur when in the code something got missing that is required for the macro to run. Many times while working with VBA, the syntax is correct on a single line of the program, but there is a mistake when all the project code is taken into account.
Some common examples of compilation errors are:
- If statement in your macro without End If statement
- Using the For loop without the Next statement
- Select statement in your macro without End Select statement
- Defining a Sub or Function in your program that does not exist
- Calling a Sub or Function with the wrong parameters
- Option Explicit present at the beginning of your code and variables not declared at the top
Example 1: Using If without End IF
As soon as you will run the above code, VBA will throw a compile error. This happens as I have used the If Then statement without closing it with the required ‘End If’.
Runtime errors
Runtime errors, also known as exceptions. These error types occur during execution of the code, after interpretation.
Example 1: Demonstrate Runtime error
As soon as you will run the above macro, it will throw a runtime error because here the syntax is correct but at runtime it is trying to call fnmul and function fnmul doesn’t exist in our program.
Logical Errors
Logical errors are the most difficult type because one mistake can alter the entire result. Also, logical errors are very hard to track down because it depends upon your business logic. Unlike the others, these errors are not the result of a syntax or runtime error. Instead, they occur when the user makes an error in the program logic that alters the script, and the user does not get the desired output.
For instance, if you divide a number by zero or a script that is written which enters into infinite loop.
Example 1: Demonstrate the program for logical error
Code:
Err Object
Assume if we have a runtime error, then the execution stops by displaying the error message. As a developer, if we want to capture the error, then Error Object is used.
What is error Handling?
Error Handling is defined as the method to handle different programming errors that occurs when your code is running. The practice of error handling makes your code look more professional and error ready. At some point, if you think there are chances that an error might occur, it is good practice to use error handling technique to control.
Advantages of Error Handling
- Error Handling can check all types of errors i.e logical, run-time and syntax.
- If an error occurs, it will automatically skip the rest of the code and give you a notification or perform other steps.
- It can update or aware the user with the information of an error in the code.
Methods for Error Handling
Error Handling can be practiced by the following methods:
- Go To Line
- Go To 0
- Go To -1
- Resume Next
1. Go To line
This error-handling method enables the error-handling routine that starts at the line specified in the given line argument. If any compile time or run time error is encountered, it repositions the program flow to the line specified in the same procedure.
Example 1: Writing a code where sheet5 is not present in Excel worksheet
Output
Run the code by pressing the F5 key and fetch the output. As a result, it will return the compile error.
In the above macro, the Excel worksheet doesn’t contain Sheet5. Also, we have not handled those kinds of errors, and because of that, VBA had thrown an error.
In the next example, let’s handle the above macro. Whenever the system encounters an error, it will directly jump to the ‘jumpnext: statement’ and show the error message.
Example 2: Handle Error Using Go To Line
Output
Run the code by pressing the F5 key and fetch the output. As a result, it will custom message.
2. Go To 0
This error handling method disables the enabled error handler in the current procedure, resets it to Nothing and displays a message box that defines the error.
Example 1: Divide with string and display error using Go to 0
Output
Run the code by pressing the F5 key and fetch the output. As a result, it will return the window displaying the Type mismatch error.
3. Go to -1
This error handing method disables the existing exception in the current procedure, clears the error and resets it to nil, thereby enabling the end user to make another error trap.
Example 1: Display the error using Go To -1 error handling method
Output
Run the code by pressing the F5 key and fetch the output. As a result, it will return the window displaying the Type mismatch error.
4. Resume Next
This error handling method specifies that whenever their encounters a run-time, the resume next will ignore the error, the control flows to the statement immediately following the statement where the error occurred, and the execution moves on from that point.
Example 1: Encounter a logical error (dividing by 0) without using Resume Next.
Output
Run the code by pressing the F5 key and fetch the output. As a result, it will return the window displaying the Type mismatch error.
Using the Resume Next we can skip the error line because a VBA procedure starts with Sub and ends with End Sub and excel will run the statements between them. Let’s see in the next example what will happen if we run the above program using the Resume Next statement.
Example2: Encounter the logical error (dividing by 0) using Resume Next.
Output
Run the code by pressing the F5 key and fetch the output. As a result, it will skip the error line and move to the next line of code. Therefore, it has returned 0.
