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
-
Quicker in terms of less characters or efficiency of code?user2140173– user21401732013-07-09 16:45:26 +00:00Commented Jul 9, 2013 at 16:45
-
@mehow Less characters. My list will have less than 10 numbers to check, so performance differences between methods should be negligible.Ben– Ben2013-07-09 16:48:19 +00:00Commented Jul 9, 2013 at 16:48
Add a comment
|
5 Answers
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.
Comments
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.
Comments
You can use the Application.Match function on an array:
If Not IsError(Application.Match(userID, Split("1,2,3,4",","))) Then...
2 Comments
Gaffi
Doesn't
IsError need () in this usage?
David Zemens
Yes, it does. Good catch. I have revised.
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
Comments
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!"
