Excel VBA Return Statement
VBA is wonderful and a lifesaver for those who know the in and out of VBA coding. It can save tons of time at our workplace. In our earlier articles, we have discussed many things and concepts of VBA return. One such concept is the GoSub Return statement. In this article, we will introduce you to these concepts.
What is GoSub Return Statement?
The statement “Go Sub” means it will go to the line of code indicated by label through name and performs a specific set of the task until it finds the statement “Return.”
This is similar to the error handler statement “GoTo Label,” but what “GoSub Return” does is it will return back to the next line of code rather than continuing from the label name.
Below is the syntax of the VBA GoSub Return statement.
GoSub [Label Name] ….. Line of Code [Label Name]: ….. Line of Code to perform the task
I know it is difficult to understand from reading the syntax but using the example, you may be able to understand the concept.
Before I go-to examples, let me tell a few things about this statement.
- GoSub statement calls subroutine in VBA, which is named by a label within the same routine or Function.
- Both GoSub and Return should be within the same procedure. We cannot call another subroutine macro here.
- You can introduce any number of GoSub Return statements.
- A return statement will continue the execution of the code from where it left off before it jumped to the subprocedure indicated by a label name.
How to use GoSub Return Statement in VBA?
To understand the usage of this statement, look at the below code first. Later I will explain to you the code line by line.
Sub Go_Sub_Return() GoSub Macro1 'Label Name1 GoSub Macro2 'Label Name2 GoSub Macro3 'Label Name3 Exit Sub Macro1: MsgBox "Now running Macro1" Return Macro2: MsgBox "Now running Macro2" Return Macro3: MsgBox "Now running Macro3" Return End Sub
To understand this code, let’s run the code line by line by pressing the F8 key. Upon pressing the F8 key first time, it will initiate the macro running.
Now press the F8 key one more time to go to the next line.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
We all know macro will run the code line by line, but if you press the F8 key, it will not go to the next line; rather, it works differently.
It has jumped to the label named as “Macro1” this is because in the previous statement “GoSub,” we have instructed the macro to go to the subprocedure label name “Macro1”, so accordingly, it has jumped to the respective label name.
Now by pressing the F8 key, excel macro will execute the label “Macro1” task of showing the result in the message box.
Click on the Ok of the message box to return to the coding window.
Now it had highlighted the “Return” statement. If you press the F8 key one more time, what it will do is it will “Return Back” to the previous line of code before it jumped to the label name.
Last time it executed the code “GoSub Macro1,” and it performed the label name “Macro1” task. Since we have mentioned the “Return” statement, it is back to the next line of code i.e.
What is statement says is, “go-to label named Macro2”. In the below for label “Macro2”, I have mentioned a specific set of tasks.
Now we will get the second subprocedure task of showing value in the message box.
Now click on, Ok. It will highlight the second “Return” statement.
Now upon clicking the F8 key, it will return to the next line of code before it returns to the label “Macro2”. Now it will highlight “GoSub Macro3”.
Now it will go to the label “Macro3”, press the F8 key.
Now third label task will be executed.
Now click on, OK. It will highlight the “Return” statement.
Now press the F8 key one more time; it will jump back to the line of code “Exit Sub.”
No, it will exit the subroutine. It is necessary to add the word “Exit Sub” otherwise, and it will encounter an error.
Now take a look at one more example of using the GoSub Return statement.
Sub Go_Sub_Return1() Dim Num As Long Num = Application.InputBox (Prompt:="Please enter the number here", Title:="Divsion Number") If Num > 10 Then GoSub Division Else MsgBox "Number is less than 10" Exit Sub End If Exit Sub Division: MsgBox Num / 5 Return End Sub
This code will ask you to enter the number which is >10. If the number is >10, then it will perform the task of dividing it by 5 (Label Name is “Division”), or else it will show the message as “Number is less than 10”.
Things to Remember
- The label name should be the same in GoSub and Return statement, and in the return statement label name should be followed by a colon (:).
- Always use Exit Sub in VBA after all the return statements are made to avoid the error message.
This has been a guide to VBA Return. Here we learn how to use GoSub Return Statement in VBA along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –