While developing VBA code in Excel, you need to create sub procedures to carry out specific tasks such as processing the data or automating the process itself. However, you might want to stop an operation during a task without ending the sub completely sometimes.
An effective and easy method of ending a procedure early is to use the Exit Sub command. This is particularly helpful when you don’t want the remaining code to run needlessly once a specific condition is fulfilled.
➤ Go to the Developer tab and click on Visual Basic to open the VBA Editor.
➤ Select Insert > Module.
➤ Copy and paste the following code in the module. Input your specific condition within the If statement.
Sub ExitOnCondition()
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Data")
For i = 2 To 10
If ws.Cells(i, 3).Value < 18 Then
MsgBox ws.Cells(i, 2).Value & " is under 18. Procedure stopped."
Exit Sub
End If
Next i
MsgBox "All entries are 18 or older!"
End Sub➤ Click on Run. You will see the first result that meets the condition and the procedure will stop running.
We have introduced the Exit Sub command in Excel VBA thoroughly in this article along with the explanation of how it works. You will get to know how you can use the Exit Sub command to stop the VBA code when an invalid or duplicate data is found or a specific condition is fulfilled.
What Is The Exit Sub in Excel VBA?
The Exit Sub command is used in Excel VBA coding to instantly stop a procedure before it reaches its natural code end. Once a specific point is reached, it terminates the procedure and skips the rest of the code lines following this command.
The unit of code that carries out a specific function such as automating a process, looping through data or validating an input is called a Sub procedure in Excel VBA. Suppose, a user cancels an operation, an error prevents further execution, or invalid data is recognized in the dataset during the run of the code.
In such specific circumstances, Exit Sub terminates the procedure immediately and stops the remaining lines of code from running. As a result, the VBA codes become more dependable and simpler to debug, in addition to increasing efficiency.
How Does Exit Sub Work in Excel VBA?
A regular Sub procedure starts with the keyword Sub and ends with End Sub. VBA executes each line of the code in between these two keywords. However, when you insert Exit Sub in the middle of the code anywhere, VBA stops the procedure as soon as it reaches the keyword Exit Sub.
The general structure of the VBA code with Exit Sub looks like this:
Sub ProcedureName()
' Input your code here
Exit Sub
' rest of the code here will not run after Exit Sub
End Sub
Here, the VBA will run the code up to Exit Sub but then it will jump out of the procedure and skip the coding section below this command.
Functions can not use Exit Sub to stop earlier. You need to use the Exit Function command to end a function early. Exit Sub will only work inside the Sub procedure code.
Examples of Exiting a Sub in Excel VBA
Example 1: Exit a Sub When a Condition is Met
Sometimes we need to stop the loop when a certain condition in the code has been met. Instead of checking every single entry or record even after the condition has been met long before, the macro will exit instantly when the condition is reached.
This method is effective because it eliminates needless iterations of the data which saves time. When disqualifying data arises in real-world situations, you can utilize this to halt processing.
Suppose, this is our sample dataset:
Here, our condition is finding the first person under 18 years old using Exit Sub.
Steps:
➤ Go to the Developer tab.
➤ Click on Visual Basic to open the VBA Editor.
➤ Click on Insert and select Module. A new module in the VBA Editor window will open.
➤ Enter the following code in the module.
Sub ExitOnCondition()
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Data")
' Loop through all rows of data
For i = 2 To 10
' Check if the person is under 18
If ws.Cells(i, 3).Value < 18 Then
MsgBox ws.Cells(i, 2).Value & " is under 18. Procedure stopped."
Exit Sub ' Exit when condition is met
End If
Next i
MsgBox "All entries are 18 or older!"
End Sub
➥ ws.Cells(i, 3).Value < 18 checks if the age is below 18.
➥ MsgBox ws.Cells(i, 2).Value & " is under 18. Procedure stopped." displays which individual caused the macro to stop.
➥ Exit Sub immediately halts further execution—no other rows are checked as the condition is met.
➤ Click on Run > Run Sub/UserForm to execute the code.
➤ You will see the result in a message box. Sarah is the first person to meet the condition of being under 18 with her age 16. So the result shows Sarah is under 18. Procedure stopped.
Despite having more people under 18 on later rows, no other rows are checked after row 5 because of the Exit Sub command.
Example 2: Exit a Sub When Invalid Data Is Found
While working with so many datasets, it is very common to encounter missing or incorrect data. You can use Exit Sub to discover an invalid entry and instantly halt execution of the code to identify the invalid data. It prevents errors in subsequent calculations and maintains data integrity.
Suppose, this is our dataset where we want to stop the code when there is an invalid data in the age column.
Steps:
➤ Go to the Developer tab.
➤ Click on Visual Basic to open the VBA Editor.
➤ Click on Insert and select Module.
➤ Enter the following code in the module.
Sub ExitOnInvalidData()
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Invalid Age") ' Refers to the dataset sheet
' Loop through each record (row 2 to 10)
For i = 2 To 10
' Check if the Age cell is empty, zero, or negative
If ws.Cells(i, 3).Value <= 0 Or IsEmpty(ws.Cells(i, 3).Value) Then
MsgBox "Invalid age detected for " & ws.Cells(i, 2).Value & ". Exiting procedure."
Exit Sub ' Stop the procedure immediately
End If
Next i
MsgBox "All ages are valid!"
End Sub
➥ If ws.Cells(i, 3).Value <= 0 Or IsEmpty(ws.Cells(i, 3).Value) checks if the Age cell is empty, zero, or negative.
➥ MsgBox "Invalid age detected for " & ws.Cells(i, 2).Value & ". Exiting procedure." alerts and shows the user when an invalid age is found.
➥ Exit Sub ensures the macro stops immediately, so the next rows do not execute.
➤ Click on Run > Run Sub/UserForm to execute the code.
➤ The result will show on row 8, the age is missing. So, a message box saying, Invalid age detected for Ethan. Exiting Procedure will be displayed.
Example 3: Exit a Sub When a Duplicate Data is Found
Duplicate entries in datasets, particularly the ones including user or employee information, can lead to major issues in companies. You can easily identify and halt execution when a duplicate name is discovered in the worksheet by using VBA’s Exit Sub statement. This prevents multiple entries of the same data.
Suppose, this is our dataset:
We need to check if there are any duplicate entries in the worksheet.
Steps:
➤ Go to the Developer tab.
➤ Click on Visual Basic to open the VBA Editor.
➤ Click on Insert and select Module.
➤ Enter the following code in the module.
Sub ExitOnDuplicateData()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim name1 As String, name2 As String
Set ws = Worksheets("Data")
For i = 2 To 10
name1 = ws.Cells(i, 2).Value
For j = i + 1 To 10
name2 = ws.Cells(j, 2).Value
If name1 = name2 And name1 <> "" Then
MsgBox "Duplicate name found: " & name1 & ". Exiting procedure."
Exit Sub
End If
Next j
Next i
MsgBox "No duplicate names found."
End Sub
name1 = ws.Cells(i, 2).Value scans all the entries in the Name column starting from row 2 to 10.
➥ For j = i + 1 To 10 compares the selected name (name1) with every name that comes after it (name2).
➥ If name1 = name2 And name1 <> "" Then checks whether the two names (name1 and name2) are exactly the same and whether the name isn’t blank (name1 <> ""). If both are true, it means a duplicate entry was found.
➥ Exit Sub immediately stops the entire macro once the duplicate is detected — even if there are more rows to check.
➤ Click on Run > Run Sub/UserForm to execute the code.
➤ Since the dataset contains the entry of Emily twice, the resultant message box will display: Duplicate name found: Emily. Exiting Procedure.
Frequently Asked Questions (FAQs)
Can I Use Exit Sub Inside Loops?
Yes, you can use Exit Sub inside many loops while coding in Excel VBA. You can use it within the For, Do While, or For Each loops. When Exit Sub is triggered, it stops the loop and the entire Sub procedure and instantly returns control to Excel.
Can I Use Exit Sub in a Function?
No, it is not possible to stop the operations by using Exit Sub in a Function. It only works to stop a Sub procedure. To exit a Function, you will need to use the Exit Function command which is different.
What’s The Difference Between Exit Sub and Exit For?
Exit Sub exits the entire Sub procedure in Excel VBA depending on the condition, error or validity of the data. On the other hand, Exit For exits only the current loop but continues executing the rest of the Sub procedure without stopping. If you want to stop only the loop, not the entire macro, use Exit For command instead of Exit Sub.
Wrapping Up
As we can see, one of the most basic yet effective tools in VBA programming is the Exit Sub command. Exit Sub enables your program to react intelligently to changing situations without wasting time or executing extraneous code.
Whether you’re managing errors, validating any input, or searching through data, strategic use of the Exit Sub is very important in conjunction with appropriate conditions, loops. Hope this guide was helpful for you to understand the Exit Sub command better and use it to boost your Excel automation skills.
