MS-Excel / General Formatting

---

Using If Then Else to Handle a False Result

Using the If...Then statement to make decisions adds a powerful new weapon to your VBA arsenal.
However, this technique suffers from an important drawback: A False result only bypasses one or more statements; it doesn't execute any of its own. This is fine in many cases, but there will be times when you need to run one group of statements if the condition returns True and a different group if the result is False.
To handle this, you need to use an If...Then...Else statement:

If condition Then
   [TrueStatements]
Else
   [FalseStatements]
End If

condition
The logical expression that returns True or False.

TrueStatements
The statements to run if condition returns True.

FalseStatements
The statements to run if condition returns False.

If the condition returns True, VBA runs the group of statements between If...Then and Else. If it returns False, VBA runs the group of statements between Else and End If.

Let's look at an example. Suppose you want to calculate the future value of a series of regular deposits, but you want to differentiate between monthly deposits and quarterly deposits. Listing shows below a Function procedure called FutureValue that does the job.

A Procedure That Uses If...Then...Else

Function FutureValue(Rate As Single, Nper As Integer, Pmt As Currency, Frequency As String) As Currency
    If Frequency = "Monthly" Then
        FutureValue = FV(Rate / 12, Nper * 12, Pmt / 12)
    Else
        FutureValue = FV(Rate / 4, Nper * 4, Pmt / 4)
    End If
End Function

The first three arguments-Rate, Nper, and Pmt-are, respectively, the annual interest rate, the number of years in the term of the investment, and the total deposit available annually. The fourth argument-Frequency-is either "Monthly" or "Quarterly." The idea is to adjust the first three arguments based on Frequency. To do that, the If...Then...Else statement runs a test on the Frequency argument:

If Frequency = "Monthly" Then

If the logical expression Frequency = "Monthly" returns True, the procedure runs the following statement:

FutureValue = FV(Rate / 12, Nper * 12, Pmt / 12)

This statement divides the interest rate by 12, multiplies the term by 12, and divides the annual deposit by 12. Otherwise, if the logical expression returns False, then a quarterly calculation is assumed and the procedure executes the following statement:

FutureValue = FV(Rate / 4, Nper * 4, Pmt / 4)

This statement divides the interest rate by 4, multiplies the term by 4, and divides the annual deposit by 4. In both cases, VBA's FV function is used to return the future value.