在 VBA 中使用 InStr 进行多字符串搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24972335/
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
Multiple string search with InStr in VBA
提问by Vishwabandhu Kumar
I am checking whether a Name textbox starts with Mr. Mrs. Ms. etc.
我正在检查姓名文本框是否以 Mr. Mrs. Ms. 等开头。
I created a function but I am not able to compare more than one string.
我创建了一个函数，但我无法比较多个字符串。
Here is my code.
这是我的代码。
'Checking whether name is starts with Mr./Mrs./Ms./Dr. or not
If Not FindString(LCase(Me.gname.Value), LCase("Mr")) Then
MsgBox "Consumer Name Starts with Mr./Mrs./Ms./Dr. Check Consumer Name"
Cancel = True
Exit Sub
End If
'Here is the Find String function i created
Function FindString(strCheck As String, strFind As String) As Boolean
Dim intPos As Integer
intPos = 0
intPos = InStr(strCheck, strFind)
FindString = intPos > 0
End Function
采纳答案by Jagadish Dabbiru
Pass strFind as group of strings seperated by a delimiter ex:-
将 strFind 作为由分隔符分隔的一组字符串传递，例如：-
FindString(LCase(Me.gname.Value), LCase("Mr;Mrs;Ms;Dr"))
Now split them and compare using a loop.
现在拆分它们并使用循环进行比较。
Arr = Split(strFind,";")
Flag = 0
For Each str in Arr
If InStr(strCheck, str) > 0 Then
Flag = 1
End If
Next
If Flag = 1 Then
FindString = True
Else
FindString = False
End If
回答by Alex K.
Pass a list of tokens to search for using a ParamArrayand loop each looking for a match.
传递令牌列表以使用 a 进行搜索ParamArray并循环每个查找匹配项。
You can use vbTextCompareto enforce case sensitivity.
您可以使用vbTextCompare来强制区分大小写。
Remember that starts withis different from contains.
请记住，以开头不同于包含。
'// you can pass as many prefixes as you like
If StringStarts(Me.gname.Value, "Mr", "Mrs", "Dr", "Supreme Commander", "Capt.") Then
MsgBox "Consumer Name Starts with Mr./Mrs./Ms./Dr. Check Consumer Name"
...
Function StringStarts(strCheck As String, ParamArray anyOf()) As Boolean
Dim item As Long
For item = 0 To UBound(anyOf)
If InStr(1, strCheck, anyOf(item), vbTextCompare) = 1 Then
StringStarts = True
Exit Function
End If
Next
End Function
Or better with a RegExto allow optional .and not match Mruku
或者更好RegEx地允许可选.和不匹配Mruku
StringStarts(Me.gname.Value, "Mr|Mrs|Ms|Dr")
...
Function StringStarts(strCheck As String, options As String) As Boolean
With CreateObject("VBScript.RegExp")
.IgnoreCase = True
.Pattern = "^(" & options & ")\.*\b"
StringStarts = .Test(strCheck)
End With
End Function
回答by FreeSoftwareServers
This is my version of @AlexKgreat answer. While it solved the OP's original problem I wanted to share a more generalized answer for others to benefit from.
这是我的@AlexK版本的好答案。虽然它解决了 OP 的原始问题，但我想分享一个更通用的答案，让其他人从中受益。
Here is how I used the Function:
以下是我如何使用该功能：
Public Sub InString_Test()
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet1")
Dim rcell As Range, rng As Range
Set rng = WS.Range("A1:A" & WS.UsedRange.Rows.Count)
For Each rcell In rng.Cells
If InStrFunc(Range(rcell.Address), "TEST", "CAT") Then
MsgBox "String Found in " & rcell.Address
End If
Next rcell
End Sub
Function InStrFunc(strCheck As String, ParamArray anyOf()) As Boolean
Dim item As Long
For item = 0 To UBound(anyOf)
If InStr(1, strCheck, anyOf(item), vbTextCompare) <> 0 Then
InStrFunc = True
Exit Function
End If
Next
End Function
