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 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.

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

Additionally, you can skip some values by using the Step 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.

In the next lesson, I will present you with a 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.

CAUTION

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

Do While Loop

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

Look at the following example:

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, and 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.

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

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.