vba "IN" operator

Question

How best can one imitate the "IN" operator in VBA for excel?

eg

if X in (1,2,3) then

instead of:

if x=1 or x=2 or x=3 then

???

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

did you try

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

Answer 3

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 4

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 else
 Case Else  
  ' More code or do nothing

End Select

Answer 5

You can also use the Filter function as described here