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 6.2 shows a Function procedure called FutureValue that does the job. Listing 6.2. A Procedure that Uses If...Then...Else Function FutureValue(Rate, Nper, Pmt, Frequency) 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 the 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) tip  | 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. |
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, 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 (see Chapter 4) is used to return the future value. | 