VBA Error Handling

VBA code is generally error-prone because even the most careful programmer can commit syntax or logic mistakes. Errors and mistakes are common because code can never be perfect. The Error Handling Standard is a formalised approach to coding that allows us to track and fix software defects. It’s a method for anticipating mistake conditions and determining how best to respond automatically, avoiding a system failure.

Types or Error

There are three common forms of VBA programming errors. They are:

(a) Syntax Errors

(b) Compile Errors

(c) Runtime Errors

(d) Logical Errors.

Syntax Errors

Syntax errors (compile errors) occur at the interpretation time for VBScript. In the following code, for instance, we get a syntax error because we spelt the name of an object wrong (workshee instead of worksheets).

Examle:

VBA will show a Syntax error when you run the above code.

Compile Errors

Compile errors arise when in the code something got missing that is essential for the macro to operate. When dealing with VBA, there are often cases where the syntax is proper on a single line of code, but a mistake appears when the entire project’s code is examined. The following are some frequent causes of compilation errors:

• Using the For loop without the Next statement
• Using the Select statement in your macro without the End Select statement
• Defining a Sub or Function that doesn’t exist in your programme
• Calling a Sub or Function with the incorrect parameters
• Option Explicit is at the top of your code, but variables are not declared at the top.

Example: Using If without End IF

VBA will show a compile error when you run the above code.

Runtime Errors

Exceptions and runtime errors are the same things. These errors happen when the code is being run after it has been interpreted.

Example 1:

When you try to run the above macro, you’ll get a runtime error because the syntax is correct, but variable length is exceed as Integer length is between (-32,768 to 32,767).

Example 2:

VBA will show a Run-Time error when you run the above code.

Logical Errors

The most challenging error to fix is a logical error, as even a single misstep can change the entire outcome. In addition, logical mistakes are difficult to identify because their location is tied directly to your company’s reasoning. These aren’t the result of a syntax or runtime error like the others. Instead, they occur when the user modifies the script in some way that breaks the programme logic and causes the user to receive unexpected results.

Example :

VBA will show a Logical error when you run the above code. As the value is divided by zero.

What is VBA Error Handling

Error Handling is how you deal with different programming errors when your code runs. Handling errors will make your code look more professional and ready to handle errors. It is recommended to employ error handling techniques whenever there is any possibility of an error occurring.

Merits of Error Handling

• Error Handling can check for logical, runtime, and syntax errors.
• If there is an error, it will automatically skip the rest of the code, let you know, or do something else.
• It can tell the user about a mistake in the code or let them know about it.

Methods for Error Handling

Error Handling can be done in the following ways:

• Go To Line
• Go To 0
• Go To -1
• Resume Next

Go To Line: The error-handling method starts the error-handling routine at the line in the given line argument. If there is an error at compile time or run time, it moves the programme flow to the line specified in the same method.

Example:

Sub VBA_Error_Handling_Go_To_Line()
Dim x As Integer, y As Integer
x = 50
y = 0
If Err.Number = 0 Then
GoTo ExitCode:
Else
Value = x / y
End If
MsgBox Value
ExitCode:
End Sub

Go To 0: It turns off the current procedure’s error handler, sets it to “Nothing,” and shows a message box that explains the error.

Example:

Sub VBA_Error_Handling_Go_To_Zero()
Dim x As Integer, y As Integer
Dim i As Integer, j As Integer
x = 50
y = 0
i = 40
j = 0

On Error Resume Next

Value = x / y
MsgBox Value
On Error GoTo 0

Value2 = i / j
MsgBox Value2
End Sub

Go To -1: The error-handling function disables the existing exception in the current operation, clears the error, and resets it to nil, allowing the user to create a second error trap.

Example:

Sub VBA_Error_Handling_Go_To_Minus_1()
Dim x As Integer, y As Integer
Dim i As Integer, j As Integer

x = 50
y = 0
i = 40
j = 0

On Error Resume Next
Value = x / y
MsgBox Value
On Error GoTo -1
Value2 = i / j
MsgBox Value2
End Sub

Resume Next: This error-handling method says that the error will be ignored whenever a run-time error happens. The control will move to the statement right after the one where the error occurred, and execution will continue from there.

Example:

Sub VBA_Error_Handling_Resume_Next()
Dim x As Integer, y As Integer

x = 50
y = 0

On Error Resume Next
Value = x / y
MsgBox Value
End Sub