If Exists in a set 1

[egodette]

Instead of using
If (variable = "one" or variable = "two" or variable = "three") then

Is there a way in VBA for Excel to say
If variable exists in ("one","two","three") then

 

[Remou]

Is this confined to strings?

One possibility is:

If Instr ("one","two","three",variable)>0 Then

http://lessthandot.com

 

[macropod]

Another way is to use a Case test. Very useful when you have lots of possibilities in different groups:

Dim StrVar As String
Select Case StrVar
  Case "one", "two", "three"
    'Do something
  Case "four", "five", "six"
    'Do something
  Case Else
    'Do something
End Select

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

How does one get a star for code that

a) doesn't work (at least not with VBA's Instr function);
b) if it did work in the way clearly expected would be prone to erroneous results?

 

[Remou]

@strongm

Oops. Cut and paste got me, it should of course read

If InStr("one,two,three", variable) > 0 Then

http://lessthandot.com

 

[PHV]

A safer way:

If InStr(",one,two,three,", "," & variable & ",") > 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

>Cut and paste got me

Yep! I've had that as well

And unsurprisingly PHV's solution fixes the error issue I was alluding to

 

[MakeItSo]

How about using the array filter funtion and a check for negative first?
==>

ValidValues = Array("one", "two", "three")

'negative check
If UBound(Filter(ValidValues, variable, True, vbTextCompare)) = -1 Then 'not one, two nor three
    'do your stuff here
Else
    'you have one, two or three
    'do your stuff here
End If

Should do the trick as well, and "one,two" as variable would not hit. (In case that could ever happen)

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[strongm]

Nope, that's prone to the same errors as remou's solution (for example "on" would match), and which PHV's solution does not fall prey to.

 

[MakeItSo]

Hmmm. I see.
Thanks for pointing out.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.