Listing A Procedure That Uses ifThen Else

Function FutureValue(Rate As Single, Nper As Integer, Pmt As Currency, ^Frequency As String) As Currency If Frequency = "Monthly" Then

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:

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, VBAs FV function (see Chapter 4) is used to return the future value.

^ if...Then...Else statements are much easier to read when you indent the expressions between if...Then, Else,and End if,as I've done in Listing 6.2.This lets you easily identify which group of statements will be run if there is a True result and which group will be run if the result is False. Pressing the Tab key once at the beginning of the first line in the block does the job. See also "Indenting for Readability," later in this chapter.

0 0

Post a comment