Imitating the "IN" Operator

Question

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 IN operator in VBA for excel?

Answer 1

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

Answer 2

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

Answer 3

Fastest Method:

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

---

Examples:

Use 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

Use 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

---

Speed Comparison:

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".

Answer 4

did you try

eval("3 in(1,2,3,4,5)")

Answer 5

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.

Answer 6

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

Answer 7

dim x, y
x = 2
y = Array(1, 2, 3)

For i = 0 To 2
If x = y(i) Then
'your code comes here
Exit For
End If
Next i

Answer 8

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