The Excel VBA Goto statement jumps execution to a labeled line within the same procedure. It is a legacy control-flow tool that is rarely needed in modern VBA because loops and structured error handling cover most cases more safely. This article explains the syntax, how it works, common uses, and why you should usually avoid it.
What Is the Goto Statement in VBA?
In VBA, Goto is a keyword that transfers control to a specified line label. The label is a name followed by a colon, placed at the start of a line. When VBA encounters Goto LabelName, it skips all lines between the Goto and the label, then continues execution from there.
Here is the basic syntax:
Sub ExampleGoto()
Dim x As Long
x = 10
If x < 5 Then Goto SmallNumber
MsgBox "x is 5 or more"
Exit Sub
SmallNumber:
MsgBox "x is less than 5"
End Sub
In this example, if x is less than 5, execution jumps to SmallNumber: and the first MsgBox is skipped. The Exit Sub prevents falling into the label when the condition is false.
How the Goto Statement Works
Line labels must be unique within a procedure and must start with a letter or underscore. They can appear anywhere in the procedure, but a label cannot be placed inside a For loop or If block in a way that would cause invalid jumps. VBA allows Goto to jump forward or backward, but jumping into a loop body is not allowed because it would bypass the loop initialization.
Important rules:
- Labels are not executable statements; they are markers.
- You cannot use
Gototo jump out of a procedure or into another procedure. - You cannot use
Gototo jump into aWithblock or aSelect Caseblock from outside. - Variables declared after the label are still in scope if the jump lands after their declaration, but they may be uninitialized.
For example, this code is invalid because it jumps into the middle of a For loop:
Sub InvalidJump()
Dim i As Long
Goto InsideLoop
For i = 1 To 5
InsideLoop:
Debug.Print i
Next i
End Sub
This will compile but may cause unexpected behavior or a runtime error because i is not properly initialized.
Common Uses of Goto in VBA
Historically, Goto was used for two main purposes: error handling and breaking out of nested loops. In modern VBA, structured error handling with On Error statements often uses Goto to jump to an error handler label. This is still a legitimate use.
Example of error handling with Goto:
Sub SafeDivision()
Dim a As Double, b As Double, result As Double
a = 10
b = 0
On Error Goto ErrHandler
result = a / b
MsgBox result
Exit Sub
ErrHandler:
MsgBox "Cannot divide by zero"
End Sub
Another common use is breaking out of multiple nested loops. Without Goto, you might need a flag variable. With Goto, you can jump directly to a label after the loops:
Sub FindValue()
Dim i As Long, j As Long
For i = 1 To 10
For j = 1 To 10
If Cells(i, j).Value = "Found" Then Goto FoundIt
Next j
Next i
MsgBox "Not found"
Exit Sub
FoundIt:
MsgBox "Found at row " & i & " col " & j
End Sub
Why You Should Avoid Goto in Most Cases
Excessive use of Goto leads to “spaghetti code” — code that is hard to read, debug, and maintain because the flow jumps around unpredictably. Modern programming practice favors structured constructs like If...Then...Else, Select Case, Do...Loop, and For...Next loops, which make the flow clear.
Specific problems:
- Labels can be misplaced, causing logic errors.
- It is easy to forget an
Exit Subbefore a label, causing code to run unintentionally. - Debugging is harder because you cannot easily trace the execution path.
- Code review and collaboration become difficult.
Instead of Goto, consider these alternatives:
- Use
Exit FororExit Doto break out of a single loop. - Use a
Booleanflag to exit nested loops. - Use
On Error Gotoonly for error handling, not for normal flow. - Use
Select Caseinstead of multipleIfjumps.
Goto vs. On Error Goto
There is a common confusion between the Goto statement and On Error Goto. The latter is a special error-handling directive that sets a jump target when a runtime error occurs. It is used like this:
On Error Goto ErrorHandler
This is not the same as a plain Goto because it only activates when an error is raised. After the error handler runs, you typically use Resume or Exit Sub to leave. On Error Goto 0 disables error handling. This is a legitimate and necessary use of Goto in VBA.
Frequently Asked Questions
Can I use Goto to jump to a line in another subroutine?
No. Goto can only jump within the same procedure. To call another procedure, use Call or just the procedure name.
What is the difference between Goto and GoTo? (case sensitivity)
VBA is case-insensitive, so Goto, GoTo, and goto are all accepted. The official documentation uses GoTo, but both work.
Is Goto considered bad practice in VBA?
Yes, except for error handling. Using Goto for normal flow control is widely discouraged because it makes code less readable and harder to maintain. Most VBA developers reserve it for On Error Goto handlers.
Can I use Goto to break out of a nested loop?
Yes, you can jump to a label after the loops, as shown earlier. However, using a Boolean flag or a separate function is often cleaner. If you use Goto, ensure you have an Exit Sub before the label to avoid falling through.
Does Goto work in Excel for Mac?
Yes, VBA is the same on Mac and Windows for this statement. The only differences are keyboard shortcuts and some system-specific features, but Goto behaves identically.
Practical Example: Using Goto for Error Handling
Here is a complete example that reads a number from a cell and handles errors gracefully. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.
Sub ReadNumber()
Dim inputVal As Variant
Dim num As Double
' Read from cell A1
inputVal = Range("A1").Value
' If empty, jump to error handler
If IsEmpty(inputVal) Then Goto EmptyCell
' Try to convert to number
On Error Goto NotNumber
num = CDbl(inputVal)
On Error Goto 0
MsgBox "The number is " & num
Exit Sub
EmptyCell:
MsgBox "Cell A1 is empty."
Exit Sub
NotNumber:
MsgBox "Cell A1 does not contain a number."
End Sub
In this example, Goto is used for two different jumps: one for an empty cell and one for a conversion error. Both are error-handling scenarios, which is an acceptable use.
Conclusion and Best Practices
To summarize, the Goto statement is a low-level control flow tool that is still available in VBA. Use it only when necessary, primarily for error handling with On Error Goto. For other situations, prefer structured loops and conditionals. If you do use Goto, keep the labels close to the jump and always include an Exit Sub or Exit Function before the label to prevent accidental fall-through.
Related
- Excel Template for Weekly Attendance: How to Build One
- Excel Template for Time Sheet: How to Make One
- Excel Template for Tracking Monthly Expenses
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Copy Paste Row: Excel Code and Common FixesTo copy an entire row to another row in Excel with VBA, use Rows(sourceRow).Copy Destination:=Rows(targetRow). For example, Worksheets("Data").Rows(5).Copy…
- VBA InputBox New Line: Add Line Breaks to PromptsTo put a new line in a VBA InputBox prompt, join the prompt text with vbCrLf or vbNewLine.…
- VBA: How to Find an Accredited VA PractitionerIf by VBA you mean the U.S. Department of Veterans Affairs’ Veterans Benefits Administration, use VA’s official accreditation…
- Insert Borders in Excel Using VBA: Code and TipsTo insert borders with VBA, set the LineStyle, Weight, and optionally Color properties of a range’s Borders collection.…
- VBA Access: Sort Records in Queries and FormsTo sort records with Access VBA, put an ORDER BY clause in the SQL used to open a…
- Excel VBA InputBox: Collect Text, Numbers, and ChoicesIn Excel VBA, an InputBox displays a dialog that asks the user to enter information. Use VBA’s InputBox…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.