Error Handling - VBA

Types of error

Syntax errors

Syntax errors, also called as parsing errors, occur at the interpretation time of the Script. For example, line causes a syntax error because it is missing a closing parenthesis or misspelling a function name.

Runtime errors

Runtime errors, also called exceptions, occur during execution, after interpretation.

For example, line causes a runtime error because our syntax is correct but at runtime it is trying to call function, which is a non-existing function. Or opening a file from a specific location but file doesn’t exist. At error came that time are caller runtime error

Logical Errors

Logical errors can be the most difficult error type to trace down. These errors do not occur because result of a syntax or runtime error. Instead, they occur when you make a mistake in the logic that drives your script and you do not get the result you expected.

You cannot catch those errors, because it depends on your business requirement what type of logic you want to put in your program.

On Error statement

Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine. 

As we have seen there are two ways to treat runtime errors 

• Expected errors – write specific code to handle them.
• Unexpected errors – use VBA error handling statements to handle them.

Syntax

On Error GoTo line On Error Resume Next On Error GoTo 0

The VBA On Error statement is used for error handling. This statement performs some action when an error occurs during runtime.

There are four different ways to use this statement

1. On Error GoTo 0 – the code stops at the line with the error and displays a message.
2. On Error Resume Next – the code moves to next line. No error message is displayed.
3. On Error GoTo [label] – the code moves to a specific line or label. No error message is displayed. This is the one we use for error handling.
4. On Error GoTo -1 – clears the current error.

On Error GoTo 0 example

Sub error_handling_demo() Dim x, y As Integer Dim z As Double x = 7 y = 0 On Error GoTo 0: z = x / y Debug.Print " hello"     End Sub

Result

as you can see on error goto 0, stops the execution and prompt the error and description of the error, we are printing "hello" just to check if it continues the execution or not 

On Error Resume Next Example

Sub error_handling_demo() Dim x, y As Integer Dim z As Double x = 7 y = 0 On Error Resume Next: z = x / y Debug.Print " Error has been skipped "     End Sub

Result

as you can see even after dividing 7 by 0 program did not throw any error but executed from the next line. that mean resume next continues after the line which have error.

On Error GoTo [label] Example

Sub error_handling_demo() Dim x, y As Integer Dim z As Double x = 7 y = 0 On Error GoTo errorresolved: ' error resolves is lable name z = x / y Debug.Print " next line after the error" errorresolved: Debug.Print " Error has been catched  "     End Sub

Result

as you can see here print part which was after the line which is causing the error did not got print instead it went to the label errorresolved and print the statement written under the label

Note- all the error handling label should be define before the ending of the function or procedure