VBA GoTo STATEMENT

What is Excel VBA GoTo Statement?

The GoTo statement in VBA jumps to a specific line within a procedure. It allows the code execution to skip to a particular line that the programmer specifies. The syntax for the GoTo statement is GoTo line, where the line argument can be a line label or a line number.

VBA GoTo statement has two arguments: reference, where the GoTo statement in VBA skips to the reference in the subroutine, and the scroll function, declared False by default. Consider the following example:

This example checks if the given number is one or not. If the number is 1, it skips to Line 1 using the VBA GoTo statement and prints “Number is 1,” which is again skipped to the last line. The output is printed in the immediate tab, as shown below.

2 Ways to Use GoTo Statement in Excel VBA

You can use many ways to implement the GoTo statement in Excel VBA. Here, we’ll discuss two main ways to use the GoTo statement in Excel VBA. They are:

1. Application.GoTo method
2. Error handler method

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#1 – Application.GoTo Method

The Application.GoTo method is used to move the cursor to a specific cell in the Excel worksheet and perform a variety of applications based on it.

Step 1: Consider the example below. Here, we begin a sub-procedure. We use the Application.GoTo method to move the cursor to a particular cell in a worksheet.

Step 2: Once you run the code, the cursor has moved to the specified worksheet.

#2 – Error Handler Method

In a subroutine, when you do not want the code running process to be halted by errors, the errors can be handled by skipping the part of the code which throws the error. For example,

On Error GoTo Next

This line tells Excel VBA to ignore the line causing errors and continue to the following line.

Note: Skipping lines of code when the next line is dependent on the previous line of code, that is, the error part, may cause unexpected results.

Step 1: Consider an example where you need to use Error Handling. Here, the sub-procedure takes the execution to the part “ErrorHandler:” when there is an error in the code, as shown below. 

Step 2: Run the code, and it will return the output, as shown below.

Examples

Below are some examples that show the usage of the GoTo Statement.

Example #1

Let us see an interesting GoTo Statement example. For example, you’d like to fill a selected range with a substring. The cell value can be selected with the GoTo statement, and the VBA MID function reference can find the substring. Here, we’re using the string: “Hello World.”

The VBA MID Function is used to split the string and print the substring across a given range in Excel using the Application.GoTo statement. We acquire the substring “World” from the statement above and print it in the cell “C7”. The steps to do this are shown below:

Step 1: Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Step 2: Declare a subroutine to print the substring in the given range and highlight a cell.

Step 3: Declare two string variables, one for the original string and the other to store the substring value.

Step 4: Initialize the string value with a string input.

Step 5: With the VBA MID Function reference, split the word “World” from the initial string.

Here, we split the string, starting at the 7th position and moving five places to create our substring. ‘W’ begins at the 7th position, and ‘d’ is at the 5th. Hence, the resultant substring is “World.”

Step 6: Print the substring value in the range defined.

Step 7: Using the GoTo statement, move to the cell with the substring value in it.

Here, the Scroll argument is given as True. GoTo statement has two arguments reference and scroll. The scroll is used to scroll through the worksheet till the stipulated range and displays it. It will scroll the Worksheet to show the exact cell. It is helpful to highlight specific cell values in an Excel Worksheet using the GoTo statement.

Code:

Sub GoToWithMID()
Dim myString As String
Dim subString As String
myString = “Hello World”
subString = Mid(myString, 7, 5)
Range(“C7”).Value = subString
Application.Goto Range(“C7”), scroll:=True
End Sub

Step 8: Run the above code by pressing F5 or the Run button above the Excel toolbar.

Step 9: View the Excel Worksheet.

Since we have declared the “Scroll” argument as True in the GoTo statement, the subroutine printed “World” in cell “C7” and scrolled to it, highlighting the cell value.

Example #2

Consider an example where the user needs a procedure to scroll to a given cell value but shouldn’t stop the code from running when an invalid input is entered. It can be done by utilizing the GoTo Statement. Let’s see how to do that by referring to the steps shown below:

Step 1: Initialize a sub-procedure to scroll and display the cell value.

Step 2: Define a GoTo statement to skip to the “ErrorHandler” line when the code throws an error.

The ErrorHandler line is defined below.

It prints the Error type in a message box and then clears it.

Step 3: Accept an input range from the user.

Step 4: Initialize the Range datatype and set the input range given by the user to the variable.

Step 5: Initialize an If-Else statement to check whether the given input by the user is empty, and print “Invalid cell address!” if the input is empty.

Step 6: Exit the sub-procedure if there are no errors in the code, if not, the ErrorHandler statement is executed.

Code:

Sub GoToPracticalExample()
On Error GoTo ErrorHandler
Dim userInput As String
userInput = InputBox(“Enter a cell address:”)
Dim rng As Range
Set rng = Range(userInput)
If Not rng Is Nothing Then
Application.Goto rng, scroll:=True
Else
MsgBox “Invalid cell address!”
End If
Exit Sub
ErrorHandler:
MsgBox “An error occurred: ” & Err.Description
Err.Clear
End Sub

Step 7: Run the above code by pressing F5 or the green arrow button on the Excel VBA toolbar.

Step 8: The output is as shown below.

If invalid input is given, as shown below, we get:

This is an example of how Error Handling using the GoTo statement is used in Excel VBA.

Important Things to Note

• The GoTo statement can be used for error-handling purposes whenever necessary.
• Use the GoTo statement when it improves code readability and maintainability.
• Avoid excessive, unnecessary, or confusing use of the GoTo statement. For example, do not use the GoTo function to skip over lines of code since it decreases the readability of the code and may cause additional errors.
• Do not create “spaghetti code” by misusing the GoTo statement.
• It is best to avoid using the GoTo statement as the primary control structure in your code.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to What is VBA GoTo STATEMENT. We explain the two ways to use Goto statement in excel with examples. You can learn more from the following articles –

• VBA DateAdd Function
• VBA Worksheet Function
• VBA CreateObject