VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design


Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

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

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
    For i = LBound(numbers) To UBound(numbers)
        total = total + numbers(i)
    Next i
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
            salary.Interior.Color = vbGreen
        End If
    Next salary
    Exit Sub
    If IsNumeric(salary.Value) = False Then
        salary.Value = 0
    End If
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
    If Len(TextBox1.Text) = 0 Then
        MsgBox "Please enter your name."
    End If
    If InStr(1, TextBox2.Text, "@") = 0 Or _
       InStr(1, TextBox2.Text, ".") = 0 Then
        MsgBox "Please enter a valid email address."
    End If
    If Not IsNumeric(TextBox3.Text) Or _
       TextBox3.Text < 18 Or _ TextBox3.Text > 60 Then
        MsgBox "Please enter a valid age (between 18 and 60)."
    End If
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
    MsgBox "This is the child subroutine."
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
    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.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA StatementsTags: , Last Updated: September 28, 2023

Leave A Comment