Breaking Down the Excel VBA For Loop

08 Jul Breaking Down the Excel VBA For Loop

Howdee! There may be occasions where you need to perform an action a set number of times when writing code. This is generally called “looping”. The Excel VBA for loop is one of the most useful tools an Excel developer can learn. Being able to loop through items and perform actions is at the core of most things you’ll do when developing with Excel. Whether you’re doing an action for every cell in a range, every row/column in a range, or each item in a dictionary, the principle is always the same. Let’s dive in.

Excel VBA For Loop Types

For Loop

An Excel VBA for loop will execute a block of code a specified number of times. You can set the number of loops by hardcoding the integer representing the number of loops you want to perform, but it is generally done with variables. Let’s look at the syntax before I explain scenarios.

Sub For_Loop_Example()
Dim NameArr As Variant

NameArr = Array("Bill", "Jane", "Joe", "Piper", "Logan")

For i = 0 To 4 Step 1
    MsgBox NameArr(i)
Next i

End Sub

To start, we declare the variable NameArr as variant. We then populate this variable with an array of five names. In the for loop, we declare that, for each variable I we want to display the corresponding element of the array in a message box. The “Step 1” tells the loop to increment i by one on each run of the loop.

It’s worth noting that this code would run without “Step 1” since that is the default setting. You only need to have the Step declaration there if you want to increment i by something other than one on each loop. However, it’s good practice to declare this on each for loop. I also want to point out that this loop will start with 0 because the first element in a VBA array will always be 0. This is different than if you were looping through a range of cells, which will start with 1.

For Each Loop

A second Excel VBA for loop is the for each loop. This loop will perform an action for each item in a parent item. For example, you might use a for each loop to check the value of each cell in a range and do something if it meets certain criteria. You might have an array of data and need to perform some action for each element of the array. There are a lot of scenarios.

The decision-making process I use when defining whether I want to use a for each loop, or a for loop, is pretty straight forward. If I have a range, array, dictionary, or other data storage object that I’ve already declared and/or populated with data, and I need to do something to every piece of data within that object, I generally use a for each loop. The reason for this is that this ensures that, no matter what size (number of data points) the object, this ensures I’m always performing the action(s) for each item in it. Let’s look at an example:

Sub For_Each_Example()
Dim NameArr As Variant

NameArr = Array("Bill", "Jane", "Joe", "Piper", "Logan")

For Each Item In NameArr
    MsgBox "The current name is " & Item
Next

End Sub

This is a very simple Excel VBA for loop where I’ve created an array with five names. Then the code is looping through each item (element) of the array and popping up a message window displaying the current name of the loop. It’s important to note that the for each loop will start with the first item in the object and loop through each item in that object in order.

Do While Loop

While not explicitly an Excel VBA for loop, I still want to cover do loops here. Do loops were very difficult for me when I first began writing code, I would often find myself in a do loop that would never end and would have to force quit Excel to get it to stop.

A do while loop is a loop that will run as long as a condition is true. Another way to say this would be, the loop will continue until the condition you set is evaluated as false. I generally use this loop when I’m performing an action on a list of objects. Those objects could be cells that are populated with data (do while cell is not blank), files in a folder (do while file name does not equal nothing), or maybe on an API call (do while call returns data).

There are a lot of scenarios to use do while loops. Let’s check out an example of the syntax:

Sub Do_While_Loop_Example()
Dim NameArr As Variant

NameArr = Array("Bill", "Jane", "Joe", "Piper", "Logan")

Do While i < 5
    MsgBox NameArr(i)
    i = i + 1
Loop

End Sub

Sticking with our NameArr array we’ve used previously, we start a do while loop where we instruct it to loop while i is less than five. I don’t have to initially declare i = 0 as the compiler will do that for me, but a big difference between a for loop and a do loop is that I have to increment the variable i during each do loop with a line of code. This code will behave the same as my for loops, but requires one more line of code.

Do Until Loop

Do until loops are great when you need to perform a process until a result is achieved. Another way to think of this is the opposite of a do while loop. A do while loop runs while something is true. A do until loop runs until a specific condition is true.

A very common application of this loop is to perform an action until you return nothing. For example, I use this loop to grab data in batches of 100 records until my data query returns no rows. Using our NameArr example one last time, let’s look at some syntax.

Sub Do_Until_Loop_Example()
Dim NameArr As Variant

NameArr = Array("Bill", "Jane", "Joe", "Piper", "Logan")

Do Until i = 5
    MsgBox NameArr(i)
    i = i + 1
Loop

End Sub

In the do while loop, we performed an action while i < 5 was true. In this example, we performed the action until i = 5 was evaluated as true. Both subroutines return the same exact result but the evaluation is very different. I also want to call out that you can use an alternate syntax for do loops. You can simply start your loop with “Do” and have the while or until statement after the “Loop” keyword. I’ve included additional examples in the example file to show this. If you’re a free member, head over to that page and download the file to check it out!

While all of these examples are very basic and short, the principles of each are what is important to grasp here. Once you understand each Excel VBA for loop, you’ll have a much better understanding of which loop to use when in your code. If you have questions about one of these loops or one of your own, feel free to leave it in the comments!

Cheers,

R