Error Handling in Excel VBA

Error handling in VBA (and any programming language)  is anticipating an error and taking the right steps to make sure this error is dealt with before moving on to the next bit of code, to avoid the macro stopping abruptly.

There is a different kind of errors in VBA, such as:

Compile errors – This type of errors will not allow the code to compile at all

Run-time errors – The code will compile but it will stop due to an unexpected error in the code

We will only be looking at Run-Time errors here and how to handle such errors.

There are different ways of handling this errors and we will see a few examples on how to do this here.

VBA On Error GoTo 0

This statement will prompt VBA to display its default Error message and it will disable any other error handling you might have in place.

Let’s see an example:

Please declare 2 integer variables as below, and let’s perform a simple division with a 0 number.

Run this code and you will see that excel will prompt this error message:

Run-time error `11`: Division by 0

Now let’s add the On Error GoTo 0 statement to the code:

Rerun the code and you will notice that the same error message as above will be displayed.

VBA On Error Resume Next

This statement will skip the code that is causing an error and execute the line of code straight after the error.

This is one of the most misused error statements in VBA as skipping an error in the code does not solve the error itself, merely skips to the next line of code.

This excel On Error Resume Next statement is normally used together with the VBA Err object. This object will hold any information related to an error occurring during Run-time (Note: it will only hold information about 1 error at a time).

We can use this object with a VBA IF statement and handle the error inside this statement.

Let’s see an example:

We will assign the value of an input box to an Integer variable and enter this value in Cell B2 of our active sheet:

Run the code above and enter a text value in the window that will pop up (since we are assigning this value to an Integer variable, we are expecting the code to display an error). We will see the following error:

To avoid this we will use the VBA On Error Resume Next statement together with the Err object.

First, we will add the On Error Resume Next statement just before we assign the input box value to integer i as follows:


Then we will add an If statement to tell VBA what to do if this error occurs:

So if the Err.Number (the error number that this Object is holding) is anything but 0, then we know that some kind of error occurred, in that case, we want the integer i value to be 2.

Please run the above macro and enter a text value again, you will see that no error message will be displayed and the value in cell B2 will be 2:

If you enter a number value, there will be no error and the If condition will not run as the value of the Err.Number will be 0.

This code seems to work, but there is a hidden issue here:

We have now set the default error handling to Resume Next, meaning any error that might happen after this piece of code will be skipped and we will not be notified. It is very important that after we have handled our error, we add the On Error GoTo 0 statement.

If we continue with our example and try and do a division with 0 as follows:

9.png

When you run this code, you will see that no error messages will be displayed warning us about the 0 division, the macro will go to the end of the routine and skip this division completely, which is an unwanted result of using the On Error Resume Next statement.

To avoid this, after we have handled our error, we will add the On Error resume 0 statement as follows:

0.png

If you run the above code you will see the following:

  • A value entered in cell B2 (the number you entered in the input box, or 2 if the value that you entered is anything other than a number)

  • The following error message

And this is the result that we want and expect.

VBA On Error GoTo <label>

This statement is mostly used to exit the macro when an error has occurred.

In the example above, if we want the macro to stop if the user inputs a text value instead of a numerical one we would do the following:


We have added a VBA On Error GoTo <value> statement.

The <Value> part of this statement can be anything. It is good practice to write something that explains what you are trying to do.

In our example, I am telling VBA that if an error occurs in this line of code, move to the line named: exitMacro.

The macro will skip the next step (which is ActiveSheet.Range("B2").Value = i ) and go straight to the line exitMacro: and execute the block of code under it, which is :display a message and end the sub.

Let’s try and run this code and in the input box enter a text value.

You will see this message displayed on the screen and no value has been entered in cell B2 as this piece of code was never executed:

Now let’s run the code again and enter the number 5 in the input box.

You will see that cell B2 will be updated with the value 5, but we will still get the message under the exitMacro.

This is because when there is no error, VBA will follow the code step by step:

-First will ask for an input value and assign it to the integer i

-Then it will place this value in cell B2

- The next line of code is:

So it will execute this code. This block of code will not only run when there is an error above but every time we run the macro.

To avoid this, we need to add an Exit Sub statement, just before the exitMacro value.

So if there is an error, the code will jump straight to the exitMacro value, run the code under it and end execution.

If there is no error, the macro will run every line, and exit the routine when it reaches Exit Sub.

If you would like to read more about this topic, check out the Microsoft guide to using it here:

On Error Statement (Visual Basic) | Microsoft Docs