MS-Excel / General Formatting

---

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. 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

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 below shows the GrossMargin procedure. This version-called GrossMargin2- uses If...Then to check the totalSales variable. The procedure calculates the gross margin only if the value of totalSales isn't zero.

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

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.Because, in this case, the efficiency gained is only minor, you're probably better off leaving in the full expression.