excel - Are there any advantages to use `Application.Match` on a VBA array instead of looping over it when you only care if an e
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, ByVal 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, ByVal 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 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 117 0.00% 12 us
1 Initialization Completed 1 91 0.00% 9100 ns
2 IsInArrayWithMatch completed 1 173,474,249 99.90% 17.3 s
3 IsInArrayWithLoop completed 1 168,355 0.10% 17 ms
TOTAL 4 173,642,812 100.00% 17.4 s
Total time recorded: 17.4 s
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?
EDIT: Additionally, Application.Match doesn't always work well with Dates, so that seems like another reason to use a loop with an equality check as well:
Dim MyDate As Date
MyDate = #12/15/2024#
Dim SomeDates(1 To 3) As Date
SomeDates(1) = #12/6/2024#
SomeDates(2) = #12/15/2024#
SomeDates(3) = #12/22/2024#
Debug.Assert IsInArrayWithMatch(MyDate, SomeDates) 'Should return true, but returns false if your local computer configurations for short dates is not mm/dd/yyyy.
Debug.Assert IsInArrayWithMatch(CStr(MyDate), SomeDates) 'Converting the date using the default VBA conversion function will fix the issue, however that is still a downside of using Application.Match in this case.
EDIT2:
Regarding Application.Match being faster than looping when dealing with a range, the following results don't seem to indicate that.
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.
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, ByVal 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, ByVal 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 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 117 0.00% 12 us
1 Initialization Completed 1 91 0.00% 9100 ns
2 IsInArrayWithMatch completed 1 173,474,249 99.90% 17.3 s
3 IsInArrayWithLoop completed 1 168,355 0.10% 17 ms
TOTAL 4 173,642,812 100.00% 17.4 s
Total time recorded: 17.4 s
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?
EDIT: Additionally, Application.Match doesn't always work well with Dates, so that seems like another reason to use a loop with an equality check as well:
Dim MyDate As Date
MyDate = #12/15/2024#
Dim SomeDates(1 To 3) As Date
SomeDates(1) = #12/6/2024#
SomeDates(2) = #12/15/2024#
SomeDates(3) = #12/22/2024#
Debug.Assert IsInArrayWithMatch(MyDate, SomeDates) 'Should return true, but returns false if your local computer configurations for short dates is not mm/dd/yyyy.
Debug.Assert IsInArrayWithMatch(CStr(MyDate), SomeDates) 'Converting the date using the default VBA conversion function will fix the issue, however that is still a downside of using Application.Match in this case.
EDIT2:
Regarding Application.Match being faster than looping when dealing with a range, the following results don't seem to indicate that.
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.
Share Improve this question edited 2 hours ago T.M. 9,9283 gold badges37 silver badges59 bronze badges asked yesterday DecimalTurnDecimalTurn 4,0673 gold badges18 silver badges42 bronze badges 10 | Show 5 more comments1 Answer
Reset to default 1For 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 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.
- 谷歌继续封死华为后路，Mate 30无法安装谷歌服务
- Computex 2012：Windows 8将怎样改变PC行业
- linux - How to use GNU ld with rustc-compiled obj file without cargo? - Stack Overflow
- powerbi - I have two table Fact table and Dimention table and I am trying to calculate Past year sales .Value its not working -
- angularjs - Laravel Custom Auth in registering - Stack Overflow
- python - Implementing multiple live streams at the same time using aiortc - WebRTC - Stack Overflow
- tomcat9 - guacd WebSocket Tunnel Timeout with "Support for protocol 'vnc' is not installed" Er
- vuejs3 - Vue 3 Composition API data() - Generic idea required - Stack Overflow
- c - How to properly implement HKDF Expand with openssl EVP_KDF - Stack Overflow
- react native - Views are still shifted when the keyboard opens, even though they are not wrapped in a KeyboardAvoidingView - Sta
- Symfony 7 - Autocomplete form field - Stack Overflow
- android - OneSignal Not Subscribing Users - Stack Overflow
- android - How call Authenticator.authenticate with custom respond in okhttp? - Stack Overflow
- wordpress - XML Reader Not Found in cPANEL php v8.3 - Stack Overflow
- innodb - How does MySQL handle lock queuing order for SELECT ... FOR UPDATE? - Stack Overflow
- How to compare dates in vba for excel - Stack Overflow
- reactjs - How to render text and list from markdown frontmatter on Next project? - Stack Overflow
Matchcan be less code (you can just write one line e.g.If IsError(Application.Match(valToTest, arrayHere,0)), otherwise use a loop. Related: stackoverflow.com/questions/18754096/… – Tim Williams Commented yesterdayDebug.Print Application.Match(MyDate, SomeDates, 0)gives me2....Matchagainst a worksheet list of dates has problems unless you wrap the search value inCLng()– Tim Williams Commented yesterdayError 2042when running that line. I think this could have to do with local configurations and how dates are converted to strings. What do you get if you type?#12/15/2024#in the Immediate Window? Personnally, I get2024-12-15which matches with my Regional Formats. – DecimalTurn Commented yesterday