Return to VBA Code Examples

VBA Loop Through Array / For Each Item in Array

This tutorial will teach you how to loop through Arrays in VBA.

There are two primary ways to loop through Arrays using VBA:

  • For Each Loop – The For Each Loop will loop through each item in the array.
  • For Next Loop – The For Next Loop will loop through specified start and end positions of the array (We can use the UBound and LBound Functions to loop through the entire array).

For Each Item in Array

The For Each Loop enables you to loop through each element of the array.

The above procedure will loop through all the names in the array.

vba array loop 2

For Next Loop

The For Next Loop will loop through each item at a specified start and end position of the array.

Loop Through Part of Array

You can manually specify the start and end positions for your loop. This may be appropriate if you know your array size and/or you only want to loop through part of an array.

In the example above, we have looped through positions 2 and 3 of the array.  The immediate window would return the names as follows.

vba array loop 1

Loop Through Entire Array

Next, we will use the UBound and LBound Functions to loop through an entire array. This is extremely useful if the start and end positions of the array might change (ex. a Dynamic Array):