如果我们要谈论的表现则没有substutute运行一些测试。根据我的经验Application.Match（）比调用它使用一个循环功能要慢十倍。

 子仪（）    昏暗我一样长，B，T
    昏暗的ARR（1〜100）作为字符串    对于i = 1到100
        ARR（ⅰ）=VALUE_与＆amp;一世
    接下来，我    T =定时器
    对于i = 1到100000
        B =包含（ARRValue_50）
    接下来，我
    Debug.Print包含，定时器 -  T的    T =定时器
    对于i = 1到100000
        B = Application.Match（ARR，Value_50，FALSE）
    接下来，我
    Debug.Print匹配，定时器 -  T的结束小组
函数包含（ARR，V）为布尔
昏暗RV作为布尔，磅长，UB长，我只要
    磅= LBOUND（ARR）
    UB = UBound函数（ARR）
    对于i =磅为UB
        如果ARR（I）= V，那么
            RV = TRUE
            对于出口
        万一
    接下来，我
    包含= RV
结束功能
 

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

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