CODEWITHSUNDEEP
excelvbaif-statement
horace_vr
horace_vr

Reputation: 3166

How to check against a list of values in an IF statement?

I am trying to write an IF statement like this:

if var [is any of 1,4,5,6,12] then do stuff

But I don't know the syntax for this in VBA, other than:

if var=1 or var=4 or var=5...

which seems a bit clumsy. Is there a different way?

Upvotes: 17

Views: 98822

Answers (3)

William Bell
William Bell

Reputation: 182

I'm a bit late to the 'party' but how about:

If InStr(1, ",1,5,8", "," & lVal1, vbTextCompare) > 0 Then

to check if 'lVal1' to equal to 1, 5 or 8.

And

If InStr(1, ",6,8,10,12", "," & lVal2, vbTextCompare) = 0 Then

to check that 'lVal2' is not equal to 6, 8, 10, 12.

The comma delimiters are important because, without them, in the first example, '15' or '58' or '158' would all be matched if 'lVal' was able to take one of those values.

You will need to pay attention to the delimiter that you use if there was likely to be any ambiguity with the value being checked for.

Likewise, using your knowledge of the range of values that will be encountered, the clunkiness of the leading delimiter in the first string and concatenating the delimiter to the front of the search value, could be removed.

Upvotes: 8

psychicebola
psychicebola

Reputation: 949

You can use a Select Case statement:

select case var
case 1,4,5,6,12
  'do something
case else
  'alternative
end select

Upvotes: 27

SeanS
SeanS

Reputation: 418

You could make a list of numbers, and then in a for-loop to compare these:

dim newNumber as Integer
dim compareList as new List Of(int)

for count as integer = 0 to compareList.count - 1
    if newNumber = compareList(nCount)
       'Do Stuff
    end if
next

This is a simple way of doing that I like to do, but may get performance intensive if your lists are really large/you want to do a lot of code in the "if" loop.

Upvotes: 1

Related Questions