VBA break is Available? How to Exit For Loop?
In programming languages, there is normally a break statement is available to exit a loop (For, While, Do While) while it is still not completed. There is no break statement in VBA.
What is break statement generally?
In programming languages, there is normally a break statement available to exit a loop (For loop, While, Do While) while it is still not completed.
There is no break keyword/statement in VBA.
Using the Exit For
In VBA, we use Exit For to get out of the for loop entirely.
We will show you simple as well as exiting for loop in the array below.
How to exit a for loop in VBA?
In this example,
- we set a variable initial value to 1.
- A for loop is used to iterate till value of variable i = 10
- In each iteration, the value of i increases by 1.
- We will concatenate the current value of i, and as for loop finishes, a MsgBox is displayed as shown below:
Normal for loop without breaking it:
|
1 2 3 4 5 6 7 8 9 10 |
Sub for_exit() Dim i As Integer Dim curr For i = 1 To 10 Step 1 curr = curr & vbNewLine & i Next i MsgBox curr End Sub |
Output:
Break the for loop by Exit For
The example below exits the For loop by using “Exit For” as the value of i reaches 5:
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub for_exit() Dim i As Integer Dim curr For i = 1 To 10 Step 1 If i >= 5 Then Exit For Else curr = curr & vbNewLine & i End If Next i MsgBox curr End Sub |
Output:
You can see, For loop till the value of i reached 5.
Exiting an array with For Each loop example
Similarly, you may exit the For..Each loop that we use with arrays or collections.
In the example below, we have a string array of four elements. As we reach the third item, we will check its value and exit the For..Each loop.
First, let us show how it works without Exit For:
The code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub for_exit() Dim Prog_Langs(1 To 4) As String Dim combined_str As String Dim arr_item As Variant 'Array Elements Prog_Langs(1) = "C#" Prog_Langs(2) = "Go Lang" Prog_Langs(3) = "C++" Prog_Langs(4) = "Python" 'Using the For Next loop For Each arr_item In Prog_Langs combined_str = combined_str & arr_item & ", " Next arr_item 'Display all array elements MsgBox combined_str End Sub |
Output:
Now, we will exit the For..Each loop as it encountered the array element “C++”. So, MsgBox should display only two languages:
Code with “Exit For”:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub for_exit() Dim Prog_Langs(1 To 4) As String Dim combined_str As String Dim arr_item As Variant 'Array Elements Prog_Langs(1) = "C#" Prog_Langs(2) = "Go Lang" Prog_Langs(3) = "C++" Prog_Langs(4) = "Python" 'Using the For Next loop with Exit For For Each arr_item In Prog_Langs If arr_item = "C++" Then Exit For End If combined_str = combined_str & arr_item & ", " Next arr_item 'Display array elements MsgBox combined_str End Sub |
Result:
