How can one achieve:
if X in (1,2,3) then
if x=1 or x=2 or x=3 then
In other words, how can one best imitate the
IN operator in VBA for excel?
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
You could also try the CASE statement instead of 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
Here’s a method much faster and more compact than any of 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
IsIn with a numeric value:
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
IsIn with a string value:
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
To compare speed I ran the test from the accepted answer 100,000 times:
0.406 sec (FASTEST)This Function (using InStr):
1.828 sec (450% slower)Accepted Answer with the “ISIN” function
1.799 sec (440% slower)Answer with the “IsInArray” from freeVBcode
0.838 sec (206% slower)Answer with modified “IsInArray” function
I didn’t include the much longer answer that uses
SELECT..CASE since the OP’s goal was presumably to simplify and shorten the task compared to “
if x=1 or x=2 or x=3 then“.
did you try
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
You could also make a version that iterates through the array instead of concatenating, if that is more appropriate to your data type.
It doesn’t work without writing your own function. Be aware that the accepted solution by @Kredns may not work as expected for all types of objects since they are coerced to strings (which also may raise Type Mismatch errors).
This solution should (hopefully) handle all types of data (at least in Excel 365, not sure about earlier versions):
Function IsIn(x As Variant, list As Variant) As Boolean ' Checks whether list (Array) contains the element x IsIn = False For Each element In list If x = element Then IsIn = True Next element End Function
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