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 one is to select any range of the worksheet in the specified workbook and another one is error handler.
To overcome the anticipated errors in VBA, we have a function called “GOTO.” We will see both kinds of GoTo statements in this article.
2 Ways to use GoTo Statement in VBA Code
#1 – Application.GoTo Method
If you want to go to the specific workbook or specific worksheet in all the opened workbooks, we can use the statement Application.GoTo method.
Let’s look at the syntax of the Application.GoTo method
- [Reference]: This is nothing but a specified cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.. 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 the Goto method. I have 3 sheets named Jan, Feb, and Mar.
If I want to go to cell C5 in the Jan sheet, I will use the below set of codes.
Step 1: Start the excel macroExcel MacroMacro in excel is a series of instructions in code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. 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 a 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 example, look at the below line of code.
Sub GoTo_Example2() Sheets("April").Delete Sheets.Add End Sub
The above code says to delete the sheet in 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 the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. couldn’t find the sheet name and throws the error. Sometimes we need to ignore this error because oftentimes, 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 the GoTo method as an error handler.
Now I will modify the code as On error goes to the next line.
Sub GoTo_Example2() On Error GoTo NextLine Sheets("April").Delete NextLine: Sheets.Add End Sub
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 the On Error Resume Next VBAOn Error Resume Next VBAVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error. statement.
- 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, your task may not be finished perfectly.
This has been a guide to VBA GoTo. Here we learn how to use the VBA GoTo statement using Application.GoTo and Error Handler Method along with examples and downloadable excel template. Below are some useful excel articles related to VBA –