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.
|
1 2 3 |
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, and 5.
Additionally, you can skip some values by using the Step value.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
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 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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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 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:
|
1 2 3 4 5 6 7 |
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, 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.
|
1 2 3 4 5 6 7 |
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.