在VBA中,是否有一种简单的方法(一行代码)来搜索数组中的字符串?还是我需要循环遍历每个元素并将其与目标字符串进行比较?
编辑: 这是一个一维数组。我只需要知道一个字符串是否在数组中出现。
例如:
names(JOHN, BOB, JAMES, PHLLIP)
如何判断 "JOHN" 是否在数组中,需要保证查询速度尽可能快,因为这个操作将会被重复大约5000次,我不希望它减缓整个进程的速度。
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
如SeanC指出,这必须是一个一维数组。
示例:Sub Test()
Dim arr As Variant
arr = Split("abc,def,ghi,jkl", ",")
Debug.Print IsInArray("ghi", arr)
End Sub
以下代码是基于 HansUp 的评论更新的:
如果你想要匹配元素在数组中的索引,试试这个:
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
这也假设一个一维数组。请记住,LBound和UBound是基于零的,因此索引为2表示第三个元素,而不是第二个。
示例:
Sub Test()
Dim arr As Variant
arr = Split("abc,def,ghi,jkl", ",")
Debug.Print (IsInArray("ghi", arr) > -1)
End Sub
如果您有具体的示例,请更新您的问题,否则示例代码可能不适用于您的情况。
arr
中未找到stringToBeFound
,则IsInArray
的第二个版本返回0。 - HansUpUBound()
返回。 Dim lngReturn As Long; lngReturn = -1
如果找到了stringToBeFound
:lngReturn = i
最后,IsInArray = lngReturn
- HansUpTest()
中搜索g
,则会返回True。如果您需要精确匹配,则需要循环比较(请参见https://learn.microsoft.com/en-us/previous-versions/office/developer/office2000/aa164525(v=office.10)?redirectedfrom=MSDN)。 - mellamokb另一个选项是使用字典而不是数组:
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
这将为您提供一行代码
oNames.Exists("JOHN")
Filter
进行部分匹配。比如你有一个名字列表存储在数组中,但是想查找两个新加入的人 "JO" 和 "PHIL",这两个人并不在原来的四个人名字之中。此时,Filter(oNAMES, "JO")
会匹配到名字为 "JOHN" 的人,这可能并不是你想要的。但是如果使用字典,就不会出现这种情况。另一个可供选择的选项是强制执行精确匹配(即无部分匹配)的方式:
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
您可以在http://msdn.microsoft.com/en-us/library/office/ff835873(v=office.15).aspx了解有关Match方法及其参数的更多信息。
这里有另一个答案。它运行快速、可靠(参见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
使用示例:
Sub test()
Debug.Print "Is 'A' in the list?", IsInArray(Split("A,B", ","), "A")
End Sub
String
连接操作。 - chaotic3quilibrium有一个函数将返回找到的数组中的所有字符串。
Filter(sourcearray, match[, include[, compare]])
sourcearray 必须是一维的
该函数将返回数组中所有包含match
字符串的字符串。
Function isInArray(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
Dim element
For Each element In arr
If element = stringToBeFound Then
isInArray = True
Exit Function
End If
Next element
End Function
Dim element
(至少,如果打开了 Option Explicit 选项,那么你就应该这样做)。 - redOctober13Public Function IsName(name As String) As Boolean
Dim names As Object
Set names = CreateObject("System.Collections.ArrayList")
names.Add "JOHN"
names.Add "BOB"
names.Add "JAMES"
names.Add "PHLLIP"
IsName = names.Contains(name)
End Function
以下是使用方法:
If IsName("JOHN") Then ...
您可以不使用包装函数使用以下内容,但它提供了更好的API:
Function IsInArray(ByVal findString as String, ByVal arrayToSearch as Variant) as Boolean
IsInArray = UBound(Filter(arrayToSearch,findString)) >= 0
End Function
< p > Filter
函数的签名如下:
Filter(sourceArray, stringToMatch, [Include as Boolean = True], [Compare as VbCompareMethod = vbBinaryCompare])
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
对Jimmy Pena的回答的补充说明
正如SeanC所指出的,这必须是一个一维数组。
以下示例调用演示了IsInArray()
函数不能仅针对一维数组调用,
而且还可以针对"平面"二维数组调用:
Sub TestIsInArray()
Const SearchItem As String = "ghi"
Debug.Print "SearchItem = '" & SearchItem & "'"
'----
'a) Test 1-dim array
Dim Arr As Variant
Arr = Split("abc,def,ghi,jkl", ",")
Debug.Print "a) 1-dim array " & vbNewLine & " " & Join(Arr, "|") & " ~~> " & IsInArray(SearchItem, Arr)
'----
'//quick tool to create a 2-dim 1-based array
Dim v As Variant, vals As Variant
v = Array(Array("abc", "def", "dummy", "jkl", 5), _
Array("mno", "pqr", "stu", "ghi", "vwx"))
v = Application.Index(v, 0, 0) ' create 2-dim array (2 rows, 5 cols)
'b) Test "flat" 2-dim arrays
Debug.Print "b) ""flat"" 2-dim arrays "
Dim i As Long
For i = LBound(v) To UBound(v)
'slice "flat" 2-dim arrays of one row each
vals = Application.Index(v, i, 0)
'check for findings
Debug.Print Format(i, " 0"), Join(vals, "|") & " ~~> " & IsInArray(SearchItem, vals)
Next i
End Sub
Function IsInArray(stringToBeFound As String, Arr As Variant) As Boolean
'Site: https://dev59.com/L2gu5IYBdhLWcg3w9br_#10952705
'Note: needs a "flat" array, not necessarily a 1-dimensioned array
IsInArray = (UBound(Filter(Arr, stringToBeFound)) > -1)
End Function
SearchItem = 'ghi'
a) 1-dim array
abc|def|ghi|jkl ~~> True
b) "flat" 2-dim arrays
1 abc|def|dummy|jkl|5 False
2 mno|pqr|stu|ghi|vwx True