Nested IfThen Statements

You can make more complex decisions in your VBA procedures by placing an If.. .Then or If.. .Then.. .Else statement inside another If.. .Then or If...Then...Else statement. Structures in which an If statement is contained inside another If block are referred to as nested If statements.

The following TestConditions procedure is a revised version of the WhatValue procedure created in the previous section. The WhatValue procedure was modified to illustrate how nested If.. .Then statements work.

Sub TestConditions() Range("A1").Select If IsEmpty(ActiveCell) Then

MsgBox "The cell is empty."

Else

If IsNumeric(ActiveCell.Value) Then If ActiveCell.Value = 0 Then

ActiveCell.Offset(0, 1).Value ElseIf ActiveCell.Value > 0 Then ActiveCell.Offset(0, 1).Value ElseIf ActiveCell.Value < 0 Then ActiveCell.Offset(0, 1).Value End If

Else

ActiveCell.Offset(0, 1).Value = "text"

End If End If End Sub

To make the TestConditions procedure easier to understand, each If...Then statement is shown with different formatting. You can now clearly see that the procedure uses three If.. .Then blocks.

The first If block (in bold) checks whether the active cell is empty. If this is true, the message is displayed, and Visual Basic skips over the Else part until it finds the matching End If. This statement is located just before the End Sub keywords.

If the active cell is not empty, the IsEmpty (ActiveCell) condition returns false, and Visual Basic runs the single underlined If block following the Else formatted in bold. This (underlined) If...Then...Else statement is said to be nested inside the first If block (in bold). This statement checks if the value of the active cell is a number. Notice that this is done with the help of another built-in function—IsNumeric. If the value of the active cell

= "zero" = "positive" = "negative"

is not a number, the condition is then false, so Visual Basic jumps to the statement following the underlined Else and enters "text" in cell B1.

However, if the active cell contains a number, Visual Basic runs the double-underlined If block, evaluating each condition and making the appropriate decision.

The first If block (in bold) is called the outer If statement. This outer statement contains two inner If statements (with single and double underlining).

Tip 5-5: Nesting Statements

Nesting means placing one type of control structure inside another control structure. You will see more nesting examples with the looping structures discussed in Chapter 6.

0 0

Post a comment