11.2. Testing Multiple Conditions by Using Logical Operators

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.

Table 11.2. VBA's Logical Operators
OperatorMeaningExampleComments
AndConjunctionIf ActiveWorkbook.FullName = "c: empExample.xlsm" And Year(Date) >= 2005 ThenIf both conditions are True, the result is True. If either condition is False, the result is False.
NotNegationActivePresentation.Saved = Not ActivePresentation.SavedNot reverses the value of x (True becomes False; False becomes True). The Saved property used in this example is Boolean.
OrDisjunctionIf ActiveWindow.View = wdPageView Or ActiveWindow.View = wdOutlineView ThenIf either the first condition or the second is True, or if both conditions are True, the result is True.
XOrExclusionIf Salary > 55000 XOr Experienced = True ThenTests for different results from the conditions: Returns True if one condition is False and the other is True; returns False if both conditions are True or both conditions are False.
EqvEquivalenceIf blnMyVar1 Eqv blnMyVar2 ThenTests for logical equivalence between the two conditions: If both values are True, or if both values are False, Eqv returns True. If one condition is logically different from the other (that is, if one condition is True and the other is False), Eqv returns False.
ImpImplicationIf blnMyVar1 Imp blnMyVar2 ThenTests for logical implication. Returns True if both conditions are True, both conditions are False, or the second condition is True. Returns Null if both conditions are Null or if the second condition is Null. Otherwise, returns False.

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 Evaluation

Here'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


Using Not to Toggle Boolean Properties

Not is a handy way of turning True to False and False to True. By using Not with a Boolean property, you can toggle the state of the property without even needing to check what the current state is. For example, in Excel, you can toggle the value of the Boolean property Saved (which controls whether Excel thinks the document in question contains unsaved changes) by using code such as this:

If ActiveWorkbook.Saved = True Then
  ActiveWorkbook.Saved = False
Else
  ActiveWorkbook.Saved = True
End If

But you can achieve the same effect much more simply by using the following code:

ActiveWorkbook.Saved = Not ActiveWorkbook.Saved


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.118.163