When I want to check if a certain value is present in a VBA array (in Excel), I was used to take advantage of the Application.Match function to create a simple IsInArrayWithMatch function:
Function IsInArrayWithMatch(ByVal Val As Variant, ByRef MyArray As Variant)
IsInArrayWithMatch = Not IsError(Application.Match(Val, MyArray, 0))
End Function
However, I wanted to compare this approach with a simple loop over the array to make sure that this was actually the optimal method, so I created IsInArrayWithLoop like so:
Function IsInArrayWithLoop(ByVal Val As Variant, ByRef MyArray As Variant) As Variant
If Not IsArray(MyArray) Then
IsInArrayWithLoop = CVErr(xlErrValue)
End If
Dim i As Long
For i = LBound(MyArray) To UBound(MyArray)
If Val = MyArray(i) Then
IsInArrayWithLoop = True
Exit Function
End If
Next
End Function
Then, I wrote a simple benchmark using VBA-Benchmark:
Sub Benchmark_IsInArray()
Dim Bm As New cBenchmark
Bm.Start
Bm.TrackByName "Start"
Dim i As Long
Dim MyArray As Variant
Const MaxRep = 10000
MyArray = Array(1, 2, 3, 4, 5)
Dim Temp As Variant
Bm.TrackByName "Initialization completed"
For i = 1 To MaxRep
Temp = IsInArrayWithMatch(3, MyArray)
Next i
Bm.TrackByName "IsInArrayWithMatch completed"
For i = 1 To MaxRep
Temp = IsInArrayWithLoop(3, MyArray)
Next i
Bm.TrackByName "IsInArrayWithLoop completed"
Bm.Report
End Sub
And got the following results:
IDnr Name Count Sum of tics Percentage Time sum
0 Start 1 128 0.00% 13 us
1 Initialization completed 1 75 0.00% 7500 ns
2 IsInArrayWithMatch completed 1 5,842,539 99.61% 584 ms
3 IsInArrayWithLoop completed 1 22,558 0.38% 2.26 ms
TOTAL 4 5,865,300 100.00% 587 ms
Total time recorded: 587 ms
This seems to indicate that looping over the array directly can be roughly to 100x faster than using the Application.Match function.
Knowing that, is there an example or reason where to use
Application.Matchin general over a simple loop?
EDIT1: Since I changed the scope of the question to be only about performance to avoid opinion-based responses, I moved the discussion about Dates to a seperate question.
EDIT2:
Regarding Application.Match being faster than looping when dealing with a range, the following results don't seem to indicate that.
(Note that to handle 2D array, we need to replace Val = MyArray(i) with Val = MyArray(i,1)
Benchmarking code:
Private wb As Workbook
Sub Benchmark_IsInArray2()
Dim Bm As New cBenchmark
Bm.Start
Bm.TrackByName "Start"
If wb Is Nothing Then
Set wb = Workbooks.Add
End If
Dim ws As Worksheet
Set ws = wb.Sheets(1)
Dim rng As Range
Set rng = ws.Cells(1, 1).Resize(5, 1)
rng.Value2 = Application.Transpose(Array(1, 2, 3, 4, 5))
Dim i As Long
Const MaxRep = 10000
Dim Temp As Variant
Bm.TrackByName "Initialization completed"
For i = 1 To MaxRep
Temp = IsInArrayWithMatch(3, rng)
Next i
Bm.TrackByName "IsInArrayWithMatch completed"
For i = 1 To MaxRep
Temp = IsInArrayWithLoop(3, rng.Value2)
Next i
Bm.TrackByName "IsInArrayWithLoop completed"
Bm.Report
End Sub
IDnr Name Count Sum of tics Percentage Time sum
0 Start 1 73 0.00% 7300 ns
1 Initialization completed 1 4,565,847 30.55% 457 ms
2 IsInArrayWithMatch completed 1 10,012,531 66.99% 1 s
3 IsInArrayWithLoop completed 1 368,770 2.47% 37 ms
TOTAL 4 14,947,221 100.00% 1.49 s
Total time recorded: 1.49 s
Here, the method using a loop is still roughly 30x faster than using Match.
For best VBA-Excel performance you need to minimize the number and size of data transfers between VBA and Excel. If the array is already present in VBA then using .MATCH will be slow because it requires using the VBA to Excel API to pass and convert a copy of the variant array to the .MATCH function. Looping the variant array avoids this step. If the VBA array is actually a large Range object it is faster to use .MATCH because then the Excel data pointed to by the Range object does not need to be copied and converted to a VBA variant array.