spot_img

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

  1. Error Handling can check all types of errors i.e logical, run-time and syntax.
  2. If an error occurs, it will automatically skip the rest of the code and give you a notification or perform other steps.
  3. 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

VBA Error Handling

Output

Run the code by pressing the F5 key and fetch the output. As a result, it will return the compile error.

VBA Error Handling

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

VBA Error Handling

Output

Run the code by pressing the F5 key and fetch the output. As a result, it will custom message.

VBA Error Handling

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

VBA Error Handling

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.

VBA Error Handling

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

VBA Error Handling

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.

VBA Error Handling

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.

VBA Error Handling

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.

VBA Error Handling

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.

VBA Error Handling

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.

VBA Error Handling


Next TopicVBA Instr Function

spot_img
Previous articleVBA Data Types
Next articleCircular reference in Excel