How to Use for Each on an Array Using VBA

There are two types of For loops in VBA:

1. For Each Loop
2. For To Next Loop

This article describes using a For Each loop on an array. An example related to the For To Next loop is also provided in the article.

Use For Each on an Array Using VBA

Example 1:

To iterate a For Each loop on an array, we can use the following syntax:

Public Sub sampleProgram()
    Dim arr(7) As String
    Dim element As Variant

    For Each element In arr
        func1 element
    Next element
End Sub

Sub func1(ByVal ele As String)
End Sub

The code may seem overwhelming; however, it has been described below to help you understand each statement precisely.

It is essential to declare the array first to iterate using a For Each loop over an array. You can declare an array of any data type; for this example, the array arr has been declared a string with 7 elements.

For the array to work as an iterator for For Each, the variable element is declared a Variant type. The element variable is then used as an iterator that takes the value from the array arr and iterates over the array.

Within the loop, a subroutine func1() is used, which takes the iterating element of the array arr as a parameter. The element is of the Variant data type because it acts as an iterator for the For Each loop.

When element is passed to the func1() as a parameter, it must be made sure that func1() accepts a Variant type parameter. For this, two things can be done:

1. Make a subroutine that takes the parameter by value, as defined in the example above, ByVal ele As String.
2. Otherwise, convert the Variant to a String data type before passing it to the subroutine. Here is the code snippet for converting the element to a String data type:

func1 CStr(element)

The func1() in the code example is a subroutine called in the For Each loop over each array value. As a dummy, this subroutine has been kept empty for the user.

It can be populated with the work that is required to be done. Otherwise, the subroutine call can also be omitted in the For Each loop.

Example 2:

The second example discusses the For To Next loop as an alternative to the For Each loop discussed above.

The For Each loop is structured around the collection of objects. As discussed in the first example, a Variant data type variable or an object is used as an iterator for the loop.

In addition, if a function is used, it must accept Variant type parameters.

The For To Next loop works differently.; it makes use of the upper and lower bounds of the array. In addition, an Integer type variable is used to iterate the loop, which can also be used to access every element of the array.

Here is a code snippet:

Public Sub sampleProgram()
    Dim arr(7) As String
    Dim i As Long

    For i = LBound(arr) To UBound(arr)
        func1 arr(i)
    Next i
End Sub

An array has been declared in the sampleProgram() as arr of data type String. An iterator i has been declared of data type Long to iterate through the array.

As discussed earlier, the array’s lower and upper bounds are calculated using specific functions. The For To Next loop iterates for the defined number of times and calls the subroutine func1() on each iteration.

The value at the specific array element arr(i) is passed to func1() as a parameter; hence, there will be no issues with the Variant data type.