VBA is equivalent to SQL 'in' function

Question

I am writing a conditional statement in vba, for example

if(userID = 1 or userID = 2 or userID = 3 or userID = 4) then ... 

I was wondering if there is a faster and cleaner way to do this. Something like

 if(userID in (1,2,3,4)) then ... 

thanks

Answer 1

An alternative could be:

 select case userID case 1,2,3,4,5,6 ' do something end select 

It conveys the meaning of the if ... then ... else construct very well.

Answer 2

Another way

 If UBound(Filter(Array(1, 2, 3, 4, 5, 6), UserID)) > -1 Then 

The filter returns an array with a match. If there is no match, ubound = -1.

Answer 3

You can use the Application.Match function for the array:

 If Not IsError(Application.Match(userID, Split("1,2,3,4",","))) Then... 

Answer 4

CW , because it corresponds to a hypothetical example, but hardly in a real situation. Like , however, is a good keyword to know.

 If userID Like "[1-6]" Then 

This is normal for unambiguous checks, but not for different personal user identifiers.

i.e.

 userID = 1 If userID Like "[1-6]" Then ' result is True 

but

 userID = 11 If userID Like "[1-6]" Then ' result is False