vba 如何在数组中搜索字符串
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/10951687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to search for string in an array
提问by aSystemOverload
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 IFa string is somewhere in the array.
编辑:它是一个一维数组。我只需要知道如果一个字符串是某处在数组中。
IE:
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 次,而且我不希望该函数减慢整个过程。
回答by JimmyPena
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 SeanCpoints out, this must be a 1-D array.
正如SeanC指出的那样,这必须是一维数组。
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.
如果您有特定的示例,请用它更新您的问题,否则示例代码可能不适用于您的情况。
回答by atomicules
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 maynot be desired. With a dictionary, it won't.
字典提供的优点是精确匹配而不是来自 的部分匹配Filter
。假设您有一个数组中的原始姓名列表,但正在寻找“JO”或“PHIL”,他们实际上是除了我们开始的四个人之外的两个新人。在这种情况下,Filter(oNAMES, "JO")
将匹配可能不需要的“JOHN” 。用字典,它不会。
回答by Brian Hinchey
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 方法及其参数的更多信息
回答by SeanC
there is a function that will return an arrayof all the strings found.
有一个函数将返回一个包含所有找到的字符串的数组。
Filter(sourcearray, 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
Filter(sourcearray, match[, include[, compare]])
sourcearray 必须是一维的
该函数将返回数组中包含该match
字符串的所有字符串
回答by Sebastian Viereck
more simple Function whichs works on Apple OS too:
更简单的功能也适用于 Apple OS:
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
回答by ChaimG
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
回答by ChaimG
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
回答by technicaltitch
A Case
statement might suit some applications more simply:
一个Case
语句可能更简单地适应一些应用程序:
select case var
case "a string", "another string", sVar
'do something
case else
'do something else
end select
回答by redOctober13
You could use the following without the wrapper function, but it provides a nicer API:
您可以在没有包装函数的情况下使用以下内容,但它提供了更好的 API:
Function IsInArray(ByVal findString as String, ByVal arrayToSearch as Variant) as Boolean
IsInArray = UBound(Filter(arrayToSearch,findString)) >= 0
End Function
The Filter
function has the following signature:
该Filter
函数具有以下签名:
Filter(sourceArray, stringToMatch, [Include As Boolean = True], [Compare as VbCompareMethod = vbBinaryCompare])
Filter(sourceArray, stringToMatch, [Include As Boolean = True], [Compare as VbCompareMethod = vbBinaryCompare])