EDUCBA

Home VBA VBA Resources VBA Tips VBA Return

VBA Return

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

You can download this VBA Return Excel Template here – VBA Return Excel Template

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.

Insert 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

VBA Return Example 1-1

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

VBA Return Example 1-2

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

Exit Sub Example 1-3

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

VBA Return Example 1-4

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

VBA Return Example 1-5

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

VBA Return Example Output 1

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

VBA Return Example 2-1

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

Code:

Sub Example2()

GoSub Sample1
GoSub Sample2

End Sub

Multiple Subroutines Example 2-2

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

VBA Return Example 2-3

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

VBA Return Example Output 2

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

VBA Return Example 3-1

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

Integer Variables Example 3-2

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

Addition Operation Example 3-3

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

Return Statement Example 3-4

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

VBA Return Example Output 3

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

VBA Return Example 4-1

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