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 –