Hi Bart,

My earlier tests compare with yours (see below), though I hadn't tested the
Long array as can't assign that to the input range in a UDF. The tests were
geared towards for use in a UDF.

'Relative' time differences will vary according to the inner loop (input
range size) and outer loop (contrived to increase time), and decrease with
the more other work that's going on.

The tests demonstrate it's significantly faster to assign range values to a
variant array (already well known) and somewhat faster to loop the array
with For..Each vs For...To..Next. However the overall difference might not
be noticeable in practice and there could be other good reasons to loop
For..To..Next.

Simple to implement, just run Setup. The timer is a bit crude but the
overhead is irrelevant here.

Option Explicit
Public Declare Function GetTickCount Lib "kernel32" () As Long
Sub Setup()

Range("C1:C5").Value = Application.Transpose(Array(0, 1, 2, 3, 4))
Range("A1:A1000").Value = 123.456
Range("E1:E5").Formula = "=foo($A$1:$A$1000,C1)"

' entering above formula triggers a triple re-calc

' manually change A1 or do a recalc, see results in cells

End Sub

Function foo(rng As Range, d As Long)
' returns calculation time (ms) & description of loop method
Dim arr
Dim i As Long, a As Long, t As Long
Dim dbl As Double
Dim s As String
Dim v
Dim cell As Range

On Error GoTo errH
If rng.Count = 1 Or (rng.Rows.Count 1 And _
rng.Columns.Count 1) Then
foo = CVErr(xlErrRef)
Exit Function
End If

t = GetTickCount

For a = 1 To 100 ' change this
If d < 2 Then
arr = rng
If d = 0 Then
For Each v In arr
dbl = dbl + v
Next
ElseIf d = 1 Then
For i = 1 To UBound(arr)
dbl = dbl + arr(i, 1)
Next
End If
End If
If d = 2 Then
For Each cell In rng
dbl = dbl + cell.Value
Next
ElseIf d = 3 Then
For i = 1 To rng.Rows.Count
dbl = dbl + rng(i, 1).Value
Next

ElseIf d = 4 Then
For i = 1 To rng.Rows.Count
dbl = dbl + rng.Rows(i)(1).Value
Next
End If

Next
'foo = dbl '

t = GetTickCount - t

If d = 0 Then
s = " For...Each variant"
ElseIf d = 1 Then
s = " For...To variant.count"
ElseIf d = 2 Then
s = " For...Each cell in range"
ElseIf d = 3 Then
s = " For...To rng.count, rng(i, 1)"
ElseIf d = 4 Then
s = " For...To rng.count, rng.Rows(i)(1)"
End If

foo = t & s
Debug.Print t & s, , Application.Caller.Address(0, 0)
Exit Function
errH:

foo = CVErr(xlErrValue)
End Function

Regards,
Peter T

"RB Smissaert" wrote in message
...
Not sure where I got it from but I always thought that for arrays For Next
was faster
than For Each, but it looks this is not true:

<snip