Often, you'll need to test two or more conditions before taking an action: if statement X is True and statement Y is True, then do this; if statement X is True or statement Y is True, then do the other; if statement X is True and statement Y isn't True, then find something else to do; and so on. For example, if it's raining and you have a cold, put on your warmest rain gear.
To test multiple conditions, you use VBA's logical operators to link the conditions together. Table 11.2 lists the logical operators that VBA supports, with short examples and comments.
Of these six logical operators, you'll probably use the conjunction (And), disjunction (Or), and negation (Not) operators the most, with the other three thrown in on special occasions. (If the Imp logical operator doesn't make sense to you at this point, you probably don't need to use it.)
VBA Doesn't Do Short-Circuit EvaluationHere's something to beware of when evaluating multiple conditions: VBA doesn't do short-circuit evaluation in logical expressions (unlike other programming languages such as C and C++). Short-circuit evaluation is the formal term for a simple logical technique most people use several times a day when making decisions in their daily lives: if the first of two or more complementary conditions is false, you typically don't waste time evaluating any other conditions contingent upon it. For example, suppose your most attractive coworker says they'll take you to lunch if you get the product out on time and get a promotion. If you don't get the product out on time, you've blown your chances — it doesn't much matter if you get the promotion, because even if you do, your lunch will still be that brown bag you forgot to put in the department fridge. There's no point in evaluating the second condition because it depends on the first, and the first condition wasn't met. VBA doesn't think that way. It evaluates the second condition (and any subsequent conditions) whether or not it needs to. Evaluating the conditions takes a little more time (which isn't usually an issue) and can introduce unexpected complications in your code (which can be an issue). For example, the following snippet produces an error when the selection is only one character long. The error occurs because the code ends up running the Mid function on a zero-length string (the one-character selection minus one character)—even though you wouldn't expect this condition to be evaluated when the first condition is not met (because the length of the selection is not greater than 1): Dim strShort As String strShort = Selection.Text If Len(strShort) + 1 And _ Mid(strShort, Len(strShort) - 1, 1) = "T" Then MsgBox "The second-last character is T." End If To avoid problems such as this, use nested If statements. In the following code example, the first condition isn't met (again, for a one-character selection), so the second condition isn't evaluated: If Len(strShort) > 1 Then If Mid(strShort, Len(strShort) - 1, 1) = "T" Then MsgBox "The second-last character is T." End If End If |
18.119.118.163