Can you list the possible values for a conditional statement in Excel VBA

Question

I'm new so sorry if this is a dumb question. I want to have an If statement stating if variable a is an element of a set of some integers then... Would it work if I write:

If a=1,6,12,19 Then

or would I have to write

If a=1 Or a=6 Or a=12 Or a=19 Then

You can use a case statement. E.g. Public Function test(a) Select Case a Case 1, 2, 3, 4, 5, 6 'do something End Select End Function — RyanL
– RyanL, Commented Mar 17, 2017 at 19:46

Community · Accepted Answer · 2017-05-23 12:32:08Z

4

No, you can't write it as if a=1,6,12,19 Then but there are some alternatives that you can use and they are covered in the answers to this previous question. This is the one that I'd recommend in most cases.

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

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Mar 17, 2017 at 19:48

Richard Schwartz

14.6k2 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user6140865 Over a year ago

If there are ever anything more than 2 conditions, I always go with select case. It's a beautiful thing! Don't forget case else. It's not required but it's a good habit to stick it in there before your end select so you don't forget the option is there :)

user4039065 · Accepted Answer · 2017-03-17 20:08:54Z

1

The InStr function can handle it as a delimited substring within a string containing delimited options.

if cbool(instr(1, "x1x2x3x4x5x6x", format(a, "x0x"))) then
    'a is a number between 1 and 6
    debug.Print a & " is in 1,2,3,4,5,6"
end if

answered Mar 17, 2017 at 20:08

user4039065

Comments

user3598756 · Accepted Answer · 2017-03-18 06:46:52Z

0

you can use Application.Match() method

if Not IsError(Application.Match(a,array(1,6,12,19),0) Then
    your code
End If

answered Mar 18, 2017 at 6:46

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Can you list the possible values for a conditional statement in Excel VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related