VBA On-GoSub Statement

The On…GoSub statement is one of the many keywords in VBA that is used for branching, or changing the flow of the program based on certain conditions. In this blog post, we will delve deeper into the purpose, syntax, top 5 examples, important notes and remarks of the On…GoSub statement in VBA.

VBA On…GoSub Statement

The Purpose of the On…GoSub Statement

The On…GoSub statement is used to define a block of code that will be executed based on the result of an expression. It allows the program to jump to a specific section of code and then return to the original point of execution. This statement is particularly useful when dealing with a large amount of code or when creating more complex macros.

The Syntax of the On…GoSub Statement

The general syntax for the On…GoSub statement is as follows:

On expression GoSub line
[On expression1 GoSub line1]
...
[On expressionN GoSub lineN]
[line]
Return
[line1]
...
[lineN]

The ‘expression’ in the above syntax can be any numeric or string expression and the ‘line’ can refer to any line number in the code. The On…GoSub statement must always be followed by a ‘Return’ statement to ensure that the program returns to the original point of execution.

Examples of the VBA On…GoSub Statement

Conditional Branching

One of the most common uses of the On…GoSub statement is for conditional branching. This allows the program to execute different blocks of code based on the result of a given expression. For example:

Sub conditionalBranching()
    Dim x, y As Integer
    x = 10
    y = InputBox("Enter a number")
    On x = y GoSub positiveNum
    MsgBox "The number is neither equal to nor greater than 10."
    Exit Sub
positiveNum:
    MsgBox "The number is either equal to or greater than 10."
    Return
End Sub

In the code above, if the user inputs a number that is equal to or greater than 10, the program will execute the block of code under the ‘positiveNum’ label. Otherwise, it will move on to the next line after the ‘Return’ statement.

Looping

The On…GoSub statement can also be used for looping through a specific section of code multiple times. This is achieved by using a ‘For…Next’ loop and setting the On…GoSub statement as the ending point for the loop. For example:

Sub looping()
    Dim i As Integer
    For i = 1 To 10
        On i = 5 GoSub loop5times
        MsgBox "The code will be executed 5 times."
        Exit For
loop5times:
    Next i
End Sub

In the above code, the loop will only run 5 times before moving on to the next line after the ‘Return’ statement.

Menu Selection

The On…GoSub statement is also useful for creating menu selections within a program. This can be achieved by using a ‘Select Case’ statement and setting the On…GoSub statement as the result for each case. For example:

Sub menuSelection()
    Dim choice As String
    choice = InputBox("What is your preferred choice? (A, B, or C)")
    Select Case choice
        Case "A": GoTo A
        Case "B": GoTo B
        Case "C": GoTo C
    End Select
A:
    MsgBox "You have selected Option A."
    Return
B:
    MsgBox "You have selected Option B."
    Return
C:
    MsgBox "You have selected Option C."
    Return
End Sub

In this code, the user can input their preferred choice and the program will execute the corresponding block of code.

Error Handling

The On…GoSub statement can also be used for error handling in VBA. This is useful when there are multiple points of failure in a program and you want to jump to a specific section of code based on the type of error that occurs. For example:

Sub errorHandling()
    On Error GoSub errorHandler
    Dim x, y As Integer
    x = InputBox("Enter a number")
    y = InputBox("Enter another number")
    MsgBox "The result is " & x / y
    Exit Sub
errorHandler:
    MsgBox "An error has occurred."
    Return
End Sub

In the above code, if an error occurs during the execution of the program, it will jump to the ‘errorHandler’ label and display an error message.

Creating Menus or Jump Lists

The On…GoSub statement is also useful for creating menus or jump lists within a userform in VBA. This can be achieved by using a ‘ListBox’ control and setting the On…GoSub statement as the result for each list item. For example:

Private Sub ListBox1_Click()
    Select Case ListBox1.ListIndex
        Case 0: GoSub menuOption1
        Case 1: GoSub menuOption2
        Case 2: GoSub menuOption3
    End Select
    Exit Sub
menuOption1:
    MsgBox "You have selected Menu Option 1."
    Return
menuOption2:
    MsgBox "You have selected Menu Option 2."
    Return
menuOption3:
    MsgBox "You have selected Menu Option 3."
    Return
End Sub

In this code, when the user clicks on a specific list item, the corresponding block of code will be executed.

Important Notes and Remarks

• The ‘expression’ in the On…GoSub statement must evaluate to a numeric or string value.
• The ‘line’ specified must correspond to an actual line number in the code, otherwise, an error will be thrown.
• The On…GoSub statement should always be followed by a ‘Return’ statement to ensure the program returns to its original point of execution.
• When using the On…GoSub statement, the program will not return to the line after the ‘GoSub’ statement unless a ‘Return’ statement is encountered.
• It is recommended to use error handling when using the On…GoSub statement to prevent unexpected results and errors.

In conclusion, the On…GoSub statement in VBA is a powerful tool for branching and creating more complex macros. It allows the program to jump to a specific section of code and then return to the original point of execution. By using conditional branching, looping, menu selection, error handling, or creating menus, the On…GoSub statement can greatly enhance the functionality of a VBA program.

Thank you for reading this blog post! I hope you found it informative and helpful. I would love to hear your feedback and views on the On…GoSub statement and how you have used it in your own VBA projects.

Please leave a comment below and let’s start a conversation. Happy coding!