In this Article
The GoTo Statement in VBA allows you to jump to a line of code.
First create a line label anywhere in your code:
|
1 |
Skip: |
Then add to “GoTo” statement to jump to the line label
|
1 |
GoTo Skip |
GoTo Examples
This example tests the year. If the year is 2019 or later it will GoTo the Skip line label. This allows you to skip over code if certain conditions are met.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub GoTo_Example() Dim year As Integer year = 2019 If year >= 2019 Then GoTo Skip 'Process Data for Years < 2019 MsgBox "Year is Prior to 2019" Skip: End Sub |
GoTo Multiple Line Labels
You can also use GoTo statements to jump to relevant lines of code. Let’s adjust our previous example to go to different code locations based on which year it is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Sub GoTo_Statement() Dim year As Integer year = 2019 If year = 2019 Then GoTo year2019 ElseIf year = 2010 Then GoTo year2020 Else GoTo year2021 End If year2019: 'Process 2019 MsgBox "Year is 2019" GoTo EndProc year2020: 'Process 2020 MsgBox "Year is 2020" GoTo EndProc year2021: 'Process 2021+ MsgBox "Year is 2021+" EndProc: End Sub |
Notice the “GoTo EndProc” before each line label. We added this line of code so that those code sections are skipped unless they are accessed by the relevant “GoTo”.
GoTo Error Handler End of Procedure
Now let’s use Error Handling to go to the end of the procedure if there is an error.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub GoTo_OnError () Dim i As Integer On Error GoTo EndProc i = 5 / 0 MsgBox i EndProc: End Sub |
GoTo Repeat Code
Our last example will use the GoTo Statement to repeat some code.
Below we’re using a Yes / No Messagebox (Click to learn more) to confirm that the user acknowledges the warning. If they click ‘No’, the message box will be displayed again until they click ‘Yes’ (see GIF below).
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub GoTo_YesNoMsgBox() RepeatMsg: Dim answer As Integer answer = MsgBox("WARNING: This file was opened as a Read-Only file, meaning any changes you make will not be saved unless/until you have Write-Access rights. " & _ Chr(13) & Chr(13) & "Select File, SaveAs to save a copy before working in this file." & vbNewLine & vbNewLine & "Do you understand?", vbExclamation + vbYesNo, "WARNING!") If answer = vbNo Then GoTo RepeatMsg 'Repeat until user clicks "Yes" End Sub |
VBA GoTo a Line Label in Access VBA
All of the above examples work exactly the same in Access VBA as in Excel VBA.
|
1 2 3 4 5 6 7 |
Sub TestGoTo() On Error GoTo ending DoCmd.OpenForm "FrmClients" Exit Sub ending: MsgBox "Cannot open form" End Sub |
