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]
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
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:
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."
MsgBox "The number is either equal to or greater than 10."
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.
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:
Dim i As Integer
For i = 1 To 10
On i = 5 GoSub loop5times
MsgBox "The code will be executed 5 times."
In the above code, the loop will only run 5 times before moving on to the next line after the ‘Return’ statement.
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:
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
MsgBox "You have selected Option A."
MsgBox "You have selected Option B."
MsgBox "You have selected Option C."
In this code, the user can input their preferred choice and the program will execute the corresponding block of code.
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:
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
MsgBox "An error has occurred."
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
MsgBox "You have selected Menu Option 1."
MsgBox "You have selected Menu Option 2."
MsgBox "You have selected Menu Option 3."
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!