19.5 For Next and For Each Loops
For Next loops are often just called For loops. They allow you to tell the computer how many times you want to repeat a given loop.
For Next loops syntax:
For Counter = start To end
[instructions]
Next [counter]
You use the keyword For, and then you give it a variable name. This will declare a new variable for you. Then you say it equals a starting value to an ending value. These can be variables or they can be hard-coded values. The loop then executes one or more instructions. When you’re done with that loop, it will increment the counter to the next value. As you can see, the Next keyword denotes the end of a loop. If the loop starts at 1 and goes to 10, as we’ll see in our example in a minute, the first time through the counter equals 1, and then it executes those instructions. It hits Next. The counter gets incremented to 2. It then executes the instructions again with that variable having a new value, and the loop continues.
To illustrate this, we have an example function where we will sum all the integers between two values. We will pass the start and end values as parameters to the function.
'For-Next-Loops
Function SumIntegers(First As Integer, Last As Integer) As Integer
Dim Total As Integer
Dim Num As Integer
Total = 0
For Num = First To Last
Total = Total + Num
Next Num
SumIntegers = Total
End Function
Notice that we’re explicitly declaring the data type that we’re expecting to be passed to us. So, the values need to be integers. We also specify that the returned value will be an integer as well.
The first thing we’ll do is set up a variable called Total that will keep track of our running total. We also declare Num as an integer. We’re being explicit in our declaration because we have our Option Explicit listed at the top of our module. The For loop will go from First to Last, whatever values that are passed to the function. Assume the following code was entered into a cell on the worksheet.
=SumIntegers(1,10)
The First variable gets assigned a value of 1, and the Last variable gets assigned a value of 10. So, the loop will go from 1 to 10, and that value will be saved into the Num variable each time it is incremented. As we enter into the loop for the first time, it will be assigned a value of 1. When it hits the Next Num statement, its value will be incremented by one. As long as the value is equal to or less than the last number, it will execute another iteration of the loop.
Total starts out as 0. In the first iteration, it will be assigned 0, its current value, plus 1, the value of Num, for a total of 1. On the second loop, Num will be 2, and that value will added so that Total is 3 after the second loop. It will increment by three in the third loop, and the total will be 6. That pattern continues until the Next Num statement increments the value to 11, at which time it is outside the 1 to 10 range. Then it goes to the instructions after the loop: the Total is assigned to SumIntegers and is returned to the cell that called the function.
The video will show you how you can step through the code as it executes and track values of variables as you go. In the end, a value of 55 will be returned from the function and saved into the cell that called the function. Again, if you know how many times a block of code should run, you will use a For loop.
For Each Loops
A For Each loop performs some operation for each value or item in a collection. In VBA, we’ll often use it with ranges. We will iterate over each cell in a range of cells and do something for each cell.
For Each [cell] In [range of cells]
[instructions]
Next Cell
When we use For Each in a user-defined function (UDF), we will often pass in a range of cells we want to use in our processing or for our calculations. We will then declare a variable that represents a single cell. In VBA, we typically use the Range data type for single cells. We can individually evaluate each cell by using this approach.
Consider a situation where we want to see how many cells in a range are colored yellow. Well, there isn’t a function to do that for us, so we’ll have to write our own. Let’s create a function called YELLOWCOUNT that will take a range of cells as input. Our function might look something like the following.
'For Each Cell Loops
Function YELLOWCOUNT(rng As Range) As Variant
Dim cnt As Long
Dim cell As Range 'note that cell was created as a range (any word would do)
For Each cell In rng
If cell.Interior.ColorIndex = 6 Then cnt = cnt + 1
Next cell
YELLOWCOUNT = cnt
End Function
We declare a variable to keep track of the total count of cells, and we also declare a range, called cell, that will be used with our For Each statement. The For Each statement will evaluate each cell in our variable, rng. Each cell will be assigned to the variable cell, and then the code inside the For Each loop will run for that specific cell. In our case, we will access the Interior property and the ColorIndex associated with that cell. If the ColorIndex = 6, then the cell color is yellow, and we will increment our running total. Each cell in rng will be examined, adding to our total if the cell background is yellow. Once all cells have been examined, Excel continues to the next command after the loop.
Using For Each statements with ranges is a very powerful approach. Users can simply pass a range of cells, and we can examine the values of each of those cells or other properties related to the cell as we process each of those. This approach also allows us to avoid infinite loops, which may be a problem with the Do While and Do Until loops that will be covered in the next section.