What is Excel VBA Exit Sub Procedure?
In Excel VBA (Visual Basic for Applications), the Exit Sub statement is used within a procedure (subroutine) to exit the subroutine immediately and return control to the calling code. This statement is typically used when a specific condition is met, and you want to terminate the execution of the subroutine before it reaches the end.
Consider the following example:
Create a subroutine to initialize a FOR-loop to run through 20 numbers. Here, check until the loop number goes to 15. Then, print “Goal Reached” in the Immediate Tab and perform VBA Exit Sub early. It makes you break out of the loop and not execute the rest of the subroutine.
Here, the loop runs from 1 to 15. Once 15 is reached, the Print function in VBA is executed, and the VBA Exit Sub, if i = 15 condition, is satisfied.
Table of Contents
Key Takeaways
- Exit Sub is used to prematurely exit a Sub procedure (subroutine) in Visual Basic for Applications (VBA).
- Placed within a Sub procedure, it immediately transfers control to the statement following the Sub call. It is typically used when a specific condition is met, and further execution of the Sub is unnecessary.
- The exit is confined to the specific Sub procedure where Exit Sub is used. It does not impact other parts of the code.
- It is often used in conjunction with conditional statements (e.g., VBA Exit Sub If…Then) to create more dynamic and responsive code.
How to use Excel VBA Exit Sub Procedure?
To see an example of how to use VBA Exit Sub Early in the Subroutine, view the Example below.
Step 1: In the Excel Workbook, once you open it, click the “Developer” tab in the Excel toolbar.
In the “Developer” tab, select the “Visual Basic” option in the left corner. It opens the VBA Editor.
In the Editor toolbar, select “Insert,” then in the drop-down, select “Module.” It will open a new blank tab in which you can code in.
Now, you can follow the steps to learn how to utilize the VBA Exit Sub Procedure.
Step 2: Start by naming the subroutine you want to check rounded-off variables.
Step 3: Initialize a VBA Double variable to hold in Decimal values in Excel VBA.
Step 4: Give the pi value to the double variable using the Worksheet Function in VBA of Pi already available in Excel.
Step 5: Round the Pi value to 3 decimal points and check if the rounded value is equal to 3.142.
For this, start an If condition and use the VBA Worksheet function RoundUp with the variable pi and the number of places it should be rounded off to.
The Round function in VBA selects the next highest value. Hence, the value of 3.14159 will be rounded off to 3.142.
Step 6: Print the Pi value if it is true. Then exit the subroutine. There’s no need to check the Else condition.
Step 7: For the Else condition, print a Message Box saying it is the “Wrong Number”.
Step 8: Add the value to itself and store the sum of pi+pi in the pi variable itself.
Print this sum value and exit the subroutine. Then, end the If-Else VBA statement block.
Code:
Sub roundFunc()
Dim pi As Double
pi = Application.WorksheetFunction.pi
If Application.WorksheetFunction.RoundUp(pi, 3) = 3.142 Then
MsgBox pi
Exit Sub
Else
MsgBox “Wrong Number”
pi = pi + pi
MsgBox pi
Exit Sub
End If
End Sub
Step 9: Click “F5” or the “Run” icon on the toolbar in the Excel VBA Editor to run the above subroutine. This prints the pi value if it is rounded off correctly. Else, it will print twice the value of the pi value.
Since the VBA Exit sub if True is executed, the Else part is not checked or executed. With this, the output is as viewed.
Examples
See the different ways in which VBA Exit Sub can be used for Error Handling and checking conditions.
Example #1
In this example, you need to create a system to check if the person is an adult(aged 21 and above) or not. This can be done using an If-Else statement, and if the condition is False, we should exit the subroutine. You need to also exit the subroutine if an invalid input is given.
Step 1: Start with naming the subroutine to check if a person is an adult.
Step 2: Get the age of the person from the user using the VBA InputBox function to get custom values.
Step 3: Check if the input is a number using the VBA IsNumeric function.
Step 4: If the input is a numeral, proceed with a nested If statement checking whether the input number is less than 21.
Step 5: If the given number is less than 21, print a message stating the person is “Not an adult” and then exit the subroutine.
- vbExclamation is a constant used to embed icons into the message box. This one prints an exclamation mark into the message box in a yellow triangle.
Step 6: In the Else statement, if the input is greater than or equal to 21, initiate another Message Box function printing the age along with the vbInformation constant. Then, end the If-Else statement block.
The vbInformation is a keyword in the message box where you can print an icon into the Message Box in VBA,
where
- vb – Visual Basic
- Information – Name of the icon.
Step 7: In case, the input given is not a number, but another data type, write the Else block for that scenario.
In the Else block, print the message with the message box, “Invalid Input” and then exit the subroutine. Then end the If statement.
Code:
Sub exit_example()
Dim age As Integer
age = InputBox(“Enter your Age:”, “Adult or not”)
If IsNumeric(age) Then
If age < 21 Then
MsgBox “Not an adult”, vbExclamation
Exit Sub
Else
MsgBox “You are an adult of ” & age, vbInformation
End If
Else
MsgBox “Invalid Input”
Exit Sub
End If
End Sub
Step 8: Run the code by running the macros in the Excel Worksheet. It will open the available Macros in the Excel Workbook.
Step 9: Select the subroutine you need to run. Then click “Run.” Alternatively, you can just double-click on the macro subroutine to run it. The result is as shown.
It is the icon of vbInformation embedded into the message box.
If you type in an age below 21, you get:
It is the icon of vbExclamation embedded into the message box. If you type in an invalid output.
Example #2
In this example, you will learn to use the VBA Exit Sub on Error. The errors can range from type mismatch to Division by 0. These errors may halt other processes in a system. Hence, to circumvent this situation, you can use VBA Exit Sub on Error. The process of how to do so can be seen below.
Step 1: Start with defining a subroutine to handle errors caused in a subroutine.
Step 2: Define an integer variable and provide it a value. Here, it is defined as 5.
Step 3: Start an iterative variable and then perform error handling. In case an error occurs, the error handling line is executed, and it executes the Errorhandler section.
Step 4: Start a FOR-loop from 1 to 6.
Step 5: Divide the iterative variable by the integer variable defined in step 1. Then, print the quotient value in the Immediate tab. After that, decrease the integer variable by one and continue the FOR-loop.
In this way, as the iterations increase, the integer variable decreases to 0, causing a division by 0 error.
Step 6: Define the Errorhandler section of the code after the FOR-loop.
Step 7: Print the error description in a Message Box using the vbExclamation icon.
vbExclamation is a constant used to embed the exclamation icon in the Message Box.
Code:
Sub Err_Handel()
Dim a As Integer
a = 5
Dim c As Long
On Error GoTo Errorhandler
For c = 1 To 6
no = c / a
Debug.Print no
a = a – 1
Next c
Errorhandler:
MsgBox Err.Description, vbExclamation
Exit Sub
End Sub
Step 8: Run the subroutine defined above. It will print the quotient values in the Immediate tab and print the error handling message while exiting the subroutine.
It comes in handy when operating with multiple subroutines and functions. If one subroutine stops working due to an error, it may stop all the other procedures. Hence, using Error handling and exiting subroutines when an error occurs makes it very easy to debug and also find the source of the error.
Important Things To Note
- Use comments to explain why you are using Exit Sub to enhance code readability.
- You can combine Exit Sub along with error handling to handle unexpected situations. Don’t rely solely on Exit Sub without implementing proper error handling.
- Don’t use VBA Exit Sub from a function; use Exit Function instead.
- Avoid deeply nested subroutines with multiple Exit Sub statements, as it can complicate code flow.
Frequently Asked Questions (FAQs)
No, “Exit Sub” in VBA does not automatically clean up resources or variables. It simply exits the subroutine without executing the remaining code. Cleanup, if needed, must be handled explicitly in the code.
If you use “Exit Sub” outside of a Sub procedure in VBA, it will result in a compilation error. “Exit Sub” is meant to be used within Sub procedures to exit them prematurely.
Yes, you can use “Exit Sub” to exit a loop in VBA. You can set it up in such a way that you exit the subroutine once an If condition fails.
Yes, there is a difference:
“Exit Sub” is used to exit a Sub procedure prematurely, returning control to the calling code.
“End Sub” is used to mark the end of a Sub procedure, but it does not exit prematurely; the code will continue executing until it reaches the end.
Recommended Articles
This is a guide to VBA Exit Sub. Here we learn How to use Excel VBA Exit Sub Procedure with suitable examples. You can learn more from the following articles –
Leave a Reply