AW: Der große Benchmarktest...
31.05.2007 15:55:17
Matthias
...mit den drei Lösungsmöglichkeiten:
'Variante1: Schleifendurchlauf, bis Zelle gefunden wurde:
Function Variante1(wert As Long) As Long
Dim r As Range
For Each r In Range("A:A")
If r.Value = wert Then
Variante1 = r.Row
Exit Function
End If
Next
End Function
'Variante2: Tabellenfunktion VERGLEICH():
Function Variante2(wert As Long) As Long
Dim z As Long
On Error Resume Next 'z bleibt 0, wenn Wert nicht gefunden wird
z = WorksheetFunction.Match(wert, Columns(1), 0)
Variante2 = z
End Function
'Variante3: Find-Methode:
Function Variante3(wert As Long) As Long
Dim Found As Range
Dim LoLetzte As Long
LoLetzte = IIf(IsEmpty(Range("A65536")), Range("A65536").End(xlUp).Row, 65536)
Set Found = Range("A1:A" & LoLetzte).Find(wert, Range("A" & LoLetzte), , xlWhole, , _
xlNext)
If Found Is Nothing Then Exit Function
Variante3 = Found.Rows
End Function
Function Benchmark()
Const anz = 1000 'Anzahl der Schleifendurchläufe
Const findein = 500 'gesuchten Wert in Zeile ... setzen
Dim i As Long, z As Long
Dim t1 As Single, t2 As Double
Columns(1).ClearContents
Cells(findein, 1) = 914410
t1 = Timer
For i = 1 To anz
z = Variante1(914410)
Next i
t2 = (Timer - t1) / anz * 1000
Debug.Print "Variante1: " & Round(t2, 2) & " ms"
t1 = Timer
For i = 1 To anz
z = Variante2(914410)
Next i
t2 = (Timer - t1) / anz * 1000
Debug.Print "Variante2: " & Round(t2, 2) & " ms"
t1 = Timer
For i = 1 To anz
z = Variante3(914410)
Next i
t2 = (Timer - t1) / anz * 1000
Debug.Print "Variante3: " & Round(t2, 2) & " ms"
End Function
Viel Spaß,
Matthias