Excel VBA 类似运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17893280/
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
Excel VBA like operator
提问by Ramesh
I am using excel VBA to search for a substring in another string as below.
我正在使用 excel VBA 在另一个字符串中搜索子字符串，如下所示。
Dim pos As Integer
pos = InStr("I am studying at University of Texas at Arlington", "University of Texas")
If posreturns a non-negative value, it means I have the substring in the string.
However, I need a more sophisticated search, where the substring can be "Univ of Tex":
如果pos返回非负值，则表示我在字符串中有子字符串。但是，我需要更复杂的搜索，其中子字符串可以是 "Univ of Tex"：
InStr("I am studying at University of Texas at Arlington", "Univ of Tex")
Doesn't work for that.
不适合那个。
Based on the maximum search terms, I need to say the substring is present. Is it possible using excel VBA?
基于最大搜索词，我需要说子字符串存在。是否可以使用excel VBA？
回答by Andy G
The Likeoperator is already available in VBA:
该Like运算符已在 VBA 中可用：
If "I am studying at University of Texas at Arlington" Like "*Univ*of*Texas*" Then
MsgBox "Yes, it is!"
End If
回答by Mathieu Guindon
Try this:
尝试这个：
Public Function StringContainsAny(string_source As String, _
ByVal caseSensitive As Boolean, _
ParamArray find_values()) As Boolean
Dim i As Integer, found As Boolean
If caseSensitive Then
For i = LBound(find_values) To UBound(find_values)
found = (InStr(1, string_source, _
find_values(i), vbBinaryCompare) <> 0)
If found Then Exit For
Next
Else
For i = LBound(find_values) To UBound(find_values)
found = (InStr(1, LCase$(string_source), _
LCase$(find_values(i)), vbBinaryCompare) <> 0)
If found Then Exit For
Next
End If
StringContainsAny = found
End Function
Usage:
用法：
Dim SomeString As String
SomeString = "I am studying at University of Texas at Arlington"
Debug.Print StringContainsAny(SomeString, False, "university of texas") 'True
Debug.Print StringContainsAny(SomeString, True, "university of texas") 'False
Debug.Print StringContainsAny(SomeString, True, "University of Texas") 'True
But also:
但是也：
Debug.Print StringContainsAny(SomeString, False, "TEXAS", "SMURF") 'True
And:
和：
Debug.Print StringContainsAny(SomeString, False, "univ", "of", "tex") 'True
Or:
或者：
Dim Words As String()
Words = Split("univ of tex", " ")
Debug.Print StringContainsAny(SomeString, False, Words) 'True
You can specify as many values as you need for find_values(), and the function exits on the first match found.
您可以为 指定任意数量的值find_values()，并且函数在找到的第一个匹配项时退出。
If you specifically want to return truewhen all parts of "Univ of Tex" are found in the string, you'd have to tweak the code a bit, to return truewhen all passed arguments are found in the string. But then perhaps the method should be renamed StringContainsAll- perhaps something like this:
如果您特别想true在字符串中找到“德克萨斯大学”的所有部分时返回，则必须稍微调整代码，以便true在字符串中找到所有传递的参数时返回。但是也许该方法应该重命名StringContainsAll- 也许是这样的：
Public Function StringContainsAll(string_source As String, _
ByVal caseSensitive As Boolean, _
ParamArray find_values()) As Boolean
Dim i As Integer, found As Boolean
If caseSensitive Then
For i = LBound(find_values) To UBound(find_values)
found = (InStr(1, string_source, find_values(i), vbBinaryCompare) <> 0)
If Not found Then Exit For
Next
Else
For i = LBound(find_values) To UBound(find_values)
found = (InStr(1, LCase$(string_source), _
LCase$(find_values(i)), vbBinaryCompare) <> 0)
If Not found Then Exit For
Next
End If
StringContainsAll = found
End Function
Note that this last snippet isn't tested and doesn't care in what order the words are found in the string.
请注意，最后一个片段没有经过测试，也不关心单词在字符串中的顺序。
