using a formula for a column vs. looping over each row - speed difference?

[Lars Uffmann]

Hey again,

Does anyone have an idea what the speed differences are between for example:

Dim lastRow As Long
lastRow = Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

' (oldWs and newWs are WorkSheets)

' Method 1
oldWs.Range("B2:B" & lastRow).Formula = "If (RC[-1]=""yes"", 1, 0)"
newWs.Columns(1).Value = oldWs.Columns(1).Value

' Method 2
Dim i As Long
For i = 2 To lastRow
If (oldWs.Cells(i, 1).Value = "yes") Then
newWs.Cells(i, 1).Value = 1
Else
newWs.Cells(i, 1).Value = 0
End If
Next i

In theory, I'd see Method 1 as doing a redundant operation by
calculating the values into a new (later obsolete) column of the old
sheet, then copying the values of that, while Method 2 directly
calculates the values of the final column. However, I don't know how
optimized operations on Ranges are in Excel, so can someone give me an
input on which Method would be faster on large datasets, and if Method 1
is faster then by how much? Thanks!

Lars

 

[Lars Uffmann]

just did a small benchmark myself with a nested if-loop as function and
did the same operation 100 times on 6321 lines:
Method 1: 23 seconds
Method 2: 34 seconds

I guess there is quite some optimisation in Excel Formula and Copy
operations... Let's see how it fares vs. my loop when I need to
calculate more fields per row

 

[Bob Phillips]

Anywhere you can remove a loop, as long as that loop is not a trivial number
of iterations, you will score.Even calling back to Excel is better as the
Excel functions are extremely optimised.

 

[Lars Uffmann]

Let's see how it fares vs. my loop when I need to
calculate more fields per row

Hmm - still Excel optimisation wins. Is there a way to directly
calculate results into a destination column, without putting the formula
there?

Like to change this:
' Method 1
oldWs.Range("B2:B" & lastRow).Formula = "If (RC[-1]=""yes"", 1, 0)"
newWs.Columns(1).Value = oldWs.Columns(1).Value


into something like this?:
newWs.Columns(1).Value = _
ExcelFormulaCalculation ("If (RC[1]=""yes"", 1, 0)"


Best Regards,

Lars

 

[RB Smissaert]

Looping through a range is very slow compared to looping through an array.
Even though the range will has to be assigned to the array and vice-versa
after doing
the manipulations it will still win hands down.
Haven't compared with a sheet formula approach, but my guess is that the
array method will win.

Just compare TestRange and TestArray:

Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub TestRange()

Dim i As Long
Dim lastRow As Long
Dim oldWs As Worksheet
Dim newWs As Worksheet

Set oldWs = Sheets(1)
Set newWs = Sheets(2)

lastRow = 10000

StartSW

For i = 2 To lastRow
If (oldWs.Cells(i, 1).Value = "yes") Then
newWs.Cells(i, 1).Value = 1
Else
newWs.Cells(i, 1).Value = 0
End If
Next i

StopSW , "doing range"

End Sub

Sub TestArray()

Dim i As Long
Dim lastRow As Long
Dim arr1
Dim arr2
Dim oldWs As Worksheet
Dim newWs As Worksheet

Set oldWs = Sheets(1)
Set newWs = Sheets(2)

lastRow = 10000

StartSW

With oldWs
arr1 = Range(.Cells(2, 1), .Cells(lastRow, 1))
End With

ReDim arr2(1 To UBound(arr1), 1 To 1)

For i = 1 To UBound(arr1)
If (arr1(i, 1) = "yes") Then
arr2(i, 1) = 1
Else
arr2(i, 1) = 0
End If
Next i

With newWs
Range(.Cells(2, 1), .Cells(lastRow, 1)) = arr2
End With

StopSW , "doing array"

End Sub

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime > lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime > lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function


RBS