VBA 数组 - 检查严格（非近似）匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14205999/
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
VBA Arrays - Check strict (not approximative) match
提问by Sam
If UBound(Filter(myArray, Sheets(i).Cells(1, j).Value, True)) = -1 Then
'take action
End if
I used this syntax to compare an element found in Cells(1, j) (e.g. "ally") to all the elements of an array (e.g. "mally", "kate", "becks"), and to take action when no exact match is found. Trouble is, based on this line of code it seems "ally" is considered as matching "mally" (probably because "ally" is a substring from "mally"), whereas I want "ally" to be recognised as distinct from "mally".
我使用此语法将 Cells(1, j) 中的元素（例如“ally”）与数组的所有元素（例如“mally”、“kate”、“becks”）进行比较，并在没有时采取行动找到完全匹配。问题是，基于这行代码，似乎“ally”被认为是匹配“mally”（可能是因为“ally”是“mally”的子字符串），而我希望“ally”被识别为与“mally”不同”。
Any help with the syntax as to achieve this? Thank you!
语法上有什么帮助来实现这一点吗？谢谢！
采纳答案by Nick Perkins
Filter will return any items that partially match. The work around suggested by Microsoft is to then search the filtered array for exact matches.
过滤器将返回任何部分匹配的项目。Microsoft 建议的解决方法是在过滤后的数组中搜索精确匹配项。
Function FilterExactMatch(astrItems() As String, _
strSearch As String) As String()
' This function searches a string array for elements
' that exactly match the search string.
Dim astrFilter() As String
Dim astrTemp() As String
Dim lngUpper As Long
Dim lngLower As Long
Dim lngIndex As Long
Dim lngCount As Long
' Filter array for search string.
astrFilter = Filter(astrItems, strSearch)
' Store upper and lower bounds of resulting array.
lngUpper = UBound(astrFilter)
lngLower = LBound(astrFilter)
' Resize temporary array to be same size.
ReDim astrTemp(lngLower To lngUpper)
' Loop through each element in filtered array.
For lngIndex = lngLower To lngUpper
' Check that element matches search string exactly.
If astrFilter(lngIndex) = strSearch Then
' Store elements that match exactly in another array.
astrTemp(lngCount) = strSearch
lngCount = lngCount + 1
End If
Next lngIndex
' Resize array containing exact matches.
ReDim Preserve astrTemp(lngLower To lngCount - 1)
' Return array containing exact matches.
FilterExactMatch = astrTemp
End Function
This code is taken from http://msdn.microsoft.com/en-us/library/office/aa164525%28v=office.10%29.aspx
此代码取自http://msdn.microsoft.com/en-us/library/office/aa164525%28v=office.10%29.aspx
回答by Sandeep Bhat
IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
回答by Sorceri
If you do not need to use Filter then the below snippet would work
如果您不需要使用过滤器，那么下面的代码片段将起作用
Dim v
Dim bMatch As Boolean
bMatch = False
For Each v In myArray
'compare strings
If StrComp(CStr(v), Sheets(i).Cells(1, j).Value, vbTextCompare) = 0 Then
bMatch = True
End If
Next
If Not bMatch Then
'do something
End If
回答by Morbo
If the array is only used for this comparison and not needed for anything else you could also force full word comparisons by adding your own delimiters that never appear in the data - maybe square brackets.
So if you change your array to contain "[mally]", "[kate]", "[becks]"
then your condition becomes:
如果该数组仅用于此比较而不用于其他任何内容，您还可以通过添加您自己的从未出现在数据中的分隔符（可能是方括号）来强制进行全字比较。
因此，如果您将数组更改为包含“[mally]”、“[kate]”、“[becks]”，
那么您的条件变为：
If UBound(Filter(myArray, "[" & Sheets(i).Cells(1, j).Value & "]", True)) = -1
