Excel VBA GoTo Statement
VBA GoTo statement is used when an error occurs while running the code rather than displaying error to resume the next line of code by ignoring the error message. There are two kinds of GOTO statements in Excel VBA. One is to select any range of the worksheet in the specified workbook and another one is error handler.
In order to overcome the anticipated errors in VBA, we have function called “GOTO”. We will see both kinds of GoTo statements in this article.
2 Ways to use GoTo Method
#1 – Application.GoTo Method
If you want to go to the specific workbook or specific worksheet in all the opened workbooks then we can use the statement Application.GoTo method.
Let’s look at the syntax of Application.GoTo method
- [Reference]: This is nothing but a specified cell reference. If the reference is not provided by default it will take you to the last used cell range.
- [Scroll]: This a logical statement of TRUE or FALSE. If the value is TRUE it will scroll through the window, if the value if FALSE it will not scroll through the window.
If you want to go to a specific cell in the specific worksheet we can use Goto method. I have 3 sheets named Jan, Feb, and Mar.
Now if I want to go to cell C5 in Jan sheet I will use below set of code.
Step 1: Start the macro name.
Sub GoTo_Example1() End Sub
Step 2: Start the method “Application.GoTo”
Step 3: In the reference argument we need to specify the worksheet name and in that worksheet, we need to mention the specific cell.
Sub GoTo_Example1() Application.Goto Reference:=Worksheets("Jan").Range("C5") End Sub
Step 4: Mention the scroll as TRUE.
Sub GoTo_Example1() Application.Goto Reference:=Worksheets("Jan").Range("C5"),Scroll:=True End Sub
Step 5: Now run this code using the F5 key or you can also run this code manually, it will take you to the specified sheet and specified cell.
Now I will change the scroll argument to FALSE and see the change it will encounter.
Sub GoTo_Example1() Application.Goto Reference:=Worksheets("Jan").Range("C5"), Scroll:=False End Sub
If you want to go to specific workbook then you need to mention the workbook name as well before the workbook name.
Sub GoTo_Example1() Application.Goto Reference:=Workbooks("Book1.xlsx").Worksheets("Jan").Range("C5"), Scroll:=False End Sub
#2 – Error Handler Method
When the particular line of code encounters an error then VBA stops executing the rest of the code and shows the error message.
For an example look at the below line of code.
Sub GoTo_Example2() Sheets("April").Delete Sheets.Add End Sub
Above code says to delete the sheet April and add a new sheet. In the active workbook if there is any sheet name called April it will delete or else it will show the below error message dialogue box.
When I run this code my workbook didn’t have a sheet called April, so VBA code couldn’t find the sheet name and throws the error. Sometimes we need to ignore this error because often times if there is no sheet name called April then we need to go on with the other lines of code.
To eliminate this error we can use GoTo method as an error handler.
Now I will modify the code as On error go to next line
Sub GoTo_Example2() On Error GoTo NextLine Sheets("April").Delete NextLine: Sheets.Add End Sub
Now if you run this it will add the new sheet even though there is no sheet name called April.
The statement “On Error GoTo NextLine” understands that if any error occurs it will jump to the next line and in the next line VBA code is to add a new sheet.
Things to Remember
- If you want to jump to the next line when the error occurs you can also use “On Error Resume Next” statement.
- In order to jump to next, you need to be sure that that particular line of code is expected an error.
- If the important line of the code skips with this error handler then your task may not be finished perfectly.
This has been a guide to VBA GoTo. Here we learn how to use GoTo statement in excel VBA using Application.GoTo and Error Handler Method along with examples. Below are some useful excel articles related to VBA –