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