IF() END.IF() Handles Errors Differently than IF() in Excel

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0

SUMMARY

When evaluating an error, the IF() END.IF() form handles errors differently than the single IF() statement.

The following examples illustrate the difference.

Example 1               Example 2
---------               ---------

A1: =1/0                B1: =1/0
A2: =IF(A1=TRUE)        B2: =IF(B1=TRUE,BEEP(),)
A3: =BEEP()             B3: =RETURN()

In example 1, the macro halts at A2 and returns an error, while example 2 evaluates "=1/0" as an error but completes the macro.

MORE INFORMATION

In example 2, the IF statement is a single formula that resolves to #DIV/0, just as any simple formula (such as A1+1) that refers to a cell containing an error value will return that error value.

In example 1, the IF statement returns the same error. However, because it is part of a program structure (the IF/END.IF) rather than a stand-alone formula that returns a single value, the integrity of the code is destroyed, and Excel displays the macro error. A cell that resolves to an error cannot go on and execute an action such as branching to the END.IF(). The error must first be checked for, and the error-returning comparison (A2=TRUE) only computed if the variables are not in error.