VBA EXIT SUB

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:

Excel VBA Exit Sub 1

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.

Excel VBA Exit Sub 1-1

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.

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.

How to use Excel VBA Exit Sub Procedure 1

In the Developer” tab, select the “Visual Basic” option in the left corner. It opens the VBA Editor.

How to use Excel VBA Exit Sub Procedure 1-1

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.

How to use Excel VBA Exit Sub Procedure 1-2

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.

How to use Excel VBA Exit Sub Procedure 1-3

Step 3: Initialize a VBA Double variable to hold in Decimal values in Excel VBA.

How to use Excel VBA Exit Sub Procedure 1-4

Step 4: Give the pi value to the double variable using the Worksheet Function in VBA of Pi already available in Excel.

How to use Excel VBA Exit Sub Procedure 1-5

Step 5: Round the Pi value to 3 decimal points and check if the rounded value is equal to 3.142.

How to use Excel VBA Exit Sub Procedure 1-6

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.

How to use Excel VBA Exit Sub Procedure 1-7

Step 7: For the Else condition, print a Message Box saying it is the “Wrong Number”.

How to use Excel VBA Exit Sub Procedure 1-8

Step 8: Add the value to itself and store the sum of pi+pi in the pi variable itself.

How to use Excel VBA Exit Sub Procedure 1-9

Print this sum value and exit the subroutine. Then, end the If-Else VBA statement block.

How to use Excel VBA Exit Sub Procedure 1-10

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.

How to use Excel VBA Exit Sub Procedure 1-11

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.

VBA Exit Sub Example 1

Step 2: Get the age of the person from the user using the VBA InputBox function to get custom values.

VBA Exit Sub Example 1-1

Step 3: Check if the input is a number using the VBA IsNumeric function.

VBA Exit Sub Example 1-2

Step 4: If the input is a numeral, proceed with a nested If statement checking whether the input number is less than 21.

VBA Exit Sub Example 1-3

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.

VBA Exit Sub Example 1-4
  • 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.

VBA Exit Sub Example 1-5

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.

VBA Exit Sub Example 1-6

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.

VBA Exit Sub Example 1-7
VBA Exit Sub Example 1-8

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.

VBA Exit Sub Example 1-9
VBA Exit Sub Example 1-10

It is the icon of vbInformation embedded into the message box.

If you type in an age below 21, you get:

VBA Exit Sub Example 1-11
VBA Exit Sub Example 1-12

It is the icon of vbExclamation embedded into the message box. If you type in an invalid output.

VBA Exit Sub Example 1-13
VBA Exit Sub Example 1-14

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.

VBA Exit Sub Example 2

Step 2: Define an integer variable and provide it a value. Here, it is defined as 5.

VBA Exit Sub Example 2-1

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.

VBA Exit Sub Example 2-2

Step 4: Start a FOR-loop from 1 to 6.

VBA Exit Sub Example 2-3

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.

VBA Exit Sub Example 2-4
VBA Exit Sub Example 2-5

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.

VBA Exit Sub Example 2-6

Step 7: Print the error description in a Message Box using the vbExclamation icon.

VBA Exit Sub Example 2-7

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.

VBA Exit Sub Example 2-8
VBA Exit Sub Example 2-9

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)

1) Does “Exit Sub” in VBA automatically clean up resources or variables?

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.

2) What happens if I use “Exit Sub” in VBA outside of a Sub procedure?

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.

3) Can I use “Exit Sub” to exit a loop in VBA?

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.

4) Is there any difference between “Exit Sub” and “End Sub” in VBA?

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.

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 –

Reader Interactions

Leave a Reply

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