Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a formula for a column vs. looping over each row - speed difference?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a formula for a column vs. looping over each row - speeddifference?
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 :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a formula for a column vs. looping over each row - speed difference?
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. -- __________________________________ HTH Bob "Lars Uffmann" wrote in message ... 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 :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a formula for a column vs. looping over each row - speeddifference?
Lars Uffmann wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a formula for a column vs. looping over each row - speed difference?
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 "Lars Uffmann" wrote in message ... 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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct against worksheet vs named range- any speed difference? | Excel Worksheet Functions | |||
charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel | |||
Inexplicable difference in row hiding speed - identical code, identical machines! | Excel Programming | |||
looping through ranges: how to speed up? | Excel Programming | |||
looping from one column to the next | Excel Programming |