# Imitating the “IN” Operator

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
``````

# 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
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
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`“.

did you try

``````eval("3 in(1,2,3,4,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.

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
``````