If...Then...Else Statement

Description

Conditionally executes a group of statements, depending on the value of an expression.

Syntax 1

If condition Then statements [Else elsestatements ]

Syntax 2

If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements]] . . .
[Else
[elsestatements]]
End If

Syntax 1 has these parts:

Syntax 2 has these parts:

Remarks

You can use the single-line form (Syntax 1) for short, simple tests. However, the block form (Syntax 2) provides more structure and flexibility than the single-line form and is usually easier to read, maintain, and debug.

Note

With Syntax 1 it is possible to have multiple statements executed as the result of an If...Then decision, but they must all be on the same line and separated by colons, as in the following statement:

If A > 10 Then A = A + 1 : B = B + A : C = C + B

When executing a block If (Syntax 2), condition is tested. If condition is True, the statements following Then are executed. If condition is False, each ElseIf condition (if any) is evaluated in turn. When a True condition is found, the statements immediately following the associated Then are executed. If none of the ElseIf conditions are True (or if there are no ElseIf clauses), the statements following Else are executed. After executing the statements following Then or Else, execution continues with the statement following End If.

The Else and ElseIf clauses are both optional. You can have as many ElseIf clauses as you want in a block If, but none can appear after an Else clause. Block If statements can be nested; that is, contained within one another.

What follows the Then keyword is examined to determine whether or not a statement is a block If. If anything other than a comment appears after Then on the same line, the statement is treated as a single-line If statement.

A block If statement must be the first statement on a line. The Else, ElseIf, and End If parts of the statement can have only a line number or line label preceding them. The block If must end with an End If statement.

Tip

Select Case may be more useful when evaluating a single expression that has several possible actions.

See Also

Select Case Statement.

Example

This example shows uses of the If...Then...Else statement.

Number = 53    ' Initialize variable.
If Number < 10 Then
    Digits = 1
ElseIf Number < 100 Then
' Condition evaluates to True so the next statement is executed.
    Digits = 2
Else
    Digits = 3
End If
' Assign a value using the single line form of syntax.
If Digits = 1 Then MyString = "One" Else MyString = "More than one" 

This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces the value with 0 (zero).

For Each c in Worksheets("Sheet1").Range("A1:D10")
    If c.Value < .001 Then
        c.Value = 0
    End If
Next c

This example loops on the range named "TestRange" and displays the number of empty cells in the range.

numBlanks = 0
For Each c In Range("TestRange")
    If c.Value = "" Then
        numBlanks = numBlanks + 1
    End If
Next c
MsgBox "There are " & numBlanks & " empty cells in this range."

This example sets the standard font to Arial (in Windows) or Geneva (on the Macintosh).

If Application.OperatingSystem Like "*Macintosh*" Then
    Application.StandardFont = "Geneva"
Else
    Application.StandardFont = "Arial"
End If