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 error handler statement “GoTo Label” but what “GoSub Return” do is it will return back to the next line of code rather than continuing from 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 sub procedure 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 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 lets run the code line by line by pressing the F8 key. Upon pressing F8 key first time it will initiate the macro running.
Now press F8 key one more time to go to the next line.
We all know macro will run the code line by line, but if you press 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.
Click on Ok of the message box to return to the coding window.
Now it has highlighted the “Return” statement if you press 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 label name “Macro1” task since we have mentioned “Return” statement it is back to the next line of code i.e.
What is statement says is, “go-to label named as Macro2”. In the below for label “Macro2”, I have mentioned a specific set of task.
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 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 label “Macro3”, press F8 key.
Now third label task will be executed.
Now click on OK, it will highlight the “Return” statement.
Now pressing 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 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
- Label name should be same in GoSub and Return statement and in return statement label name should be followed by a colon (:).
- Always use Exit Sub after all the return statements are done 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 –
- IsDate in Excel VBA
- Replace String in VBA
- CLng Function in VBA
- VBA VARTYPE Examples
- Variable Types in VBA
- Examples in VBA
- Switch in VBA
- Filter in VBA