VBA break is Available? How to Exit For Loop?

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:

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:

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:

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”:

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: