Matching values in string array

arrays, data-structures, excel, vba

Solution

If we're going to talk about performance then there's no substutute for running some tests. In my experience Application.Match() is up to ten times slower than calling a function which uses a loop.

Sub Tester()

    Dim i As Long, b, t
    Dim arr(1 To 100) As String

    For i = 1 To 100
        arr(i) = "Value_" & i
    Next i

    t = Timer
    For i = 1 To 100000
        b = Contains(arr, "Value_50")
    Next i
    Debug.Print "Contains", Timer - t

    t = Timer
    For i = 1 To 100000
        b = Application.Match(arr, "Value_50", False)
    Next i
    Debug.Print "Match", Timer - t

End Sub


Function Contains(arr, v) As Boolean
Dim rv As Boolean, lb As Long, ub As Long, i As Long
    lb = LBound(arr)
    ub = UBound(arr)
    For i = lb To ub
        If arr(i) = v Then
            rv = True
            Exit For
        End If
    Next i
    Contains = rv
End Function

Output:

Contains       0.8710938 
Match          4.210938

Problem

Problem: Looking for a more efficient way of finding whether there is an exact matching value in a 1d array -- essentially a boolean `true/false`. Am I overlooking something obvious? Or am I simply using the wrong data structure, by using an array when I probably should be using a collection object or a dictionary? In the latter I could check the `.Contains` or `.Exists` method, respectively In Excel I can check for a value in a vector array like: ``` If Not IsError(Application.Match(strSearch, varToSearch, False)) Then ' Do stuff End If ``` This returns an exact match index, obviously subject to limitations of `Match` function which only finds the first matching value in this context. This is a commonly used method, and one that I have been using for a long time, too. This is satisfactory enough for Excel -- but what about other applications? In other applications, I can do basically the same thing but requires enabling reference to the Excel object library, and then: ``` If Not IsError(Excel.Application.match(...)) ``` That seems silly, though, and is difficult to manage on distributed files because of permissions/trust center/etc. I have tried to use the Filter() function: ``` If Not Ubound(Filter(varToSearch, strSearch)) = -1 Then 'do stuff End If ``` But the problem with this approach is that `Filter` returns an array of partial matches, rather than an array of exact matches. (I have no idea why it would be useful to return substring/partial matches.) The other alternative is to literally iterate over each value in the array (this also is very commonly used I think) -- which seems even more needlessly cumbersome than calling on Excel's `Match` function. ``` For each v in vArray If v = strSearch Then ' do stuff End If Next ```

Original source