PDA

View Full Version : Sleeper: Evaluate method & For Loop: which on is faster



levanduyet
05-13-2010, 07:26 PM
Dear All,
We are testing to compare between Evaluate & For Loop which method is faster as following:

1) Test Evaluate:


Sub Test1()
Dim TG As Double
TG = Timer
[A:A] = [ROW(R:R)]
[E1] = Format(Timer - TG, "0.000000000")
End Sub

2) Test For Loop:


Sub Test2()
Dim i As Long, Arr() As Long, TG As Double
TG = Timer
ReDim Arr(1 To Cells.Rows.Count, 1 To 1)
For i = 1 To Cells.Rows.Count
Arr(i, 1) = i
Next
Range("C:C").Value = Arr
[G1] = Format(Timer - TG, "0.000000000")
End Sub

It look like Evaluate method is slower For loop.
Anyone has any comments.

Tks,

LVD

Blade Hunter
05-13-2010, 09:51 PM
They are completely different.

the first is evaluating the Row number per row
the second just posts the iteration of a loop

To make it a fare comparison change your code to this:



Sub Test3()
Dim i As Long, Arr() As Long, TG As Double
TG = Timer
ReDim Arr(1 To Cells.Rows.Count, 1 To 1)
For i = 1 To Cells.Rows.Count
Arr(i, 1) = Cells(i, 1).Row
Next
Range("C:C").Value = Arr
[G1] = Format(Timer - TG, "0.000000000")
End Sub


This also evaluates the row number.

Method 1 is as expected about 3 - 4 times the speed.

levanduyet
05-13-2010, 11:47 PM
Hi,

Can we do another way with the sub Test1 ?
Something like:


[A:A] = [Range("A:A")]


LVD