MS-Excel / General Formatting

---

Making Multiple Decisions

The problem with If...Then...Else is that normally you can make only a single decision. The statement calculates a single logical result and performs one of two actions. However, plenty of situations require multiple decisions before you can decide which action to take.

For example, the FutureValue procedure discussed in the preceding section probably should test the Frequency argument to make sure it's either Monthly or Quarterly and not something else. The next few sections show you three solutions to this problem.

Using the And and Or Operators

One solution to the multiple-decision problem is to combine multiple logical expressions in a single If...Then statement. From my previous tutorial Building VBA Expression, you'll recall that you can combine logical expressions by using VBA's And and Or operators. In the example, we want to calculate the future value only if the Frequency argument is either Monthly or Quarterly. The following If...Then statement uses the Or operator to test this:

If Frequency = "Monthly" Or Frequency = "Quarterly" Then

As shown in Listing below, if Frequency equals either of these values, the entire condition returns True and the procedure runs the calculation in the usual way; otherwise, if Frequency doesn't equal either value, then the procedure returns a message to the user.

A Procedure That Uses the Or Operator to Perform Multiple Logical Tests

Function FutureValue2(Rate As Single, Nper As Integer, Pmt As Currency, Frequency As String) As Currency
    If Frequency = "Monthly" Or Frequency = "Quarterly" Then
        If Frequency = "Monthly" Then
            FutureValue2 = FV(Rate / 12, Nper * 12, Pmt / 12)
        Else
            FutureValue2 = FV(Rate / 4, Nper * 4, Pmt / 4)
        End If
    Else
        MsgBox "The Frequency argument must be either " & _
               """Monthly"" or ""Quarterly""!"
    End If
End Function

Note that this procedure isn't particularly efficient because you end up testing the Frequency argument in two places. However, that just means that this example isn't the best use of the And and Or operators. The overall principle of using these operators to perform multiple logical tests is a useful one, however, and you should keep it in mind when constructing your decision-making code.

Using Multiple If...Then...Else Statements

There is a third syntax for the If...Then...Else statement that lets you string together as many logical tests as you need:

If condition1 Then
   [condition1 TrueStatements]
ElseIf condition2 Then
   [condition2 TrueStatements]
<etc.>
Else
   [FalseStatements]
End If

condition1
A logical expression.

condition1 TrueStatements
The statements to run if condition1 returns True.

condition2
A different logical expression.

condition1 TrueStatements
The statements to run if condition2 returns True.

FalseStatements
The statements to run if both condition1 and condition2 return False.

VBA first tests condition1. If this returns True, VBA runs the group of statements between If...Then and ElseIf...Then. If it returns False, VBA then tests condition2. If this test is True, VBA runs the group of statements between ElseIf...Then and Else. Otherwise, VBA runs the statements between Else and End If. Here are two things you should note about this structure:

• You can have as many ElseIf conditions as you need.
• You don't have to use the Else part if you don't need it.

Listing shows FutureValue3, a revised version of FutureValue that makes allowances for an improper Frequency argument.

Listing - A Procedure That Uses Multiple If...Then...Else Statements

Function FutureValue3(Rate As Single, Nper As Integer, Pmt As Currency, Frequency As String) As Currency
    If Frequency = "Monthly" Then
        FutureValue3 = FV(Rate / 12, Nper * 12, Pmt / 12)
    ElseIf Frequency = "Quarterly" Then
        FutureValue3 = FV(Rate / 4, Nper * 4, Pmt / 4)
    Else
        MsgBox "The Frequency argument must be either " & _
               """Monthly"" or ""Quarterly""!"
    End If
End Function

As before, the If...Then statement checks to see whether Frequency equals Monthly and, if it does, calculates the future value accordingly. If it doesn't, the ElseIf...Then statement checks to see whether Frequency equals Quarterly and calculates the future value if the expression returns True. If it returns False, the user entered the Frequency argument incorrectly, so a warning message is displayed.