如何在数组中搜索字符串?

Is there an easy (one-liner) to search for a string within an array in VBA? Or will I need to loop through each element and compare it with the target string?

在VBA中，在数组中搜索字符串是否容易(一行)?还是需要遍历每个元素并将其与目标字符串进行比较?

EDIT: It is a one-dimensional array. I only need to know IF a string is somewhere in the array.

编辑:它是一个一维数组。我只需要知道字符串是否在数组中的某个位置。

IE:

即:

names(JOHN, BOB, JAMES, PHLLIP)

How do I find out if "JOHN" is in the array, it needs to be minimal as it will be repeated around 5000 times and I don't want the function to slow the overall process down.

我怎么知道“JOHN”是否在数组中，它需要最小化，因为它将被重复大约5000次，我不希望函数减慢整个过程。

8 个解决方案

#1

If you want to know if the string is found in the array at all, try this function:

如果您想知道是否在数组中找到了字符串，请尝试以下函数:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

As Sean Cheshire points out, this must be a 1-D array.

正如Sean Cheshire指出的，这一定是一个一维数组。

Example:

例子:

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print IsInArray("ghi", arr)
End Sub

(Below code updated based on comment from HansUp)

(以下是基于HansUp评论更新的代码)

If you want the index of the matching element in the array, try this:

如果您想要数组中匹配元素的索引，请尝试以下操作:

Function IsInArray(stringToBeFound As String, arr As Variant) As Long
  Dim i As Long
  ' default return value if value not found in array
  IsInArray = -1

  For i = LBound(arr) To UBound(arr)
    If StrComp(stringToBeFound, arr(i), vbTextCompare) = 0 Then
      IsInArray = i
      Exit For
    End If
  Next i
End Function

This also assumes a 1-D array. Keep in mind LBound and UBound are zero-based so an index of 2 means the third element, not the second.

这也假设有一个一维数组。记住LBound和UBound都是零基的，所以2的索引表示第三个元素，而不是第二个元素。

Example:

例子:

Sub Test()
  Dim arr As Variant
  arr = Split("abc,def,ghi,jkl", ",")
  Debug.Print (IsInArray("ghi", arr) > -1)
End Sub

If you have a specific example in mind, please update your question with it, otherwise example code might not apply to your situation.

如果您有一个特定的示例，请用它更新您的问题，否则示例代码可能不适用于您的情况。

#2

Another option would be use a dictionary instead of an array:

另一种选择是使用字典而不是数组:

Dim oNames As Object
Set oNames = CreateObject("Scripting.Dictionary")
'You could if need be create this automatically from an existing Array
'The 1 is just a dummy value, we just want the names as keys
oNames.Add "JOHN", 1
oNames.Add "BOB", 1
oNames.Add "JAMES", 1
oNames.Add "PHILIP", 1

As this would then get you a one-liner of

就像这样，你就有了一句俏皮话

oNames.Exists("JOHN")

The advantage a dictionary provides is exact matching over partial matching from Filter. Say if you have the original list of names in an Array, but were looking for "JO" or "PHIL" who were actually two new people in addition to the four we started with. In this case, Filter(oNAMES, "JO") will match "JOHN" which may not be desired. With a dictionary, it won't.

字典提供的优势是与筛选器的部分匹配完全匹配。假设你在一个数组中有最初的名字列表，但是你在寻找“JO”或“PHIL”，除了我们开始的四个名字之外，他们实际上是两个新的人。在这种情况下，Filter(oNAMES，“JO”)将匹配可能不需要的“JOHN”。有了字典，就不会了。

#3

Another option that enforces exact matching (i.e. no partial matching) would be:

实施精确匹配(即不进行部分匹配)的另一个选项是:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

You can read more about the Match method and its arguments at http://msdn.microsoft.com/en-us/library/office/ff835873(v=office.15).aspx

您可以在http://msdn.microsoft.com/en-us/library/office/ff835873(v=office.15).aspx上阅读更多关于Match方法及其参数的信息

#4

more simple Function whichs works on Apple OS too:

更简单的功能，也适用于苹果操作系统:

Function isInArray(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
For Each element In arr
    If element = stringToBeFound Then
        isInArray = True
        Exit Function
    End If
Next element
End Function

#5

there is a function that will return an array of all the strings found.

有一个函数将返回所有找到的字符串的数组。

Filter(sourcesrray, match[, include[, compare]])
The sourcearray has to be 1 dimensional
The function will return all strings in the array that have the match string in them

过滤器(sourcesrray, match[， include[， compare]])) sourcearray必须是一维的函数将返回数组中包含匹配字符串的所有字符串

#6

Here's another answer. It works fast, reliably (see atomicules' answer) and has compact calling code:

这是另一个答案。它工作快速、可靠(请参阅atomicules的回答)，并且具有紧凑的调用代码:

' Returns true if item is in the array; false otherwise.
Function IsInArray(ar, item$) As Boolean
    Dim delimiter$, list$

    ' Chr(7) is the ASCII 'Bell' Character.
    ' It was chosen for being unlikely to be found in a normal array.
    delimiter = Chr(7)

    ' Create a list string containing all the items in the array separated by the delimiter.
    list = delimiter & Join(ar, delimiter) & delimiter

    IsInArray = InStr(list, delimiter & item & delimiter) > 0
End Function

Sample usage:

示例用法:

Sub test()
    Debug.Print "Is 'A' in the list?", IsInArray(Split("A,B", ","), "A")
End Sub

#7

If it's a list of constants then you can use Select Case as follows:

如果它是一个常量列表，那么您可以使用Select Case如下:

Dim Item$: Item = "A"

Select Case Item
  Case "A", "B", "C"
    ' If 'Item' is in the list then do something.
  Case Else
    ' Otherwise do something else.
End Select

#8

A Case statement might suit some applications more simply:

案例陈述可能更适合某些应用:

select case var
case "a string", "another string", sVar
  'do something
case else
  'do something else
end select