vba 在 If 语句中使用 InStr 而不是 And ？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3841004/
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
Using InStr instead of And in If statement?
提问by Max30
Instead of using ANDin a long Ifstatement, I'm using InStrto match a dynamic value to a known list. Like this:
我没有AND在长If语句中使用，而是使用InStr将动态值与已知列表进行匹配。像这样：
If InStr("John, George, Harry", personName) Then...
Is this okay? Should I be using ANDinstead? Is there a better way?
这个可以吗？我应该AND改用吗？有没有更好的办法？
Thanks.
谢谢。
采纳答案by Bob77
I don't think there is any single answer for all occasions.
我不认为在所有情况下都有一个单一的答案。
If the list is very short and fixed use a series of tests with Or:
如果列表非常短且固定，请使用 Or 进行一系列测试：
If personName = "John" Or personName = "George" Or personName = "Harry" Then
A mid-sized list could be represented as a String as suggested already, with a slight optimization:
一个中等大小的列表可以按照已经建议的方式表示为一个字符串，并稍作优化：
If InStr("$John$George$Harry$", "$" & personName & "$") Then
You might also use an array as your list, along with Filter():
您也可以将数组与 Filter() 一起用作列表：
If UBound(Filter(Array("$John$", "$George$", "$Harry$"), _
"$" & personName & "$")) >= 0 Then
Those options probably work better if you have a pre-built String or Array rather than inlining them within an expression.
如果您有一个预先构建的 String 或 Array 而不是将它们内联在表达式中，这些选项可能会更好地工作。
For a longer list you might use a Scripting.Dictionary object to hold the test cases. This incorporates a collision-resolved hash, and it has an Exists() method. A VB6 Collection works too though you need to use exception trapping to implement Exists-like functionality.
对于更长的列表，您可以使用 Scripting.Dictionary 对象来保存测试用例。这包含了一个冲突解决的哈希，并且它有一个 Exists() 方法。尽管您需要使用异常捕获来实现类似 Exists 的功能，但 VB6 集合也可以工作。
Even better if you have multiple "fields" to test (name and eye color?) you can use a fabricated ADO Recordset and its Filter property. This makes it easy to determine whener you have the George with blue eyes or the Harry with brown eyes. For a longer list of candidates you can set the dynamic property Optimize to True on the Recordset's Fields you want hashed for better performance.
如果您有多个要测试的“字段”（名称和眼睛颜色？），则更好，您可以使用制作的 ADO Recordset 及其 Filter 属性。这使您可以轻松确定何时拥有蓝眼睛的乔治或棕色眼睛的哈利。对于更长的候选列表，您可以在想要散列的记录集字段上将动态属性 Optimize 设置为 True 以获得更好的性能。
回答by Lie Ryan
No, that's not okay. If you do it that way, you'd match someone named "n, Ge" and "Geor".
不，那不行。如果你这样做，你会匹配一个名为“n, Ge”和“Geor”的人。
My VB is a bit rusty, but you can do:
我的 VB 有点生疏，但你可以这样做：
Dim namearray() As String = {"John", "George", "Harry"}
Dim name As String = "John"
For i = LBound(namearray) To UBound(namearray)
If namearray(i) = personName Then
... i is the array index ...
End If
Next i
I don't know if VB6 has Array.indexOf, but if it does, then you can also use the approach described here.
我不知道 VB6 是否有 Array.indexOf，但如果有，那么您也可以使用这里描述的方法。
If your list of names is very large, you may want to use a Dictionary
如果您的姓名列表非常大，您可能需要使用字典
回答by Sam Trost
What about substrings, like Anne-Marie and person name = Anne?
那么子字符串呢，比如 Anne-Marie 和 person name = Anne？
You could use a select case statement instead...
您可以改用 select case 语句...
Lifted example from DATABISON
Sub My_Select_Case_3_Text()
Dim my_val As String
my_val = "Pineapple"
Select Case my_val
Case "Apple": MsgBox "The fruit is Apple"
Case "Orange": MsgBox "The fruit is Orange"
Case "Pineapple": MsgBox "The fruit is Pineapple"
End Select
End Sub
回答by bobwah
You could store the names in a hash and then test if it is in the hash which would be faster than checking whether it is in a string or in a list.
您可以将名称存储在散列中，然后测试它是否在散列中，这比检查它是在字符串中还是在列表中要快。
回答by Dick Kusleika
A couple of options
几个选项
If Instr("[John][George][Harry]","[" & personname & "]") Then
will prevent partial matches. If you happen to be in Excel vba, you could use the built in MATCH worksheet function
将阻止部分匹配。如果您碰巧使用 Excel vba，则可以使用内置的 MATCH 工作表函数
Application.WorksheetFunction.Match("John",array("John","George","Harry"),false)
