CODEWITHSUNDEEP
vba
Ben
Ben

Reputation: 21625

VBA equivalent to SQL 'in' function

I'm writing a conditional statement in vba like

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

I was wondering if there's a quicker, cleaner way to do this. Something like

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

Thanks

Upvotes: 8

Views: 8916

Answers (5)

Mark E.
Mark E.

Reputation: 452

You could make a basic function like this:

Function InArray(Match, SourceArray)
    InArray = False
    For i = LBound(SourceArray) To UBound(SourceArray)
        If SourceArray(i) = Match Then
            InArray = True
            Exit Function
        End If
    Next
End Function

Then you could say:

if InArray(userId, array(1,2,3,4)) then msgbox "Found it!"

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

You can use the Application.Match function on an array:

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

Upvotes: 4

Gaffi
Gaffi

Reputation: 4367

CW because this matches the hypothetical example, but not likely a real use situation. However, Like is a good keyword to know.

If userID Like "[1-6]" Then

This is ok for single digit checks, but not real world multi-character user IDs.

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

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33155

Another way

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

Filter returns an array with the match. If there's no match, ubound = -1.

Upvotes: 5

MicSim
MicSim

Reputation: 26796

An alternative would be:

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

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

Upvotes: 11

Related Questions