vba 模仿“IN”运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1505206/
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
Imitating the "IN" Operator
提问by Allan Bowe
How can one achieve:
如何实现：
if X in (1,2,3) then
instead of:
代替：
if x=1 or x=2 or x=3 then
In other words, how can one best imitate the INoperator in VBA for excel?
换句话说，如何才能最好地模仿INVBA for excel 中的运算符？
采纳答案by Kredns
I don't think there is a very elegant solution.
我不认为有一个非常优雅的解决方案。
However, you could try:
但是，您可以尝试：
If Not IsError(Application.Match(x, Array("Me", "You", "Dog", "Boo"), False)) Then
or you could write your own function:
或者您可以编写自己的函数：
Function ISIN(x, StringSetElementsAsArray)
ISIN = InStr(1, Join(StringSetElementsAsArray, Chr(0)), _
x, vbTextCompare) > 0
End Function
Sub testIt()
Dim x As String
x = "Dog"
MsgBox ISIN(x, Array("Me", "You", "Dog", "Boo"))
End Sub
回答by Robert Mearns
You could also try the CASEstatement instead of IF
您也可以尝试使用CASE语句而不是IF
Select Case X
Case 1 To 3
' Code to do something
Case 4, 5, 6
' Code to do something
Case 7
' Code to do something
Case Else
' More code or do nothing
End Select
回答by ashleedawg
Fastest Method:
最快的方法：
Here's a method much faster and more compactthan anyof the other answers, and works with numeric or text values:
这是一种比任何其他答案更快、更紧凑的方法，并且适用于数字或文本值：
Function IsIn(valCheck, valList As String) As Boolean
IsIn = Not InStr("," & valList & ",", "," & valCheck & ",") = 0
End Function
Examples:
例子：
Use IsInwith a numeric value:
IsIn与数值一起使用：
Sub demo_Number()
Const x = 2
If IsIn(x, "1,2,3") Then
Debug.Print "Value " & x & " was Found!"
Else
Debug.Print "Value " & x & " was not Found."
End If
End Sub
Use IsInwith a string value:
使用IsIn带有字符串值：
Sub demo_Text()
Const x = "Dog"
If IsIn(x, "Me,You,Dog,Boo") Then
Debug.Print "Value " & x & " was Found!"
Else
Debug.Print "Value " & x & " was not Found."
End If
End Sub
Speed Comparison:
速度比较：
To compare speed I ran the test from the accepted answer 100,000 times:
为了比较速度，我从接受的答案中运行了 100,000 次测试：
0.406 sec (FASTEST)This Function(using InStr):1.828 sec (450% slower)Accepted Answerwith the "ISIN" function1.799 sec (440% slower)Answerwith the "IsInArray" from freeVBcode0.838 sec (206% slower)Answerwith modified "IsInArray" function
0.406 sec (FASTEST)这个函数（使用InStr）：1.828 sec (450% slower)使用“ISIN”功能 接受的答案1.799 sec (440% slower)答案与freeVBcode的“IsInArray”0.838 sec (206% slower)使用修改后的“IsInArray”函数 回答
I didn't include the much longer answerthat uses SELECT..CASEsince the OP's goal was presumably to simplify and shortenthe task compared to "if x=1 or x=2 or x=3 then".
我没有包括使用的更长的答案，SELECT..CASE因为与“ ”相比，OP 的目标大概是简化和缩短任务if x=1 or x=2 or x=3 then。
回答by THEn
did you try
你试过了吗
eval("3 in(1,2,3,4,5)")
回答by anschauung
There's none that I'm aware of.
没有我不知道的。
I usually use a home-brewed InArray() function like the one at http://www.freevbcode.com/ShowCode.asp?ID=1675
我通常使用自制的 InArray() 函数，例如http://www.freevbcode.com/ShowCode.asp?ID=1675
You could also make a version that iterates through the array instead of concatenating, if that is more appropriate to your data type.
如果更适合您的数据类型，您还可以制作一个迭代数组而不是连接的版本。
回答by shlomo
I wrote it now...
我现在写了...
Public Function IsInArray(FindValue As Variant, ParamArray arrEmailAttachment()) As Boolean
Dim element As Variant
For Each element In arrEmailAttachment
If element = FindValue Then
IsInArray = True
Exit Function
End If
Next element
IsInArray = False
End Function
