Looping

For simple 1-dimensional arrays, the For-Each-Next tends to be faster than the For-Next.

For - Next

You can iterate through the elements of an array using a For - Next loop.

Public Sub ForNextLoop() 
Dim arValues As Variant 
Dim vItem As Variant 
Dim iArrayCount As Integer 
   arValues = Array(1, 2, 3, 4, 5) 

   For iArrayCount = 0 To UBound(arValues) 
      vItem = arValues(iArrayCount) 
      Debug.Print vItem 
   Next iArrayCount 
End Sub 

For Each - Next

You can iterate through the elements of an array using a For Each - Next loop.
The data type of vItem must be Variant.

Public Sub ForEachLoop() 
Dim arValues As Variant 
Dim vItem As Variant 
   arValues = Array(1, 2, 3, 4, 5) 

   For Each vItem In arValues 
      Debug.Print vItem 
   Next vItem 
End Sub 

For - Next (Arrays)

You can iterate through the elements of an array using a For - Next loop

Run 5 Times

You can use a For - Next loop but you need to find out how many elements are in the array.

Public Sub ForNextLoop1() 
Dim arValues As Variant 
Dim sItem As String 
Dim iarraycount As Integer 
   arValues = Array("a", "b", "c", "d", "e") 
   
   For iarrayCount = 0 To 4 
      sItem = arValues(iarrayCount) 
      Debug.Print sItem 
   Next iarraycount 
End Sub 

The UBOUND function can be used to return the number of items in a given dimension.

Public Sub ForNextLoop1() 
Dim arValues As Variant 
Dim vItem As Variant 
Dim iarraycount As Integer 
   arValues = Array("a", "b", "c", "d", "e", "f", "g") 
   
   For iarraycount = 0 To UBound(arValues) 
      vItem = arValues(iarraycount) 
      Debug.Print vItem 
   Next iarraycount 
End Sub 

Multi Dimensional Array

This example will loop through both the dimensions.
The array is populated using the ARRAY function.
This uses part of the array and passes in 1 as the dimension.

Public Sub ForNextLoop2() 
Dim arValues As Variant 
Dim vItem As Variant 
Dim iArrayCount1 As Integer 
Dim iArrayCount2 As Integer 
   arValues = Array(Array(1, 2), Array(3, 4), Array(5, 6), Array(7, 8)) 
   
   For iArrayCount1 = 0 To UBound(arValues, 1) 
      For iArrayCount2 = 0 To UBound(arValues(0), 1) 
         vItem = arValues(iArrayCount1)(iArrayCount2) 
         Debug.Print vItem 
      Next iArrayCount2 
   Next iArrayCount1 
End Sub 

This array is populated manually.
This uses the whole array and passes in 2 as the dimension.

Public Sub ForNextLoop3() 
Dim arValues As Variant 
Dim vItem As Variant 
Dim iArrayCount1 As Integer 
Dim iArrayCount2 As Integer 
   
   ReDim arValues(0 To 3, 0 To 1) 
   arValues(0, 0) = 1 
   arValues(0, 1) = 2 
   arValues(1, 0) = 3 
   arValues(1, 1) = 4 
   arValues(2, 0) = 5 
   arValues(2, 1) = 6 
   arValues(3, 0) = 7 
   arValues(3, 1) = 8 
   
   For iArrayCount1 = 0 To UBound(arValues, 1) 
      For iArrayCount2 = 0 To UBound(arValues, 2) 
         vItem = arValues(iArrayCount1, iArrayCount2) 
         Debug.Print vItem 
      Next iArrayCount2 
   Next iArrayCount1 
End Sub 

For Each - Next (Arrays)

You can iterate through the elements of an array using a For Each - Next loop
The data type of vItem must always be Variant.
When you use this type of loop all the items are Read Only.
This type of loop cannot be used to update any values in the array.

Fixed Array - Read Only

Declaring a Variant array (arValues).

Public Sub ForEachLoop1() 
Dim arValues As Variant 
Dim vItem As Variant 
   arValues = Array(1, 2, 3, 4, 5) 
   For Each vItem In arValues 
      Debug.Print vItem 
   Next vItem 
End Sub 

Declaring a String array (asValues).
The data type of vItem must always be Variant.

Public Sub ForEachLoop2() 
Dim asValues() As String 
Dim vItem As Variant 
   asValues = VBA.Split("one,two,three,four", ",") 
   For Each vItem In asValues 
      Debug.Print vItem 
   Next vItem 
End Sub 

Multi Dimensional Array

This declares a 2-dimensional array and iterates through the elements one dimension at a time.

Public Sub ForEachLoop3() 
Dim arValues As Variant 
Dim vItem As Variant 
   ReDim arValues(0 To 3, 0 To 1) 
   arValues(0, 0) = 1 
   arValues(0, 1) = 2 
   arValues(1, 0) = 3 
   arValues(1, 1) = 4 
   arValues(2, 0) = 5 
   arValues(2, 1) = 6 
   arValues(3, 0) = 7 
   arValues(3, 1) = 8 
   
   For Each vItem In arValues 
      Debug.Print vItem 
   Next vItem 
End Sub