Excel: Loops

VBA, as well as other programming languages, offers loops. Looping is an operation of repeating the same block of code multiple times. Excel offers a few types of loops, each best in different types of situations. 

For Next Loop

This is the most common type of a loop. In the FOR loop, you specify the value where the loop starts and where it finishes. The code between the FOR and NEXT statement gets repeated. Look at the following example. 

For value = 0 To 5
MsgBox (value)
Next value

In the above example, the MsgBox will display the message 6 times, displaying numbers 0, 1, 2, 3, 4, 5.

Additionally you can skip some values by using the Step value. 

For value = 0 To 5 Step 2
MsgBox (value)
Next value

This time the MsgBox displays numbers: 0, 2, 4. Because the loop counts to 5 it won’t get to number 6. 

In the following example, we have the array with the four names of the movies. This VBA code will display each name inside the MsgBox window.

Dim movies(1 To 4) As String

movies(1) = "Matrix"
movies(2) = "Lord of the Rings"
movies(3) = "Star Wars"
movies(4) = "The Shawshank Redemption"

For Count = 1 To 4
MsgBox (movies(Count))
Next Count

In the next lesson, I will present you the similar example with the FOR..EACH..NEXT loop. 

For Each Next Loop

In Excel VBA, you can use the FOR..EACH..NEXT loop. It loops through each object in a collection. It can be the collection of worksheets or a collection of elements in an array as presented in our example below.

Dim movies(1 To 4) As String
Dim movie As Variant

movies(1) = "Matrix"
movies(2) = "Lord of the Rings"
movies(3) = "Star Wars"
movies(4) = "The Shawshank Redemption"

For Each movie In movies
MsgBox (movie)
Next movie

CAUTION

You cannot declare the movie variable in the FOR..EACH..NEXT loop as String. It has to always be declared as Variant. 

Do While Loop

Unlike the FOR loop, the DO..WHILE loop doesn’t have specified: the start and end value, instead it loops as long as the condition is true.

Look at the following example: 

Dim counter As Integer
counter = 0

Do While counter < 5
MsgBox (counter)
counter = counter + 1
Loop

In this example, Excel checks the first value, which is 0. The condition is met, so the iteration of the loop is executed. The MsgBox window displays the value 0. The next values that also meet the condition are 0, 1, 2, 3, 4. When counter = 5 then the condition is not met and the loop terminates. 

Do Until Loop

The DO..UNTIL loop is very similar to the DO..WHILE loop. The difference between these two loops is that in the DO..WHILE loop the code is executed as long as the condition is True and in the DO..UNTIL loop it is executed as long as the condition is False.

Dim counter As Integer
counter = 0

Do Until counter = 5
MsgBox (counter)
counter = counter + 1
Loop

The MsgBox will display: 0, 1, 2, 3, 4. When counter = 5 then the condition is met and the loop terminates.