Search code examples
excelvba

Issue with IF sentence containing AND & OR functions

I created a seemingly simple code which checks the values in 2 cells (Q78 and Q87). And based on the values executes actions.

If Range("Q78") = "1" And Range("Q87") = "1" Or "2" Then

Go do this

ElseIf Range("Q78") = "2" And Range("Q87") = "1" Or "2" Then

Go do that

End If

The problem I have is that even if the value in the cell Q78="2" and Q87="2" it just runs the first part of the code(If Range("Q78") = "1" And Range("Q87") = "1" Or "2" Then). However if I change the Q87 value to "1" it works the way it should. What am I doing wrong?

Solution

  • To expand on @FunThomas 's comment with an explanation of why to add the brackets...

    Using parentheses (brackets) in that way forces VBA to evaluate the expression in the order you intended. Many languages (VBA included) will evaluate the and before the or but will evaluate the content between brackets first regardless.

    Taking

    If Range("Q78") = "1" And Range("Q87") = "1" Or Range("Q87") = "2" Then

    With your values that is equivalent to saying

    If "2" = "1" And "2" = "1" Or "2" = "2" Then

    Start with the And - that bit evaluates to False so the snippet becomes

    If False Or "2" = "2" Then

    Which becomes

    If False Or True Then
   Do This

    So you will always Do This.

    Force VBA to evaluate the Or first by adding the brackets

    If Range("Q78") = "1" And (Range("Q87") = "1" Or Range("Q87") = "2") Then

    The bit in the brackets is evaluated first (not that it matters as long as that Or is done on the intended values) and the whole thing evaluates to

    If False And True Then 'i.e. If False Then

    And it will route to Do That

    EDIT: Challenge accepted! @FunThomas mentioned I hadn't explained why

    If Range("Q78") = "2" And Range("Q87") = "1" Or "2" Then

    doesn't work as you expected it to. The key bit here is Or "2".

    First thing to note is that VBA will convert strings to numbers if it can. So in this case that is the same as Or 2.

    The 2nd point, is that boolean values have numeric equivalents

    False = 0

    True = any non-zero value - usually -1 or 1, but any value will do

    So 2 by itself = True

    Back to your snippet, what it is actually saying is

    If Range("Q78") = "2" And Range("Q87") = "1" Or True Then

    Follow the steps above from that point.