In Arrays, For Each...Next vs For...Next

[ADezii]

If you want to visit each item in an Array, you have two alternatives:

1. Use a For Each..Next loop, using a Variant to retrieve each value in turn.
2. Use a For...Next loop, looping from the Lower Bound to the Upper Bound of the Array.

For Each...Next seems simpler because you need not worry about retrieving the Lower and Upper Bounds- the loop takes care of that for you.

For Each varValue In alngValues
    j = varValue
Next varValue

Using For...Next requires a bit more effort on your part because you must write the code that finds the Lower and Upper Bounds.

For lngCount = LBound(alngValues) To UBound(alngValues)
    j = alngValues(lngCount)
Next lngCount

You might think that the For Each...Next would be faster, because it requires less code but that isn't so. The For...Next loop will give you better performance if you're working with Arrays. In tests, the faster version took about 70% as long as the slower version. Here is the actual code that will prove my point. Take special note of the timeGetTime() API Function to act as the Stopwatch:

'First, the API Declaration
Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Public Function fForEach_Next(lngRepeats As Long) As Long
'Slower version of code using For Each...Next to process Array Elements
 
Dim alngValues(1 To 1000) As Long
Dim varValue As Variant, i As Long, j As Long
Dim lngStartTime As Long
 
For i = 1 To 1000
  alngValues(i) = i
Next i
 
lngStartTime = timeGetTime()
For i = 1 To lngRepeats
  For Each varValue In alngValues
    j = varValue
  Next varValue
Next i
fForEach_Next = (timeGetTime() - lngStartTime)
End Function

Public Function fFor_Next(lngRepeats As Long) As Long
'Faster version of code using For...Next to process Array Elements
 
Dim alngValues(1 To 1000) As Long
Dim lngCount As Long, i As Long, j As Long, T As Long
Dim lngStartTime As Long
 
For i = 1 To 1000
  alngValues(i) = i
Next i
 
lngStartTime = timeGetTime()
For i = 1 To lngRepeats
  For lngCount = LBound(alngValues) To UBound(alngValues)
    j = alngValues(lngCount)
  Next lngCount
Next i
fFor_Next = (timeGetTime() - lngStartTime)
End Function

WARNING! - Although you can use either of these techniques to read items from an Array, you can only use the For...Next loop to write into Array elements. The For Each...Next loop retrieves a copy of the data in the Array, not the actual Array element itself. Although you won't receive an Error if you use For Each...Next to write into an Array, the data will not actually go into the Array.