VBA Return

VBA Return Statement

In this article, we will see an outline on Excel VBA Return. It is a statement in VBA which is used for the user-defined functions with the GoSub statement. This statement can also be used outside of a subprocedure when we are using a user-defined function. And when we are using the GoSub statement with the return statement then both GoSub and return statements should be in the same procedure. Now let us discuss what the difference between these two procedures is. First, we will begin with the GoSub statement. In this statement, we stay in the same procedure but move to another statement and get a result with some lines execution of code and then return to the original procedure.

The next is the use of the return statement in a user-defined function. In the user-defined function, a function is created which is returns a value through return statement. We will discuss both types of use of the return statement through some examples.

How to Use Go Sub Return Statement in VBA?

We will learn how to use Go Sub Return Statement in Excel by using the VBA Code.

Example #1 – VBA Return Statement

Let us first discuss how Return with GoSub Statement works in VBA. How do we execute them and what we will learn in this example?. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Once we have a module in the Editor now let us begin with the procedure of declaring a sub-function.

Code:

Sub Example1()

End Sub

Step 3: Now let us send the execution to another line by the GoSub Statement but within the same procedure.

Code:

Sub Example1()

GoSub Sample

End Sub

Step 4: Now let us exit the subprocedure, for now, let us keep this in mind exit sub and end sub are two different.

Code:

Sub Example1()

GoSub Sample
Exit Sub

End Sub

Step 5: Now we can perform some execution of lines of code for the subroutine.

Code:

Sub Example1()

GoSub Sample
Exit Sub
Sample:
MsgBox "This is a sample document"

End Sub

Step 6: Now we will use the return statement to move back to the original procedure.

Code:

Sub Example1()

GoSub Sample
Exit Sub
Sample:
MsgBox "This is a sample document"
Return

End Sub

Step 7: When we press F8 we can see how this procedure works step by step.

Now we can see in the steps that process moved to the line of Sample subroutine when the compiler was on the Gosub Statement and it again moved back to exit sub after giving us the result to Exit substatement.

Example #2 – VBA Return Statement

In the above example, we use a single subroutine and what happens if there are multiple subroutines with multiple GoSub and multiple Return statements. Let us find out in example 2. For this, follow the below steps:

Step 1: We will proceed with the same module just with another subprocedure.

Code:

Sub Example2()

End Sub

Step 2: Similar to above let us declare multiple subroutines with the GoSub statement.

Code:

Sub Example2()

GoSub Sample1
GoSub Sample2

End Sub

Step 3: Now let us exit the sub and define what those subroutines will do.

Code:

Sub Example2()

GoSub Sample1
GoSub Sample2
Exit Sub
Sample1:
MsgBox "This is first Execution"
Return
Sample2:
MsgBox "This is second Execution"
Return

End Sub

Step 4: Run this code by hitting F5 or Run button.

In this example, Code worked in the same way, the compiler went to GoSub Sample1 and executed the lines of code for sample1 then it returned it to GoSub Sample2 and then again went to execute the lines of code for sample2 and then the return statement took it back to the exit substatement.

Example #3 – VBA Return Statement

Let us use a somewhat realistic example for the GoSub statement such as for addition. For this, follow the below steps:

Step 1: Declare a subprocedure for example 3.

Code:

Sub Example3()

End Sub

Step 2: Define two integer variables and ask the user to provide input as integers.

Code:

Sub Example3()

Dim i As Integer, b As Integer
i = InputBox("Enter first number")
b = InputBox("Enter second number")

End Sub

Step 3: Now let us use the GoSub statement and perform the addition operation as follows.

Code:

Sub Example3()

Dim i As Integer, b As Integer
i = InputBox("Enter first number")
b = InputBox("Enter second number")
GoSub Addition
MsgBox "Execution Complete"
Exit Sub
Addition:
MsgBox i + b

End Sub

Step 4: Now we can use the return statement to go back and inform that execution is complete.

Code:

Sub Example3()

Dim i As Integer, b As Integer
i = InputBox("Enter first number")
b = InputBox("Enter second number")
GoSub Addition
MsgBox "Execution Complete"
Exit Sub
Addition:
MsgBox i + b
Return

End Sub

Step 5: Run this code by hitting F5 or Run button placed at the uppermost ribbon of Visual Basic Editor.

Example #4 – VBA Return Statement

For this example look at the below example of code as follows.

Code:

Sub Example4()
Dim i As Integer, k As Integer, area1 As Integer
i = 2
k = 3
area1 = area(i, k)
MsgBox area1
End Sub
Public Function area(x As Integer, y As Integer) As Integer
area = x * y
End Function

In this code, there is no return statement used but it still returns the function with a value.

Explanation of VBA Return

• We have seen why we use the return statement,  in both of the cases with the use of GoSub statement and User-defined functions. However, there is a slight difference. In the GoSub function, we used a Return statement so that the compiler can return to the next code after the subroutine, and in user-defined functions return statement returned the value to be generated by the function.
• We have seen two different methods of using the Return statement through the examples above.

Method 1: The first method is the use of GoSub statement with the return statement.

Method 2: The second method is to use return values in a user-defined function.

Things to Remember

There are few things that we need to remember about the Return statement in VBA and they are as follows:

• The return statement is placed where the execution of codes for the procedures is completed.
• For the GoSub statement, the GoSub and return statement should be in the same procedure.
• For User-defined functions, the values are returned using the function name.
• There can be more than one return statement in the same procedure.

Recommended Articles

This is a guide to the VBA Return. Here we discuss how to Use Go Sub Return Statement in Excel VBA along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. VBA SendKeys (Examples with Excel Template)
2. How to Change Name Worksheet in Excel VBA?
3. VBA On Error Goto (Examples)
4. VBA Input | Excel Templates