Usage of for, while, for each, do loops in Visual Basic

Loops in programming is a technique to iterate over the elements of collections and arrays and other data sets.

The following sections explains different types of loops. All the below snippets based on a simple array of String which is declared below:

Dim Arr(9) As String

Sub InitArr()
    Arr(0) = "A": Arr(1) = "B": Arr(2) = "C": Arr(3) = "D": Arr(4) = "E"
    Arr(5) = "F": Arr(6) = "G": Arr(7) = "H": Arr(8) = "I": Arr(9) = "J"
End Sub

However similar technique can be used with any type of arrays.

For-Next

This is probably the most common loop type. It allows to perform specified number of iterations. By default each loop iteration will increment the value of index by one.

Sub ForLoop()
    
    InitArr
    
    Dim i As Integer
    
    For i = 0 To 9
        Dim val As String
        val = Arr(i)
        Debug.Print val
    Next
    
End Sub

The above sample will print all values from the array:

A B C D E F G H I J

Alternatively, different value for step can be specified with the Step keyword. Step can be negative to iterate in reverse direction.

Sub ForLoopStep()
    
    InitArr
    
    Dim i As Integer
    
    For i = UBound(Arr) To 0 Step -1
        Dim val As String
        val = Arr(i)
        Debug.Print val
    Next
    
End Sub

The above code would output values in the reverse direction:

J I H G F E D C B A

While-Wend

This loop executes while the condition is True

This type of loop can be useful when number of iterations is not known in advance.

Sub WhileLoop()
    
    InitArr
    
    Dim i As Integer
    Dim val As String
    i = 0
    
    While val <> "D"
        val = Arr(i)
        i = i + 1
        Debug.Print val
    Wend
    
End Sub

The above code would output and terminates when the current element equals to D:

A B C D

Do-Loop While

Do-Loop While are similar to While-Wend loops with an exception that the condition executed after the step, so it ensures that at least one iteration will be performed regardless of the condition

Sub DoLoop()
    
    InitArr
    
    Dim i As Integer
    Dim val As String
    i = 0
    
    Do
        val = Arr(i)
        i = i + 1
        Debug.Print val
    Loop While val <> "D"
    
End Sub

The above code would produce:

A B C D

For Each-Next

Although in most cases For-Next loop is used to iterate over the elements of an array it is not restricted to this use case only. Any code can be executed within the body of the loop.

To specifically iterate over the elements of an array For Each-Next loop can be used

Sub ForEachLoop()
            
    InitArr
    
    For Each x In Arr
        Debug.Print x
    Next
    
End Sub

The above snippet would output:

J I H G F E D C B A

Infinite Loops

Incorrect use of loops conditions may result into infinite loop. Such code usually results in the software hang or crash.

For example the below loop would run infinitely as i variable is never incremented.

Sub InifiniteLoop()
    
    InitArr
    
    Dim i As Integer
    i = 0
    
    While i <> UBound(Arr) + 1
        Debug.Print Arr(i)
    Wend
    
End Sub

To terminate the infinite loop (or any running code in VBA) it is possible to press the combination of keys: ctrl+alt+pause/break

This would display the below message box and code can be stopped or entered into debug mode.