VBA GoSub…Return Statement

One of the key features of VBA is its ability to execute subroutines, which are small blocks of code that can be called upon to perform a specific task. While the traditional method of calling subroutines is through the use of ‘Sub’ statements, the GoSub…Return statement provides a more efficient and flexible way of executing subroutine code.

In this blog post, we will explore the purpose and syntax of the GoSub…Return statement, as well as provide the top five examples of its usage. We will also discuss important notes and remarks, and conclude with asking for feedback and views on this powerful VBA statement.

VBA GoSub…Return Statement

Purpose of GoSub…Return Statement

The GoSub…Return statement is used to call a subroutine from within another subroutine. This allows for more efficient and organized code execution, as well as the reuse of code segments. It is especially useful when a certain task needs to be performed multiple times within a procedure, as it eliminates the need to rewrite the same code multiple times. In addition, the GoSub…Return statement allows for greater flexibility in terms of controlling the flow of execution and handling errors within subroutines.

Syntax of VBA GoSub…Return Statement

The syntax of the GoSub…Return statement is as follows:

GoSub label
     'code block
Return

The ‘label’ in the first line is a reference to the line of code where the subroutine begins, and the ‘code block’ represents the actual code to be executed. The ‘Return’ statement on the last line is used to return control to the line of code after the ‘GoSub’ statement. The subroutine code block can be placed anywhere within the main subroutine, and can be called upon multiple times.

Examples of  VBA GoSub…Return Statement

Calculate Sum of Numbers

Let’s say we have an array of numbers and we want to calculate the sum of these numbers. We can use the GoSub…Return statement to create a subroutine that calculates the sum and then call it whenever needed.

Sub CalculateSum()
    Dim numbers(5) As Integer
    Dim total As Integer
    'Code to populate numbers array
    
    GoSub AddNumbers
    MsgBox "Total: " & total
    Exit Sub
AddNumbers:
    For i = LBound(numbers) To UBound(numbers)
        total = total + numbers(i)
    Next i
    Return
End Sub

Format Cells Based on Criteria

In this scenario, we have a list of employees and their corresponding salaries in an Excel sheet. We want to highlight the cells that have a salary greater than $50,000 in red and those less than or equal to $50,000 in green. We can use the GoSub…Return statement to create a subroutine and then call it for each cell that needs to be formatted.

Sub FormatSalaries()
    Dim salary As Range
    For Each salary In Range("B2:B10")
        GoSub ValidateSalary
        If salary.Value > 50000 Then
            salary.Interior.Color = vbRed
        Else
            salary.Interior.Color = vbGreen
        End If
    Next salary
    Exit Sub
ValidateSalary:
    If IsNumeric(salary.Value) = False Then
        salary.Value = 0
    End If
    Return
End Sub

Check for Errors in Data Entry Form

When creating a data entry form in VBA, it is important to validate the user’s input to ensure that no errors occur when the data is submitted. The GoSub…Return statement can be used to perform different checks and validations for each field on the form.

Sub ValidateForm()
    GoSub ValidateName
    GoSub ValidateEmail
    GoSub ValidateAge
    MsgBox "Form successfully submitted."
    Exit Sub
ValidateName:
    If Len(TextBox1.Text) = 0 Then
        MsgBox "Please enter your name."
        TextBox1.SetFocus
        End
    End If
    Return
ValidateEmail:
    If InStr(1, TextBox2.Text, "@") = 0 Or _
       InStr(1, TextBox2.Text, ".") = 0 Then
        MsgBox "Please enter a valid email address."
        TextBox2.SetFocus
        End
    End If
    Return
ValidateAge:
    If Not IsNumeric(TextBox3.Text) Or _
       TextBox3.Text < 18 Or _ TextBox3.Text > 60 Then
        MsgBox "Please enter a valid age (between 18 and 60)."
        TextBox3.SetFocus
        End
    End If
    Return
End Sub

Nested Subroutines

The GoSub…Return statement can also be used to create nested subroutines, where one subroutine calls another and returns back to the parent subroutine.

Sub ParentSub()
    MsgBox "This is the parent subroutine."
    GoSub ChildSub
    MsgBox "Returned to parent subroutine."
    Exit Sub
ChildSub:
    MsgBox "This is the child subroutine."
    Return
End Sub

Error Handling within Subroutines

The GoSub…Return statement allows for better error handling within subroutines. In the below example, the ‘GoSub’ statement calls the ‘ErrorHandler’ subroutine, which handles any errors that may occur within the ‘MainSub’ subroutine.

Sub MainSub()
    On Error GoTo ErrorHandler
    'Code for main subroutine
    GoSub ErrorHandler
    'More code for main subroutine
    Exit Sub
ErrorHandler:
    MsgBox "An error has occurred."
    Resume Next
End Sub

Important Notes & Remarks

• The GoSub…Return statement is not commonly used in modern VBA coding, as newer techniques such as ‘Call’ and ‘Function’ statements have replaced its usage.
• Use caution when using the GoSub…Return statement, as it can lead to spaghetti code if not used properly.
• The ‘Return’ statement should always be used at the end of the code block in order to return control back to the main subroutine.

In conclusion, the GoSub…Return statement is a powerful tool in VBA for organizing and reusing code within subroutines. It allows for greater flexibility and control in code execution, and can also be used for error handling. While not commonly used in modern VBA coding, it is still an important concept for any VBA programmer to understand. Have you used the GoSub…Return statement in your VBA projects?

What are your thoughts and experiences with it? Share your feedback and views in the comments section below.