VBA On Error Statement – Handling Errors in Excel Macros

While writing Excel Macros we generally put more focus on the coding part and getting the desired result but during this process we forget an important thing i.e. Error handling. Error handling is an important part of every code and VBA On Error Statement is an easy way for handling unexpected exceptions in Excel Macros.

A well written macro is one that includes proper exception handling routines to catch and tackle every possible error. Error handling is important because in case of any unexpected exceptions your code doesn’t break. Even if any fatal unexpected error occurs in the code then also you should ensure that the code should terminate gracefully.

VBA On Error

Definition of VBA On Error Statement:

On Error statement instructs VBA Compiler, what to do in case any runtime exception are thrown.

Syntax of On Error Statement:

Basically there are three types of On Error statement:

  1. On Error Goto 0
  2. On Error Resume Next
  3. On Error Goto<label>:

1. On Error Goto 0

This is also called VBA default exception handling. When On Error Goto 0 is in effect, it is same as having no error handler in the code. Here we are instructing the program to display the standard runtime message box with ‘Continue’, ‘End’, ‘Debug’ and ‘Help’ buttons.

Standard-Runtime-Error-Msgbox

This message box will give you four options:

a. Continue: This will ignore the exception and continue the code, only if it is possible to do so.

b. End: This will terminate the program.

c. Debug: This option will bring the program control back to the statement from where the exception has occurred. This helps you to debug the code.

d. Help: This button will open Microsoft MSDN help pages for that exception.

2. On Error Resume Next

It is the second form of On Error statement. This statement tells the VBA program to ignore the error and resume the execution with the next line of code.

On Error Resume Next statement doesn’t fix the runtime errors but it simply means that program execution will continue from the line following the line that caused the error. However, it is the sole responsibility of the programmer to make sure that any handled error should not have any side effects (like uninitialized variables or null objects) on the program execution.

This can be ensured by using VBA Err object. Err object in VBA comes into picture whenever any runtime error occur. The Err object preserves information about one exception at a time. When an exception occurs, the Err object is updated to include information about that exception.

For instance:

For example I have a simple macro as follows:

Sub GetErr()
On Error Resume Next
N = 1 / 0    ' Line causing divide by zero exception
For i = 1 To N
'SomeSet of Statements
Next i
End Sub

Now as you can clearly see, that in this macro Line 3 causes an exception. But as we are using On Error Resume Next statement so this line will be skipped and the control will flow to the next statement. But the next statement is a loop which is depended on the value of ‘N’, and at this step ‘N’ is uninitialized so this will have a side effect on the whole flow of the program.

Now, have a look at the same program after exception handling:

Sub GetErr()
On Error Resume Next
N = 1 / 0    ' Line causing divide by zero exception
If Err.Number <> 0 Then
N = 2      ' Some minimum value of N if there is some exception in the code.
End If
For i = 1 To N
'SomeSet of Statements
Next i
End Sub

Now, here in this code we are checking the Err.Number property, if it is not equal to zero that means there is some exception in the code. And hence we have set ‘N’ to its minimum value so that there are no side effects in the code due to uninitialized variables.

3. On Error Goto<label>:

This is the third form in which VBA On Error statement can be used. This statement tells the VBA to transfer the program control to the line followed by the label, in case any runtime errors are encountered. In such cases all the statements between the exception line and the label will not be executed.

This method is more suitable for exiting the program gracefully if any fatal error occurs during the execution.

Example:

Below is a self-explanatory example of ‘On Error Goto<label>:’ where I have used the label name as ‘Error_handler’.

Sub GetErr()
On Error GoToError_handler:
N = 1 / 0    ' cause an error
MsgBox "This line will not be executed"
Exit Sub
Error_handler:
MsgBox "exception handler"
End Sub

In this code as soon as the exception occurs at Line 3, the program transfers the control to the Line 6.

Notice that here I have used ‘Exit Sub’ just before the ‘Error_handler:’ label, this is done to ensure that the Error handler block of code doesn’t execute if there is no error. If you omit the ‘Exit Sub’ statement then the Error handler code block will always execute even if no exception is encountered.

So, this was all about On Error statement in Excel VBA.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About