What is a Loop?

Loops are a very common part of any programming language. A loop is a set or sequence of programming instructions executed repeatedly during runtime until a specific condition is met.

For Loop

A “For” loop is the favorite of many programmers like me since it has the benefits below:

1. We can provide instructions like when to start or end the loop
2. How many times to iterate/until when the iteration must be continued

Syntax:

For < iterator variable >= < start value > to < end value >

< the code goes here>

Next < iterator variable >

Example:

Sub for_loop_demo()

' declare a variable
Dim i

'Loop to print all numbers from 5 to 55
For i = 5 To 55
    
 ' Statement / code to print the value of the current iterator variable
 Debug.Print i
 
Next i

End Sub

When this code runs, all numbers from 5 to 55 get printed easily.

From this example, it is very clear that the “For” loop has helped us avoid typing/copying 51 statements and printing the numbers in continuous order. Just one statement within the loop used wisely following the syntax has saved us a lot of time.

How to Break the Loop: The “Exit For” Statement

There may be instances when we want the loop to stop execution with the current iteration and continue execution of lines of code outside the loop.

Let’s come up with a practical situation for an example.

A developer of a customer service website may not want to read and record a generated ticket number until the submission of a form is successful. So, here we may simply keep counting numbers within the “For” loop. We will place a condition to check the page load within the same loop. If that condition is met, we can use “EXIT FOR” statement to exit the loop and proceed with the execution of other programming statements under the “For” loop.

Examples of the “Exit For” Statement in the “For” Loop

Example 1:

In the same piece of code where we are trying to print numbers from 5 to 55, I have inserted a condition to exit/break the loop when the iterator value is 10. So, after printing the value 10, the condition is met, and the “Exit For” statement is triggered. The statement completely breaks or skips all iterations of the “For” loop and hits the statement below the “Next” statement of the corresponding “For” statement.

Sub for_loop_demo()

' declare a variable
Dim i

'Loop to print all numbers from 5 to 55
For i = 5 To 55
    
 ' Statement / code to print the value of the current iterator variable
 Debug.Print i
 
 If i = 10 Then
    Exit For
 End If
 
Next i
Debug.Print "Execution ended at " & i

End Sub

Output:

Example 2:

In this example, the same condition is placed before all other statement(s) in the “For” loop. This causes the current iteration also to be skipped while the condition is met.

Sub for_loop_demo()

' declare a variable
Dim i

'Loop to print all numbers from 5 to 55
For i = 5 To 55
    
 If i = 10 Then
    Exit For
 End If
    
 ' Statement / code to print the value of the current iterator variable
Debug.Print "Print iterator value as condition failed " & i 
Next i
Debug.Print "Execution ended at " & i

End Sub

Though the debug statement says that execution ended at 10 in the output, the number has not been printed like the other numbers above because the “Exit For” statement got executed before it.

Example 3:

Here is an example from a snack bar where we receive input from a customer and try to get the price of the food items from our catalog. The search is done through a loop, and once the matching item is found, its price is displayed in a readable format to the customer.

In the excel sheet name “Snackbar,” we have a list of food items/snacks with the price and other related details. This is treated as the catalog mentioned in the example.

As we know, once the item searched for is found, no more searches are required i.e., to speak technically, we can say that further iterations in the “For loop” should be stopped. For this reason, once we get the matching snack item, we use the “Exit For” statement and exit the for loop completely.

Here goes the VBA program/code:

Sub purchase_snacks()

' Declare variables
Dim snack_wanted, current_snack, price As String
Dim available As Boolean


' receive an input from the customer
snack_wanted = InputBox("What would you like to buy? ")

'set an initial value for the flag variable
available = False

' Check if the item is available
For i = 2 To 13
    'use a current_snack variable to store the cell value
    current_snack = Sheets("Snack_bar").Cells(i, 1).Value
    
    'compare value with what the customer asked for
    If current_snack = snack_wanted Then
    
        'display it's price
        price = Sheets("Snack_bar").Cells(i, 2).Value
        Debug.Print "The cost of " & snack_wanted & " is " & price
        
        'change value of flag variable and exit loop as further iterations are not required
        available = True
        Exit For
    
    End If
Next i

'now display a msg if it was not found
If available = False Then ' means still - the same initialized value?
    Debug.Print " Sorry , the item '" & snack_wanted & "' is not available. Please try something else."
End If

End Sub

On running this, I provide the value “Idly” as input. I want to buy a plate of idlis, and I’m looking for its price.

On clicking the “OK” button, I see its price as the output in the immediate window.

If you notice the code, there is a flag that is set to true when the item is found. Because that sentence was executed for the run time input, we did not receive the sorry message.

Now, let us try to search for a snack item that is not in the catalog. Let me search for “Veg Roll.”

Output:

A-ha! Here it is. This time all the iterations of the “For” loop were executed successfully. Still, the available flag remained as “False” as the item was not found in the catalog and the if condition was never met. Because of this, after the loop was executed fully, the last condition was met, and the Sorry statement was displayed.

Example 4:

Use of “Exit For” statement in a “For each” loop is demonstrated here:

Here we set the range of cells in A2 to A14 as an array. We want to count the number of empty cells in the range until we find a principal amount as “12000.”

Sub empty_demo()

' declare a range
Dim myrange

'declare a variable to store count
Dim cnt

' define the range  and initialize count
myrange = Range("A1:A14")
cnt = 0

'loop through each cell of the range
For Each cell In myrange
    ' if the cell is empty , we increment the value of cnt variable by 1
    If IsEmpty(cell) Then
        cnt = cnt + 1
    End If
    If cell = 12000 Then
        Exit For
    End If
    
Next cell

' display the number of empty cells
MsgBox "There are " & cnt & " empty cells in the mentioned range. "

End Sub

Until the value 12000 is hit in Col A, we have two empty cells, which are calculated and displayed in the message box.

If we try with a value that is not in the range, the “Exit For” statement will not be hit, because of which a count of all empty cells in the given range would be displayed.

Conclusion

The Exit for statement functions almost the same as the “Exit Sub” or the “Exit Function” statements I had explained in two of my articles earlier. The only main difference is that it exits only one essential part of the huge code and proceeds with the execution of statements under the “Next” statement.

Apart from the “For” loops, the “Exit For” statement can also be used in “For each” loops.