The Right Way to Use the Goto Statement in VBA

Contents

The need for a branching statement in code

In a procedure, the program code or the application in which the code is running may encounter unexpected issues or may not work like you expect. To be on the safe side, we can transfer the program’s control to any specific line, thereby skipping the error prone block of code.

This kind of branching helps us skip any block of code or even re-run a block of code from any previous line if the expected action has not happened and the program steps need to be repeated.

The Goto branching statement

Goto is a popular branching statement available for use in most programming languages. In VBA, we can use this statement to move the program control to any line (forward or backward) within the same sub-procedure.

Syntax of the Goto statement

Goto <line>

The parameter line can either be a label or a line number.

A label is a any word with a colon in front of any line of code. When a label is used in the Goto statement, the control is transferred to the line which starts with that label.

Sample Programs

Validate age to see if it is realistic

This is a sample program that validates age. If the age is not realistic , the control is transferred to the line that has the label “Line_no1”.

Sub Goto_demo()

Dim age

Line_no1:
age = InputBox("Enter your age")
If age &gt; 110 Then
    MsgBox "It is impossible. Try again"
    Goto Line_no1
End If

'Rest of the code can go here

End Sub

Error display using Goto label

Sub Goto_demo1()
' handling error at any line in the code using this label
On Error Goto I_handle_error

' declare variables
Dim a, b

' initialize variables
a = "Hello World"
b = Split(a, " ")

' Try to display a value out of the array's upperbound
MsgBox b(2)

' label and content
I_handle_error:
Debug.Print "There is an error " &amp; Err.Number &amp; vbCrLf &amp; Err.Description
    
End Sub

Output is available in the screenshot below. As you can see a “Subscript out of range” error is caught.

"There is an error 9 Subscript out of range"

Using Goto 0 instead of the actual line number or a label

Goto 0 enables the normal debugging process.

Run-time error '9': Subscript out of range first example.

You might wonder what difference this statement makes and why it’s even useful.

Imagine a scenario where “On Error Resume Next” is used at the beginning of the program to skip known errors that need to be ignored. But later after the 100th line of code, we suspect there might be some errors that need to be debugged.

So, in this case, we can use this “On error Goto 0” statement before that 100th line to enable the normal error handling and to prevent the compiler from ignoring the upcoming errors.

This statement essentially disables or reverses the effect of “on error resume next.”

Another example using Goto 0

As in the previous example, we first use “On error resume next” to ignore errors. The program ignores all errors until it encounters the “On Error Goto 0” statement. After this line, error are displayed as usual during runtime.

Run-time error '9': Subscript out of range error popup.
Sub Goto_demo2()
' skip the normal error handling process
On Error Resume Next

' declare variables
Dim a, b

' initialize variables
a = "Hello World"
b = Split(a, " ")

' Try to display a value out of the array's upperbound - error will be skipped
Debug.Print b(2)

' Display a proper value
Debug.Print b(0)

' enable the normal error handling
On Error Goto 0

' Try running the below error prone code.
Debug.Print b(10)

End Sub

Goto statement that re-runs a block of code

Sub chk_weight()

' declare variables
Dim allwed_wt, current_wt, flag

' assign values in kg
allwed_wt = 10
flag = 0

'set a label to start again if weight is not within limits
' CInt is used to convert the string to a number
checkagain:
current_wt = CInt(InputBox(" Please enter the weight of your baggage"))

' check if the weight is within limits
' display appropriate message to the passenger
If current_wt &lt;= allwed_wt Then
    MsgBox "The weight of your baggage is within the permitted limit. No further action is required."
Else
    MsgBox "The weight of your baggage is more than the permitted limit. Please remove some baggage now as the current weight will be asked again."
    'Goto the block of code that gets input and validates the weight of baggage
    Goto checkagain
End If

End Sub

Goto statement using a line number

Considering the example above, the label “Checkagain” can be replaced with the respective line number 89 as show in the image below. But as we keep maintaining code by adding and removing lines of code in our modules or procedures, the line numbers are subject to change.

Hence, it is not a good practice to use line numbers in your Goto statements. It is always advisable to create and use labels instead.

A Goto statement using a line number -- don't do this.

Conclusion

Goto is a very popular branching statement which is used in many programming languages in combination with conditions. If used wisely, this statement can help you save time, prevent unnecessary errors, capture or debug unknown errors, and keep your code organized.

One thought on “The Right Way to Use the Goto Statement in VBA”

Leave a Reply

Your email address will not be published. Required fields are marked *