Using If...Then to Make True/False Decisions The most basic form of decision is the simple true/false decision (which could also be seen as a yes/no or an on/off decision). In this case, your program looks at a certain condition, determines whether it is currently true or false, and acts accordingly . As you might expect from the discussion of expressions in Chapter 4, "Building VBA Expressions," logical expressions (which, you'll recall, always return a True or False result) play a big part here. In VBA, simple true/false decisions are handled by the If...Then statement. You can use either the single-line syntax: If condition Then statement or the block syntax: If condition Then [ statements ] End If  | The code for this chapter is available on my Web site at the following address: http://www.mcfedries.com/ABGVBA/Chapter06.xls |
| condition | You can use either a logical expression that returns True or False, or you can use any expression that returns a numeric value. In the latter case, a return value of zero is functionally equivalent to False, and any nonzero value is equivalent to True. | | statement(s) | The VBA statement or statements to run if condition returns True. If condition returns False, VBA skips over the statements. | Whether you use the single-line or block syntax depends on the statements you want to run if the condition returns a True result. If you have only one statement, you can use either syntax. If you have multiple statements, you must use the block syntax. Listing 6.1 shows a revised version of the GrossMargin procedure from Chapter 3, "Understanding Program Variables" (see Listing 3.1). This version ”called GrossMargin2 ”uses If...Then to check the totalSales variable. The procedure calculates the gross margin only if totalSales isn't zero. tip  | You can make the If...Then statement in the GrossMargin2 procedure slightly more efficient by taking advantage of the fact that in the condition, zero is equivalent to False and any other number is equivalent to True. This means you don't have to explicitly test the totalSales variable to see whether it's zero. Instead, you can use the following statements: If totalSales Then GrossMargin = (totalSales-totalExpenses)/totalSales End If On the other hand, many programmers feel that including the explicit test for a nonzero value ( totalSales <> 0) makes the procedure easier to read and more intuitive. Since, in this case, the efficiency gained is only minor, you're probably better off leaving in the full expression. |
Listing 6.1. An If...Then Example Function GrossMargin2() Dim totalSales Dim totalExpenses totalSales = Application.Sum(Range("Sales")) totalExpenses = Application.Sum(Range("Expenses")) If totalSales <> 0 Then GrossMargin2 = (totalSales - totalExpenses) / totalSales End If End Function |
