VBA If <any of these> = <value>?

Question

I'm fairly new to VBA, and I can't find an easy way to test if any of the specified variables equal a specified value. The below seems to work, but is there an easier way to do it?

If variable1 = 1 Or variable2 = 1 Or variable3 = 1 Or variable4 = 1 Or variable5 = 1 Then End If

I've also tried the following, with no luck.

If (variable1 Or variable2 Or variable3 Or variable4 Or variable5) = 1 Then End If

Robledo

For individual elements, unless there are specific properties of the variables (where bit-twiddling tricks may work), you are out of luck. For arrays, you can try running a loop... Whoops, I take it back, follow Siddharth's example below. – Mukden

Answer 1

You can use select case :)

Sub Sample()
    Dim variable1, variable2, variable3, variable4, variable5

    variable1 = 1: variable2 = 1: variable3 = 1: variable4 = 1: variable5 = 1

    Select Case 1
        Case variable1, variable2, variable3, variable4, variable5
            MsgBox "One of them is equal to 1"
        Case Else
            MsgBox "none of then is equal to 1"
    End Select
End Sub

Tucky

That is a serious +1, didn't ever try inverting the select case method! – Mukden

I've never seen this done before. I hope I remember this trick when it will save the day. – Depressant

Thanks a lot, Siddharth! Thanks for spending the time to write this answer! – Robledo

But what if the value is vbNullString? That doesn't seem to work: "Invalid use of Null". – Robledo

@JohnSmith invalid use of null? are you sure it's not a type-mismatch? – Mejias

Answer 2

A way of doing it may be using max or min.

If max(variable1, ..., variableN) = 1 then

Hollowell

Apart from VBA not having a max function, how do you see it ever working? max(0, 1, 2) is 2, min(0, 1, 2) is 0, neither of which are = 1, yet both should be true because there is a 1 in each. – Fend