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).

Contents

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:

Exit Sub

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

Output:

Output for a sub procedure with a simple code to print numbers from 1 to 50 in a loop.

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.

Output of printing the numbers after the condition

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:

  1. If the gender is not in the expected single character format, procedure exits.
  2. 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 >= 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 <> "M" And gender <> "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.

Output

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.

Output

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”

Output:

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”

Output

Congratulations! You will receive a bicycle! Stay back to bag the reward!

Output of the reward program

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

Input values:

If num2=45 and num3=5, the output statement is

Output:

The result of the division is – quotient = 9

Explanation:

No error was encountered, so program ran smoothly until the first exit sub statement and not any further.

Input values:

If num2=13 and num3=0, the output statement is

Output:

There was an error, so exited the Sub procedure

Explanation:

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.

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *