MATCH vs FIND vs Variant Array VBA Performance Shootout in Excel 2010

When searching unsorted data in VBA I have always tended to use MATCH rather than FIND, mainly because early versions of Excel before Excel 2003 did not allow you to use FIND in UDFs.

But prompted by a discussion in one of the Excel forums I thought it was about time I revisited Find and Match to see which performs better in Excel 2010, and for good measure lets compare them to getting the data into a variant array and looping through the array.

Generating Test Data

Andreas Killer came up with a nice routine for generating test data:

Sub Init()
Dim Data(1 To 100000, 1 To 2)
Dim i As Long
Rnd -5
For i = 1 To UBound(Data)
If Rnd > 0.5 Then Data(i, 1) = "X"
If Rnd > 0.5 Then Data(i, 2) = "Y"
Next
Cells.ClearContents
Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
End Sub

This code randomly generates X in column 1 and Y in column 2 in a range of 100 thousand rows, with the number of Xs and Ys controlled by the constant 0.5
Changing the 0.5 to 0.9 will give few Xs and Ys, and changing it to 0.001 will give lots.

Since the X and the Y are using different random numbers there will be rows with X but no Y and Y but no X, as well as rows with both X and Y.

This makes it easy to test how the various methods compare with different densities of data.

Timing

I am using the MicroTimer high-resolution timer API to give timing accuracy at the microsecond (millionths of a second) level, but the timing results will all be in milliseconds.

#If VBA7 Then
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Public Function MicroTimer() As Double
'
' returns seconds
' uses Windows API calls to the high resolution timer
'
Dim cyTicks1 As Currency
Dim cyTicks2 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
'
' get frequency
'
If cyFrequency = 0 Then getFrequency cyFrequency
'
' get ticks
'
getTickCount cyTicks1
getTickCount cyTicks2
If cyTicks2 < cyTicks1 Then cyTicks2 = cyTicks1
'
' calc seconds
'
If cyFrequency Then MicroTimer = cyTicks2 / cyFrequency
End Function

The code uses conditional compilation for the Windows API calls so that it will work for both Excel 2010 32-bit and 64-bit Excel.

Using FIND

Here is the test code using Range.Find


Sub FindXY1()
Dim oRng As Range
Dim n As Long
Dim dTime As Double
Dim FirstAddress As String
dTime = MicroTimer
'
With Range("a1:A100000")
Set oRng = .Find("X", , xlValues, , , xlNext, False)
If Not oRng Is Nothing Then
FirstAddress = oRng.Address
If oRng.Offset(0, 1) = "Y" Then n = n + 1
Do
Set oRng = .FindNext(oRng)
If Not oRng Is Nothing Then
If oRng.Offset(0, 1) = "Y" And oRng.Address <> FirstAddress Then
n = n + 1
End If
End If
Loop While Not oRng Is Nothing And oRng.Address <> FirstAddress
End If
End With
'
Debug.Print "Find " & n & " " & (MicroTimer - dTime) * 1000
End Sub

The Sub does a Range.Find on the 100 thousand rows looking for X, and then checks if column 2 for that cell also contains Y.
Then this is repeated using FindNext until we have looped back to the first range address.
When completed the number of rows with both X and Y (this is less than the number of times an X was found) and the time in milliseconds is shown in the Immmediate window.

Using Match

Here is the code using WorksheetFunction.MATCH

Sub FindXY2()
Dim oRng As Range
Dim j As Long
Dim n As Long
Dim dTime As Double
dTime = MicroTimer
Set oRng = Range("a1:A100000")
On Error GoTo Finish
With Application.WorksheetFunction
Do
j = .Match("X", oRng, 0)
If oRng(j, 2).Value2 = "Y" Then n = n + 1
Set oRng = oRng.Resize(oRng.Rows.Count - j, 1).Offset(j, 0)
Loop
End With
Finish:
Debug.Print "Match " & n & " " & (MicroTimer - dTime) * 1000
End Sub

This Sub works by using Worksheetfunction.Match to find the first occurrence of X within the Range object oRng.
After each X is found oRng is resized to exclude the range already searched.

The loop terminates either when nothing is found (Worksheetfunction.Match raises an error)  or the Resize fails, also raising an error.

Using a Variant Array

Sub FindXY3()
Dim vArr As Variant
Dim j As Long
Dim n As Long
Dim dTime As Double
dTime = MicroTimer
vArr = Range("a1:B100000").Value2
For j = LBound(vArr) To UBound(vArr)
If vArr(j, 1) = "X" Then
If vArr(j, 2) = "Y" Then
n = n + 1
End If
End If
Next j
Debug.Print "Var array " & n & " " & (MicroTimer - dTime) * 1000
End Sub

The code gets the Range into a Variant, creating a 2-dimensional array, and then loops down the array looking for X and Y.

Timing Results

I ran each sub 4 times using different densities of data.

The first is with a single XY pair at row number 100000. This tests the scanning speed per row rather than the calling overhead.

Then I used the Test data generator with constant values of 0.9, 0.5 and 0.001.

Here are the results giving timings in Milliseconds with counts of the XY pairs:

And here are the ratios of the times:

  • You can see that Find is significantly slower than Match or using a Variant array, regardless of the number of XY pairs found.
  • The timings for Match increase fast with the number of XY pairs, whereas the Variant array increases much more slowly.
  • This is because there is a much higher overhead for each call to Match than looping from row to row of the array.
  • For small numbers of XY pairs Match is faster than the Variant array.

Conclusions

  • Don’t use Range.Find unless you want to search a large number of columns for the same thing (you would have to do a Match for each column).
  • The Variant array approach is surprisingly effective, particularly when you expect a large number of hits.
  • Match wins easily for a small number of hits.

OK, so who is going to admit to using Range.Find?

About these ads
This entry was posted in VBA. Bookmark the permalink.

13 Responses to MATCH vs FIND vs Variant Array VBA Performance Shootout in Excel 2010

  1. I use Find almost exclusively (until now, that is). I would say that the vast majority of times I use it is on limited, single-column ranges. I will be using Match from now on.

  2. Jon says:

    I’ve found the find function to be overly complex so I never really used from the get go. I usually just use an array (I’ve made functions to deal with using arrays).

  3. Jim Cone says:

    Charles,
    I have some rather long Find code to do a multi sheet, multi word search and it completes generally before the start button rebounds.
    So your test results showing that Match beats Find surprised me.
    I tried your code on 50,000 rows of data and got approximately the same relative results that you did. I am going to continue reading your posts.

    Another surprise was the speed increase when I replaced your range.resize code line with a different range callout.
    This came up about 18% faster…

    ‘— code starts
    Sub FindXY333()
    Dim oRng As Range
    Dim oLastRng As Range
    Dim j As Long
    Dim n As Long
    Dim Rw As Long
    Dim dTime As Long

    dTime = timeGetTime
    Set oRng = Range(“a1:A50000″)
    Set oLastRng = oRng(oRng.Rows.Count)
    Rw = oLastRng.Row
    On Error GoTo Finish
    With Application.WorksheetFunction
    Do
    j = .Match(“X”, oRng, 0)
    If oRng(j, 2).Value2 = “Y” Then n = n + 1
    Set oRng = Range(oRng(j + 1), oLastRng) ‘<<<<= Rw
    End With

    Finish:
    MsgBox TellMeHowLong(dTime) & vbCr & n & ” found”
    End Sub
    ‘— code ends

    There were about 25,000 XY occurances.
    I use the Resize property frequently because of its ease of use.
    Wondering if I still should.

    Jim Cone

  4. Jim Cone says:

    Somebody did me wrong – probably me.
    The code I posted is incomplete. The Do / Loop should look like this…
    ‘—
    With Application.WorksheetFunction
    Do
    j = .Match(“X”, oRng, 0)
    If oRng(j, 2).Value2 = “Y” Then n = n + 1
    Set oRng = Range(oRng(j + 1), oLastRng)
    Loop Until oRng.Row >= Rw
    End With
    ‘—

    • fastexcel says:

      Jim,
      Yes your method is definitely faster:
      Equivalent timings on my system are 1.3, 97, 478, 950

      Had to change the loop a bit to avoid end-condition problems:
      Do
      Set oRng = Range(oRng(j + 1), oLastRng)
      j = .Match("X", oRng, 0)
      jRow = jRow + j
      If oRng(j, 2).Value2 = "Y" Then n = n + 1
      Loop Until jRow + 1 > Rw

  5. sam says:

    I think we have to keep the end application in mind.

    The advantage of Find is that it returns a Range Object.

    So Lets assume you want to delete rows in a table where we find the X,Y pair, or highlight the X,Y rows with red we would do this easily in the end

    oRng.entirerow.delete
    or
    oRng.interior.colorindex = 3

    Also it would be interesting to see how
    Application.WorksheetFunction.CountIf would perform over MATCH

    • Jon says:

      I find the union function works well for that, pretty speeding and I can still avoid the complex find function (that always seems to be buggy – I know, it’s my own user error, but it happens enough that I prefer my own.

  6. sam says:

    Sub FindXY_COUNTIFS()
    Dim j As Long
    Dim dTime As Double
    dTime = MicroTimer
    j = [COUNTIFS(A1:A100000,"x",B1:B100000,"y")]
    Debug.Print “COUNTIFS ” & j & ” ” & (MicroTimer – dTime) * 1000
    End Sub

    below are the timings
    COUNTIFS 25145 – 51.3873271629564
    COUNTIFS 25145 – 51.1837398335047
    COUNTIFS 25145 – 51.3968255727377

  7. jeff Weir says:

    I could be missing the point here, but It seems to me that this post covers the use of FIND in the case where you would want to use it exactly like MATCH: that is to check whether the ENTIRE text in your range/array element exactly matches your search string.

    But FIND does something MATCH does not: tests whether ANY text WITHIN your range/array exactly matches your test string.

    So doesn’t this post only cover the specific situation where you want to use FIND exactly like MATCH i.e. find an entire text string?

    That is –
    with FIND:
    * searching for “Excel Zero” will return a positive hit in “Excel Zero”; or
    * searching for “Zero” will return a positive hit in “Excel Zero”

    With MATCH:
    * searching for “Excel Zero” will return a positive hit in “Excel Zero”; but
    * searching for “Zero” will NOT return a positive hit in “Excel Zero”

    So you can use FIND to emulate all the circumstances in which you might use MATCH. But you cannot use MATCH to emulate all the circumstances in which you might use FIND. Ergo, you cannot say that MATCH is faster than FIND. You can say that MATCH is faster than FIND when you are using FIND to emulate MATCH.

  8. jeff Weir says:

    Sam just pointed out to me that MATCH accepts wildcards…I’d forgotten about that, assuming I ever knew it in the first place. I wonder if using wildcards in Match would change your results at all?

    • fastexcel says:

      I don’t know what using wildcards does to MATCH performance. LIKE in VBA is pretty fast these days (It was slow in early Excel versions). I guess I should do a MATCH vs LIKE vs FIND vs REGEX shootout..

  9. Bryan Metz says:

    Bah, I hate reading articles like this. With loops and type declarations and noncontiguous ranges to contend with when using the variant array method, I want the find method to be faster so I can be lazy!!!

  10. Pingback: VBA searching shootout between WorksheetFunction.Match: Performance Pros and Cons | Excel and UDF Performance Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s