Analyst Cave

VBA GoSub – How to use On…GoSub….Return (VBA)

The VBA GoSub statement allows you to jump to a specified line label basis the numeric value of the provided expression. The expression must be numeric and evaluate to only numbers between 0 and 255 (others will cause exceptions). We will start with a simple example:

Sub TestGoSub()
    num = 2
    On num GoSub Sub1, Sub2
  
    Debug.Print "--The End--"
    Exit Sub
Sub1:
        Debug.Print "Andrew"
        Debug.Print "-----"
        Return
Sub2:
        Debug.Print "George"
        Debug.Print "-----"
        Return
End Sub

The output will be:

George
-----
--The End--

As you will notice the On…GoSub statement is used with other statements such as Exit Sub and the Return statement.

VBA GoSub Syntax

The way the VBA GoSub statement has been defined (see Microsoft reference) is the following:

On numeric_expression GoSub list_of_line_labels

Where:

VBA GoSub examples

Now that we know the syntax of VBA GoSub let us look at some examples of how to use it.

Example #1: Forgetting to use Exit

In below example an error will be raised “Return with GoSub” as we forgot to add the

Sub TestGoSub()
    num = 2
    On num GoSub Sub1, Sub2
  
    Debug.Print "--The End--"
    
    'We forgot to add line below
    'Exit Sub
Sub1:
        Debug.Print "Andrew"
        Debug.Print "-----"
        Return
Sub2:
        Debug.Print "George"
        Debug.Print "-----"
        Return
End Sub

Example #2: Forgetting to use Return

Sub TestGoSub()
    num = 2
    On num GoSub Sub1, Sub2
  
    Debug.Print "--The End--"
    
    Exit Sub
Sub1:
        Debug.Print "Andrew"
        Debug.Print "-----"
        'We didn't do Return so code will continue
Sub2:
        Debug.Print "George"
        Debug.Print "-----"
        'We didn't do Return so code will end the procedure here
End Sub

The outcome will be:

George
-----

Instead of the desired:

George
-----
--The End--

Conclusions

As much as you see the On…GoSub statement as useful I would generally discourage using it too often in your code. In most situations I would suggest:

Exit mobile version