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:
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.
Dim numbers(5) As Integer
Dim total As Integer
'Code to populate numbers array
MsgBox "Total: " & total
For i = LBound(numbers) To UBound(numbers)
total = total + numbers(i)
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.
Dim salary As Range
For Each salary In Range("B2:B10")
If salary.Value > 50000 Then
salary.Interior.Color = vbRed
salary.Interior.Color = vbGreen
If IsNumeric(salary.Value) = False Then
salary.Value = 0
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.
MsgBox "Form successfully submitted."
If Len(TextBox1.Text) = 0 Then
MsgBox "Please enter your name."
If InStr(1, TextBox2.Text, "@") = 0 Or _
InStr(1, TextBox2.Text, ".") = 0 Then
MsgBox "Please enter a valid email address."
If Not IsNumeric(TextBox3.Text) Or _
TextBox3.Text < 18 Or _ TextBox3.Text > 60 Then
MsgBox "Please enter a valid age (between 18 and 60)."
The GoSub…Return statement can also be used to create nested subroutines, where one subroutine calls another and returns back to the parent subroutine.
MsgBox "This is the parent subroutine."
MsgBox "Returned to parent subroutine."
MsgBox "This is the child subroutine."
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.
On Error GoTo ErrorHandler
'Code for main subroutine
'More code for main subroutine
MsgBox "An error has occurred."
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.