How to Use For Loops in VBA
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
In programming, loops allow you to run commands multiple times.
It’s simple, but it’s one of the most powerful things you can do with VBA code. (And it saves you a huge amount of typing.)
Let’s take a look at how loops save you time and give you great power over your Excel spreadsheet.
How to write a for loop in VBA
There are two main parts of a for loop:
First is the counter. This counts how many times the loop has been executed (it also tells the computer how many times to execute the commands).
Second is a list of commands. It can be a single line of code or many, but each line in this section will be executed every time the loop runs.
Once the loop has run as many times as you’ve specified, it ends and your VBA code continues executing on the next line.
Here’s a simple for loop that we’ll use to check out the basic syntax:
Sub diag() For i = 1 To 5 Cells(i, i).Value = i Next End Sub
To start a for loop, you’ll need a counter. That’s what “For i = 1 To 5” does. i is the variable that we’ll use for our counter.
You don’t need to use i as the name of your variable; it can be anything you want.
But i is commonly used in programming, so we’ll stick with that convention.
The range we specify, 1 to 5, tells Excels how many times to run the loop. In this case, it will run the loop five times.
The first time it runs the loop, the variable will be set to 1. The next time, it will be set to 2. And so on.
Next, we write the lines of code that Excel will execute. In our case, we’ve identified a cell (at the coordinates (i, i)) and set the value equal to i.
Finally, we use Next to tell Excel that it’s reached the end of the loop. Then it will start over if the counter hasn’t maxed out.
Here’s the result:
The first time the loop runs, i is equal to 1. So the cell at (1, 1) is set to 1.
The next time, i is 2, so the cell at (2, 2) is set to 2.
And so on.
Here’s another example of a for loop:
Sub averageSales() For i = 2 To 21 newRange = Range(Cells(i, 2), Cells(i, 5)) Cells(i, 6).Value = Application.Average(newRange) Next End Sub
Open the example spreadsheet and go to the Visual Basic editor in the Developer tab. Or use the shortcut below:
Windows: Alt + F11
Mac: Fn + ⌥ + F11
Copy and paste the script above into a new module, and execute it one step at a time using F8. Watch as Excel steps through the script, and think about how the value of i changes what’s happening.
How to use nested for loops in VBA
To get even more out of a for loop, you can put another for loop inside of it. This is called nesting loops, and while it can be difficult to wrap your head around at first, it’s very powerful.
Here’s an example of nested loops that we can use on our example spreadsheet:
Sub quarterlyAverage() Dim breakStatus As Integer breakStatus = 0 For i = 2 To 21 newRange = Range(Cells(i, 2), Cells(i, 5)) For j = 2 To 5 Cells(i, j).Select If Cells(i, j) = 0 Then breakStatus = 1 Exit For Else breakStatus = 0 End If Next Range("J1").Value = breakStatus If breakStatus = 1 Then Cells(i, 6).Value = "Missing data" Else Cells(i, 6).Value = Application.Average(newRange) End If Next End Sub
Let’s break it down, one section at a time. We’ll look at the outer loop first. Here’s what the outer loop looks like:
For i = 2 To 21 newRange = Range(Cells(i, 2), Cells(i, 5)) If breakStatus = 1 Then Cells(i, 6).Value = "Missing data" Else Cells(i, 6).Value = Application.Average(newRange) End If Next
Excel counts from 2 to 21, and looks at cells in the range of (i, 2) to (i, 5). On the first time through, that will be A2:A5. The next time, B2:B5, and so on.
Then it checks a variable called “breakStatus.” If that variable is set to 1, then Excel writes “missing data” in column F.
(This tells us that one of the sales figures is missing.)
If breakStatus isn’t set to 1, it takes the average of the cells in the range we established before.
That’s it. It’s pretty simple.
Now let’s look at the inner loop:
For j = 2 To 5 If Cells(i, j) = 0 Then breakStatus = 1 Exit For Else breakStatus = 0 End If Next
This creates a new counter, j, and looks at individual cells in each loop. In the first run through the outer loop, i is set to 1. So the first run of the inner loop will look at (i, j), which is (1, 2), or A2.
Excel checks to see if that cell is set to 0, and if it is, it sets breakStatus to 1 and exits the loop (that’s what “Exit For” does; see below).
If that cell isn’t set to 0, the loop continues. On the next run through the inner loop, j will be set to 3, so Excel looks at A3 to see if it’s set to 0.
Exiting loops
Exiting loops early using “Exit For” is a great way to stop the computation if some condition has been met. You’ll almost always want to use it as part of an IF-THEN statement, as we have here.
It’s important to note that Exit For only exits one loop. In our case, it stops the inner loop from running again. But the outer loop continues as normal.
Run the script on the example spreadsheet one step at a time by pressing F8.
You’ll be able to see Excel stepping through the loops, which should give you more insight into how the script works.
Start thinking in loops
The idea of a loop is quite simple. But it can get complicated fast.
With practice, you’ll develop a sense of how loops work and interact. It can be frustrating at first, but stick with it, and it’ll become much more intuitive!