Excel VBA On Error Goto
Whenever we use any macro where there are multiple conditions to be run. And at a certain point when the conditions are not satisfied we end up getting the error. Such kind of thing may happen often when there are multiple iterations in the code itself. To avoid such happening, we can have On Error GoTo along with the message. The On Error Goto in Excel VBA function helps us to complete the entire code. And if there is any break in the iteration then we will get the error message, but the rest of the lines will get executed.
To understand it better, suppose we want to rename 3 sheets. But the file has only 2 sheets visible. Using VBA On Error Goto will enable us to change the name of those sheets at least which are visible to execute the maximum possible portion of code.
How to Use VBA On Error Goto?
It is very simple to use VBA On Error Goto. Suppose, if we are expecting that we may end up getting the error while running the code. So, just write On Error followed by Next or Error Message as shown below.
Below are the different examples of On Error Goto in Excel VBA:
Example #1 – VBA On Error Goto
In this example, we will see what happens when the code we run gives the error and how to resolve it. In this example, we will write a module to print the text on different sheets whereas we are just opening only one sheet. For this, follow the below steps:
Step 1: Open a VBA Module where we will be writing our code from Insert menu tab as shown below.
Step 2: Write the subprocedure to define the code structure in any name.
Code:
Sub VBA_OnError() End Sub
Step 3: Now select the first worksheet with its name. Here it is named “Sheet1”.
Code:
Sub VBA_OnError() Worksheets("Sheet1").Select End Sub
Step 4: Now choose a text which we want to print. We chose cell A1 with a sample text Test.
Code:
Sub VBA_OnError() Worksheets("Sheet1").Select Range("A1").Value = "Test" End Sub
Step 5: Do the same process for selecting the next sheet which can be named as “Sheet2” with the same or any other text.
Code:
Sub VBA_OnError() Worksheets("Sheet1").Select Range("A1").Value = "Test" Worksheets("Sheet2").Select Range("A1").Value = "Test" End Sub
Step 6: Run the code by pressing the F5 key or by clicking on the Play Button. We got the error message as Subscript is out of range. This normally happens when we choose the range which is not there or if we select the incorrect range.
Step 7: But what if we include a line of code, by which if error comes, the code should move to the next possible step which could be executed. Here we will insert a line of code, ‘On Error Resume Next‘ as shown below.
Code:
Sub VBA_OnError2() On Error Resume Next Worksheets("Sheet1").Select Range("A1").Value = "Test" Worksheets("Sheet2").Select Range("A1").Value = "Test" End Sub
Step 8: Again we Run the code by pressing the F5 key. We will see the code is successfully executed and Sheet1 which is the only worksheet opened has got the text as Test in cell A1.
Example #2 – VBA On Error Goto
Let’s see another type of On Error Goto example. For this, follow the below steps:
In this example, we will see what if we write some set of code and the code does not find the portion to get executed. In that, we will again end up getting the error.
Step 1: Write the subprocedure to define the code structure in any name.
Code:
Sub VBA_OnError3() End Sub
Step 2: And after running the code, we get Run-Time error ‘9’ which means the range we have selected is incorrect.
Code:
Sub VBA_OnError3() Worksheets("Sheet1").Select Range("A1").Value = "Test" Worksheets("Sheet2").Select Range("A1").Value = "Test" End Sub
Step 3: Now to avoid this, we can insert the On Error Goto Error Message line of code just after subprocedure as shown below.
Step 4: This code will allow us to get an error message whenever the error happens. And we can choose the message which we want to see. Once we finish writing our code, at the end of code, write ErrorMessage with a colon as shown below.
Code:
Sub VBA_OnError3() On Error GoTo ErrorMessage Worksheets("Sheet1").Select Range("A1").Value = "Test" Worksheets("Sheet2").Select Range("A1").Value = "Test" End Sub
Step 5: And after that insert the message box using MsgBox followed by the message we want to print.
Code:
Sub VBA_OnError3() On Error GoTo ErrorMessage Worksheets("Sheet1").Select Range("A1").Value = "Test" Worksheets("Sheet2").Select Range("A1").Value = "Test" ErrorMessage: End Sub
Step 6: Now compile the code and run by pressing the F8 key or by clicking on the Play button.
Code:
Sub VBA_OnError3() On Error GoTo ErrorMessage Worksheets("Sheet1").Select Range("A1").Value = "Test" Worksheets("Sheet2").Select Range("A1").Value = "Test" ErrorMessage: MsgBox "Exiting On Error!" End Sub
Step 7: We will see, our range cell which is A1 got the text printed as TEST in Sheet1. And as we did not have further sheets, so the error message we got “Exiting On Error!”.
This is one of the ways to execute VBA on the Error Goto function in VBA. There are a few more ways to perform the same process as well. Which also starts the line of code ”On Error”.
Pros of VBA On Error Goto:
- We can still completely execute our code, even if contains the possibility of giving error.
- Using On Error Goto, there various ways to jump off to the error portion and getting the output that we want.
Things to Remember
- If we are sure about which part of code will give us the error, we can still use the On Error Goto command link and get the output from the written code.
- VBA On Error Goto gives the error notification. So be sure what you want to see when the error message appears. Make sure that the message must be relevant to the operation that needs to happen through code.
- Above shown examples are the most often used On Error Goto types. There are few more On Error Goto command in VBA such as On Error Goto 0, On Error Goto-1, On Error Goto [Label], Error Function, Error Statement, etc.
- Once you are done with coding, always remember to save the file in Macro Enable Excel format so the Code is safe.
Recommended Articles
This is a guide to VBA On Error Goto. Here we discuss how to use On Error Goto in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
