VBA For vs For Each

VBA For Loop vs For Each Loop

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

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 iterations is know before the loop is executed (as compared to the While and Do Until Loops).
How to declare a For Loop:

Where:
Counter
Is a numeric variable, the counter index for the loop. This can be only of VBA Native data types (e.g. Long, Integer, Double etc.)

Start and end
The starting value of the Counter and the ending value of the Counter

Step
Statement indicating that the StepIncrement between increments will be defined

StepIncrement
Optional, defaults to 1. A defined step between Counter values. E.g. for Step 2 the Counter value will be incremented by 2 instead of 2

Next
Closing statement for the loop. Optionally you can specify the Counter variable

VBA For Example

Below a simple VBA For example loop:

It is also possible to loop backwards by providing a start value higher than an end value:

VBA For Example with Step

Below a simple VBA For example with a defined Step loop:

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

VBA For Each Loop

The VBA For Each loop is a scope that defines a list of statments 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:

Where:
Iterator
The iterating variable. Used to iterate through the elements of the collection or array

Items
A collection or array of items

Next
Closing statement for the loop. Optionally you can specify the Iterator variable

VBA For Each example

Below a simple For Each example:

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

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

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.

Want to learn more on the goto statment. Read this MSDN post

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:

VBA For vs For Each Performancewise

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?

Performance

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.

For Each Loop

Initialization omitted on purpose.

The results

Arrays: VBA For Loop vs For Each Loop
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…
VBA For Loop Wins!

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.

For Each Loop

Initialization omitted on purpose.

The results

Collection: VBA For Loop vs VBA For Each Loop
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…
VBA For Each Loop Wins!

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:

Rule #1: Always use the For loop when looping through VBA Arrays
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.

Related Posts