VBA For Loops are the basis of VBA automation. The VBA For and For Each loops are the most frequently used types of loops for repetitive procedures. In this post let’s learn all there is to know about the VBA For Loop and the VBA For Each Loop, the advantages and the disadvantages of both.

VBA For Loop

The For Loop is scope that defines a list of statements that will be executed repeatably for a certain number of times. The For Loop is the most often used loop for situations when the number of iterationsvb is know before the loop is executed (as compared to the While and Do Until Loops).
How to declare a For Loop:

For Counter = Start To End [ Step StepIncrement ]
   '...Code here...
Next [ Counter ]

See examples of how to use this kind of loop.

VBA For Example

The For loop uses a counter as an argument, the counter if usually used within the loop itself. Below a simple VBA For example loop where we simply want to print a series of numbers from 1 to 5:

Dim i as Long

For i = 1 To 5
   Debug.Print i
Next i
'Result: 1,2,3,4,5

We can loop however both ways – from a lower to a higher number or the opposite – from a higher to a lower numbers. See below that is also possible to loop backwards by providing a start value higher than an end value:

Dim i as Long

For i = 3 To 1
   Debug.Print i
Next i
'Result: 3,2,1

The key thing to remember is that the counter needs to be a whole number (integer).

VBA For Example with Step

In some cases we might want to increment the counter by values larger than the default (>1). For this we need to use the Step statement. Below a simple VBA For example with a defined Step loop:

Dim i as Long

For i = 1 To 5 Step 3
   Debug.Print i
Next i
'Result: 1,4

A Step value can also be negative hence providing you with the possibility to loop backward by providing a negative number:

Dim i as Long

For i = 5 To 1 Step -3
   Debug.Print i
Next i
'Result: 5,2

To conclude – the For loop is useful when you want to execute some code using a series of numbers. As you will find in the next section – it shouldn’t be used for iterating through objects/collections etc.

VBA For Each Loop

The VBA For Each loop is a scope that defines a list of statements that are to be repeated for all items specified within a certain collection/array of items. The For Each loop, as compared to the For loop, can’t be used to iterate from a range of values specified with a starting and ending value.
How to declare a For Each Loop:

For Each Iterator in Items
   '...Code here...
Next [ iterator ]

See examples below of how to use this kind of iteration through objects/collections.

VBA For Each example

Below a simple For Each example:

Dim x(3) as Long, xIterator as Variant
x(0) = 1: x(1) = 2: x(2) = 3

For Each xIterator in x
   Debug.Print x
Next xIterator
'Result: 1,2,3

The For Each Loop is easier to use in the sense that you need not specify a starting and ending variable value. However, the For Each loop is a treacherous loop! Using the For Each Loop is some cases will come at a high performance cost. Skip to For vs For Each to understand the dangers of using For Each.

Continue and Exit For Loops

Continue For Loop

A Continue statement in loops is a statement that allows you to skip all remaining statements in your current loop iteration and proceed to the next loop iteration. Compared to Visual Basic, however, VBA (Visual Basic for Applications) does not have an equivalent of a Continue For statement. Fortunately there are at least two ways you can simulate a Continue For statement.

Option 1: Use If

For i = 1 to 10 
   '...Code here...
   If Not(continue_boolean_statement) then 
       '...Non-Continue code here...
   End if
Next i

The continue_boolean_statement is meant to be a boolean condition to skip to the next iteration like “i > 10”.
Option 2: Jump using Goto

For i = 1 to 10 
   '...Code here...
   If continue_boolean_statement then Goto ContinueForLoop
   '...Non-Continue code here...  
ContinueForLoop:
Next i

The ContinueForLoop is a goto label statement. Both approaches are equivalent, however, the Goto statement is widely discouraged in the software development world as it is very error prone.

Exit For Loop

The Exit statement can be used not only for loops (including the For loop) but for other scopes like Subs and Functions. The Exit statement will immediately exit the current scope of execution.

Exit For Loop example:

For i = 1 to 10 
   '...Code here...
   If continue_boolean_stament) then Goto EndForLoop
   '...Non-Continue code here...  
EndForLoop:
Next i

Performance Comparison of For and For Each

It is important to remember that the For and the For Each loop are to be usually used in different circumstances. There are also several limits to using both. Let us first list some of these key differences and similarities:

• For and For Each loops can both be used to iterate through collections and arrays
• Only the For loop can be used to iterate through a range of specified values e.g. 1 to 10
• Only the For loop can be used to replace items of an iterated collection or array

Usually the For loop is capable or replacing any For Each loop, but not the other way round. On the other hand, when dealing with collections of items it is more convenient to use a For Each loop without having to index through the collection of items. But that’s just looking at the capabilities of both approaches. What about performance? Is there any performance penalty for using the For or the For Each loop? Which one is faster and when?

There are several situations where you need to vary your usage of For vs For Each when looking at performance (execution time). Let’s look at some common scenarios:

Looping through Arrays

To test the performance of For vs For Each let’s consider a simple scenario: we want to calculate the division remainder (Mod) of 2 arrays of data type Long. As both arrays will be 10k elements in size this will require 10’000^2 operations = 100 million.

For Loop

Initialization omitted on purpose.

Dim x(10000) As Long, y(10000) As Long
'...
For i = LBound(x) To UBound(x)
  For j = LBound(y) To UBound(y)
    res = x(i) Mod y(j)
  Next j
Next i

For Each Loop

Initialization omitted on purpose.

Dim x(10000) As Long, y(10000) As Long, tmpX, tmpY
'...
For Each tmpX In x
  For Each tmpY In y
    res = tmpX Mod tmpY
  Next tmpY
Next tmpX

The results

Arrays: For vs For Each From the results above it seems the VBA For Each loop was much slower than the For loop. The For Loop was almost 3xfaster than the equivalent For Each loop! Hopefully this proves that the For Each loop is not recommended for looping through VBA Arrays. So the winner of this round is…

Looping through Collections

Again we will consider the same example to test the performance of For vs For Each. Each collection of objects will contain 1000 items. Hence in total there will be 1000*1000 = 1 million Mod operations.

For Loop

Initialization omitted on purpose.

Dim x as Collection, y as Collection
'...
For i = 1 To 1000
  For j = 1 To 1000
    res = x.Item(i) Mod y.Item(j)
  Next j
Next i

For Each Loop

Initialization omitted on purpose.

Dim x as Collection, y as Collection, tmpX as Variant, tmpY as Variant
'...
For Each tmpX In x
  For Each tmpY In y
    res = tmpX Mod tmpY
  Next tmpY
Next tmpX

The results

Collection: For vs For Each From the results above it seems the VBA For loop was much slower than the For Each loop. The For Each loop definitely rules when it comes to looping through collections. This time the difference in performance is even more substantial as the For Each loops was almost 27x faster than the For loop! So the winner of this round is…

Conclusions

The VBA For loop and the VBA For Each loop are not interchangeable in any circumstances and have to be used carefully. What you need to remember is the following:

1. Rule #1: Always use the For loop when looping through VBA Arrays
2. Rule #2: Always use the For Each loop when looping through a collection of objects such as the VBA Collection, VBA Dictionary and other collections

To make it more simple consider using the For Each loop only when looping through a collection of objects.