Exit statements in VBA are used to stop or leave a procedure, function, loop, or other VBA structure before it reaches its normal ending point.
Exit statements are especially useful when you want to stop a loop early, leave a Sub procedure, or stop processing when a particular condition occurs.
An Exit statement tells VBA to leave a particular programming structure immediately.
For example:
Exit SubThis statement immediately exits the current Sub procedure.
Exit statements are useful when the remaining code should not be executed.
VBA provides different Exit statements for different structures.
Exit Sub is used to leave a Sub procedure immediately.
Sub Test()
MsgBox "Start"
Exit Sub
MsgBox "End"
End SubThe second MsgBox will not execute because the procedure exits before reaching it.
Exit Sub is often used with an If statement.
Sub CheckMarks()
If Range("A1").Value = "" Then
MsgBox "Enter marks first"
Exit Sub
End If
MsgBox "Marks are available"
End SubIf A1 is empty, the procedure stops immediately.
Exit Sub can prevent further processing when required information is missing.
Sub SaveStudent()
If Range("A2").Value = "" Then
MsgBox "Enter Student Name"
Exit Sub
End If
MsgBox "Student Saved"
End SubThis is useful in data-entry programs.
Exit Function immediately leaves a Function procedure.
Function CheckNumber(ByVal number As Integer) As String
If number < 0 Then
Exit Function
End If
CheckNumber = "Positive Number"
End FunctionWhen the number is negative, the function exits before assigning the result.
Exit Function is useful when a function should stop processing under a particular condition.
Function GetGrade(ByVal marks As Integer) As String
If marks < 0 Or marks > 100 Then
Exit Function
End If
If marks >= 40 Then
GetGrade = "Pass"
Else
GetGrade = "Fail"
End If
End FunctionExit For is used to immediately leave a For...Next or For Each loop.
Dim i As Integer
For i = 1 To 10
If i = 5 Then
Exit For
End If
MsgBox i
Next iThe loop stops when i becomes 5.
Exit For can stop searching when a required student is found.
Dim i As Integer
For i = 2 To 20
If Cells(i, 1).Value = "Rahul" Then
MsgBox "Student Found"
Exit For
End If
Next iOnce Rahul is found, there is no need to continue the search.
Exit For also works with a For Each loop.
Dim cell As Range
For Each cell In Range("A1:A20")
If cell.Value = "Rahul" Then
MsgBox "Found"
Exit For
End If
Next cellExit Do is used to immediately leave a Do loop.
Dim i As Integer
i = 1
Do While i <= 10
If i = 5 Then
Exit Do
End If
MsgBox i
i = i + 1
LoopThe loop stops when i reaches 5.
Exit Do can be used inside a Do While loop when an additional stopping condition is required.
Dim i As Integer
i = 1
Do While i <= 10
If Cells(i, 1).Value = "Stop" Then
Exit Do
End If
i = i + 1
LoopThe loop stops when the word Stop is found.
Exit Do can also be used inside a Do Until loop.
Dim i As Integer
i = 1
Do Until i > 10
If i = 5 Then
Exit Do
End If
MsgBox i
i = i + 1
LoopThe loop exits when i reaches 5, even though the original Do Until condition has not yet been reached.
Exit Property is used to leave a Property procedure.
Property Get StudentName() As String
If Range("A1").Value = "" Then
Exit Property
End If
StudentName = Range("A1").Value
End PropertyIt is mainly used when working with VBA property procedures.
Exit statements are useful for validating input before continuing.
Sub AddStudent()
If Range("A2").Value = "" Then
MsgBox "Enter Student Name"
Exit Sub
End If
If Range("B2").Value = "" Then
MsgBox "Enter Course"
Exit Sub
End If
MsgBox "Student Added"
End SubAn Exit statement can prevent a procedure from continuing when required data is invalid.
Sub CalculateResult()
If Range("B2").Value < 0 Then
MsgBox "Invalid Marks"
Exit Sub
End If
MsgBox "Calculation Started"
End SubThe two statements are used with different types of loops.
| Statement | Used With |
|---|---|
| Exit For | For...Next and For Each loops |
| Exit Do | Do While and Do Until loops |
Different procedure types use different Exit statements.
| Statement | Purpose |
|---|---|
| Exit Sub | Leaves a Sub procedure. |
| Exit Function | Leaves a Function procedure. |
| Exit Property | Leaves a Property procedure. |
A common practical use of Exit For is stopping a search after finding the required record.
Sub SearchStudent()
Dim i As Integer
For i = 2 To 100
If Cells(i, 1).Value = "Amit" Then
MsgBox "Student Found in Row " & i
Exit For
End If
Next i
End SubExit Do can stop processing when a special value is found.
Sub ProcessData()
Dim rowNumber As Integer
rowNumber = 2
Do While Cells(rowNumber, 1).Value <> ""
If Cells(rowNumber, 1).Value = "STOP" Then
Exit Do
End If
Cells(rowNumber, 2).Value = "Processed"
rowNumber = rowNumber + 1
Loop
End SubA procedure can contain more than one possible Exit statement.
Sub StudentCheck()
If Range("A2").Value = "" Then
MsgBox "Enter Name"
Exit Sub
End If
If Range("B2").Value = "" Then
MsgBox "Enter Marks"
Exit Sub
End If
MsgBox "Data is Complete"
End SubEach Exit Sub handles a different validation condition.
When using nested loops, an Exit statement exits the loop in which it is placed.
Dim i As Integer
Dim j As Integer
For i = 1 To 3
For j = 1 To 5
If j = 3 Then
Exit For
End If
Cells(i, j).Value = j
Next j
Next iHere, Exit For exits the inner For loop.
Exit For can stop processing when an invalid mark is detected.
Sub CheckMarks()
Dim i As Integer
For i = 2 To 20
If Cells(i, 2).Value < 0 Or Cells(i, 2).Value > 100 Then
MsgBox "Invalid Marks in Row " & i
Exit For
End If
Next i
End SubExit Sub can be used to stop a student data-entry program when required information is missing.
Sub SaveStudent()
If Range("A2").Value = "" Then
MsgBox "Enter Student Name"
Exit Sub
End If
If Range("B2").Value = "" Then
MsgBox "Enter Student ID"
Exit Sub
End If
If Range("C2").Value = "" Then
MsgBox "Enter Course"
Exit Sub
End If
MsgBox "Student Saved Successfully"
End SubBeginners commonly make these mistakes:
The following example searches for a student and stops as soon as the student is found.
Sub FindStudent()
Dim i As Integer
For i = 2 To 50
If Cells(i, 1).Value = "Ravi" Then
MsgBox "Ravi found in row " & i
Exit For
End If
Next i
End SubThis avoids continuing through the remaining rows after the student is found.
A typical workflow is:
For i = 1 To 10
If i = 5 Then
Exit For
End If
MsgBox i
Next iExit statements provide additional control over VBA programs. They allow you to leave a procedure or loop before it reaches its normal ending point.
The most important Exit statements for beginners are:
Sub Example()
Dim i As Integer
For i = 1 To 10
If i = 5 Then
Exit For
End If
Cells(i, 1).Value = i
Next i
End SubIn this example, the For loop stops when i = 5. Exit statements are especially useful for validation, searching, data processing, and controlling loops.
The next lesson will introduce Nested Loops, where one loop is placed inside another loop.
Question: Which Exit statement is used to immediately leave a For loop?