How to use GoSub…Return statement in VBA?

The GoSub…Return statement in VBA (Visual Basic for Applications) is a legacy control flow statement used to transfer control temporarily to a line label within a procedure, execute a series of statements, and then return to the statement following the GoSub statement. It’s akin to calling a subroutine within a subroutine, but it’s less structured and generally less favored compared to defining and calling separate subroutines or functions.

Syntax:

VBA

GoSub LineLabel
...
LineLabel:
    [statements]
Return

LineLabel: A unique label in the procedure that marks the beginning of the block of statements to be executed.
[statements]: The lines of code to be executed when control is transferred to the LineLabel.
Return: This statement transfers control back to the statement immediately following the GoSub statement.

Example:

Here’s a simple example to illustrate the use of GoSub and Return:

VBA

Sub ExampleGoSub()
    Dim num As Integer
    num = 5

    GoSub PrintNumber
    num = 10
    GoSub PrintNumber

ExitHere:
    Exit Sub

PrintNumber:
    MsgBox "Number is " & num
    Return
End Sub

In this example, control is transferred to the PrintNumber line label, the message box is displayed, and then control returns to the line following the GoSub statement. This happens twice with different values of num.

Key Points:

Legacy Use: GoSub…Return is a holdover from earlier programming languages and is not commonly used in modern VBA programming. It’s generally recommended to use more structured approaches like calling separate subroutines or functions.
Readability: Code that uses GoSub…Return can be harder to read and maintain compared to using separate subroutines or functions.
Scope: Variables and their values are shared in the entire procedure, including the code within the GoSub…Return block.
Error Handling: Error handling can become more complex with GoSub…Return due to the jumping around of code execution.
Alternatives: Consider using Sub procedures or Function procedures for better code organization, readability, and maintainability.

While GoSub…Return is available in VBA, its use is generally discouraged in favor of more modern and structured programming techniques.