Differences between loops in VBA

Looping in excel VBA

When you think of loops in VBA, there are two types of loops that come to mind – Do loops and For loops. Loops in VBA for excel can loop through objects and collections, but here I will focus on looping through a range object.

Let’s say we want to print the integer sequence 1 to 10 in a column on a spreadsheet. There are two main ways to use a For loop:

For… Next

Dim i As Long
For i = 1 To 10
    Cells(i, 1) = i
Next

Normally, a For Next loop is used when the number of loops is known. That is, you must define a beginning and end.

For Each… Next

Dim myRange As Range, myCell As Range, i as long
Set myRange = Range("B1:B10")
i = 1

For Each myCell In myRange
    myCell = i
    i = i + 1
Next

Whereas, a For Each loop can be used when the number of iterations is unknown, perhaps if you have a range of undetermined size.

There is a Microsoft KB article Using For Each to Loop Through Arrays Is Not Recommended that goes into detail as to why a For Next is preferable to a For Each loop in arrays. In general, I hardly run into a situation of an array of unknown size – if I don’t know the size, then I write code to find out the size and use that variable in a For Next loop.

To print the same integer sequence in a column, there are two main ways to use a Do… Loop:

Do While

Dim i As Long
i = 1

Do While i < 11
    Cells(i, 3) = i
    i = i + 1
Loop

or

Do
    Cells(i, 3) = i
    i = i + 1
Loop While i < 11

Do Until

Dim i As Long
i = 1

Do Until i > 10
    Cells(i, 4) = i
    i = i + 1
Loop

or

 Do
    Cells(i, 5) = i
    i = i + 1
Loop Until i > 10

With the For loops, we were dealing with objects directly. With these Do loops, we are instead dealing with conditions of a specific grouping of objects. Essentially, it can be used in the same situation as the For Each loop – when the size is unknown.

Condition and Size

In fact, the size cannot be specified in the Do loop. If you want to specify the size, you will need a With block:

Dim myRange As Range, i As Long
Set myRange = Range("G1:G10")
i = 1

With myRange
    Do While i < 11
        .Cells(i, 1) = i
        i = i + 1
    Loop
End With

Whereas to specify a condition in the For Next loop of known size, there needs to be an If block:

For i = 1 To 10
    If Cells(i, 7) < 11 Then
        Cells(i, 7) = i
        i = i + 1
    End If
Next

And the winner is

Look, I prefer For Next loops in pretty much all of my code, but as demonstrated above both types of loops can be used to do the same things just in different ways and there is a time and a place for all the loop types.

If you’re looking to improve with loops, I’d recommend defining some sort of logic with two conditions and iterate through a given range to manipulate the cells – you should find yourself nested pretty deeply if you’re new to loops.

Leave a Reply

Your email address will not be published. Required fields are marked *