How to Exit a Sub in VBA When You Encounter Errors
Sub procedures are nothing but functions that can be run directly in a VBA module. You’re not required to call a sub procedure anywhere else within the module (just like with a function). But they are also a part of the module that can be run directly using the F5 key (unlike a function).
Situations to Stop Execution
There may be situations where a program’s input is insufficient to run it completely. In this case, it is a good idea to display a message to the user on a msg box rather than running it completely until the end, which would be a waste of time. For example, if user inputs a string/character in the place of a number for age, the program can display an error message stating that the input is an invalid format and stop execution instead of running it until the end.
Exit Sub Statement
Here is the VBA statement that forces the program’s control to the “End sub” statement of the sub procedure. Syntax:
How Does Exit Sub Work?
Imagine there are 50 lines of code in a sub procedure and “
Exit sub” is the statement in the 30th line wrapped in a condition. During runtime, if the condition is met, the control is transferred to the “
Exit sub” statement which in turn transfers the control to the “
End sub” statement of the same procedure. The other 20 lines of code are skipped without being executed.
Example 1: A Simple Example
In a sub procedure we have written some simple code to print numbers from 1 to 50 in a loop.
After printing each number, we check if the printed number is 25. If yes, we want to exit the entire procedure and not print anything further.
Sub print_numbers() For i = 1 To 25 Debug.Print i If i = 25 Then Exit Sub End If Next i End Sub
In the same example, let us print the numbers after the condition.
Sub print_numbers() For i = 1 To 25 If i = 25 Then Exit Sub End If Debug.Print i Next i End Sub
Now the output will contain numbers only until 24 as the counter/iterator variable value is 25 when the condition is met, and sub procedure is exited before printing the value.
You can try out similar programs and execute them using the debugging controls like breakpoints and F8 key to see how the control flows during the execution.
Example 2: A Bigger Example
This is another program with exit sub in two areas. Before looking deep into the area of usage, let us understand what the program does.
This is a program which rewards a bicycle for female children over 10 years old who have a sister. In order to check these criteria, it receives some information from the user. The inputs received are age, gender of the user, and whether the candidate has a sister or not.
If any input/input format is found invalid, a message box is displayed, and the sub procedure is exited. Now speaking of the two areas where exit sub is used:
- If the gender is not in the expected single character format, procedure exits.
- If the age is not in the numeric format, the program exists. For this, the received age value is converted to a number and validated for the format.
Sub exitsub_if_demo() ' declare variables Dim str_age, int_age, sister, gender ' get the age from the user str_age = InputBox("Please enter your age") ' convert age to an integer int_age = CInt(str_age) 'validate that the input is a number- Level 1 If IsNumeric(int_age) Then ' Level -2 validate if age is greater than 10 If int_age &gt;= 10 Then 'receive input - sister from te user sister = InputBox("Do you have a sister? ( Enter only 'Yes' or 'No' ) ") ' Level -3 - validate if the candidate has a sister If sister = "Yes" Then ' receive input on gender from user gender = InputBox("Enter your gender? ( Enter only 'M' for Male or 'F' for Female) ") ' check if the input for gender is a valid single character. If gender &lt;&gt; "M" And gender &lt;&gt; "F" Then MsgBox "Your input is invalid. Please try again! " Exit Sub End If ' Level - 4 - validate if the candidate is a female If gender = "F" Then ' display success msg Debug.Print "Congratulations! You will receive a cycle! Stay back to bag the reward! " Else ' Either not a girl child / invlid input Debug.Print "Sorry! This reward program is only for Girl children!" End If Else ' Either not having a sister / invalid input Debug.Print "Since you do not have a sister, you are not eligible for the gift." End If Else ' Age is less than 10 yrs. Hence not eligible. Debug.Print "You are less than 10 yrs old and hence not eligible for the gift. " End If Else Debug.Print "Age is invalid. Please try again. " Exit Sub End If End Sub
Now let us run this program with different combinations of input.
When we input age=6, the program stops printing the statement below. It does not proceed further.
You are less than 10 years old and hence not eligible for the gift.
When age=11, and answer to question “Do you have a sister?” is “No,” the program stops displaying the statement below and does not execute the rest of the code.
Since you do not have a sister, you are not eligible for the gift.
If input age=15, “Do you have a sister?” = “Yes” and “Gender” = “M”
Sorry! This reward program is only for girl children!
And finally, here is the positive flow in which the program runs completely with all expected inputs.
Input values age=14, “Do you have a sister” = ”Yes,” “Gender” = ”F”
Congratulations! You will receive a bicycle! Stay back to bag the reward!
Did you notice something in the above program? The nested if conditions are designed in such a way that the program stops as it prints a statement. So, the “Exit Sub” statement is mostly not required here. However, in the place of validating the gender input by the user, it does play a vital role.
Example 3: Exit Sub with On Error Statement
This is a program where the well-known “On error” statement is used to redirect the program’s control to an error handling label in case an error is encountered.
Here’s a division program where we get the dividend and divisor from the user and calculate the quotient. The program will run smoothly until the normal exit sum before the label
Errorhandler as long as zero is not entered as an input. In the event the user enters “0” as a divisor, the control hits
errorhandled in order to handle the “division by zero” error as instructed in the “On error” statement.
Sub onerrorexitsub() ' declare an integer variable Dim num1, num2, num3 As Integer ' Type the on error state so that whereever an error is encountered during execution, the control 4ris redirected to the specified label On Error GoTo Errorhandler 'receive the two numbers to be used for division num2 = InputBox("Enter the dividend of the division problem") num3 = InputBox("Enter the divisor of the division problem") ' try a division by zero - this is demo to create an error num1 = Cint ( num2 ) / Cint ( num3 ) ' Display num1 to check if it has some error value now. This statement will take the control to the label in case the value has an error. Debug.Print "The result of the division is - quotient = " &; num1 Exit Sub Errorhandler: Debug.Print "There was an error, so exited the Sub procedure" Exit Sub End Sub
If num2=45 and num3=5, the output statement is
The result of the division is – quotient = 9
No error was encountered, so program ran smoothly until the first exit sub statement and not any further.
If num2=13 and num3=0, the output statement is
There was an error, so exited the Sub procedure
A division by zero error was encountered. So, the control was moved to the errorhandled that displayed this output statement and exited the sub procedure.
In bigger programs with a lengthy flow of actions and in loops & conditions, the “Exit sub” statement is very useful.
This statement helps the control avoid executing unnecessary lines of code when not required — because it might cause unexpected outcomes in case of underlying coding bugs.
Also, it acts as a great timesaver.