IfThen Statement

The simplest way to get some decision-making into your VBA procedure is to use the If.. .Then statement. Suppose you want to choose an action depending on a condition. You can use the following structure:

If condition Then statement

For example, to delete a blank row from a worksheet, first check if the active cell is blank. If the result of the test is true, go ahead and delete the entire row that contains that cell:

If ActiveCell = "" Then Selection.EntireRow.Delete

If the active cell is not blank, Visual Basic will ignore the statement following the Then keyword.

Sometimes you may want to perform several actions when the condition is true. Although you could add other statements on the same line by separating them with colons, your code will look clearer if you use the multi-line version of the If.. .Then statement, as shown below:

If condition Then statementl statement2 statementN End If

For example, to perform some actions when the value of the active cell is greater than 50, you can write the following block of instructions:

If ActiveCell.Value >50 Then

MsgBox "The exact value is " & ActiveCell.Value Debug.Print ActiveCell.Adress & ": " & ActiveCell.Value End If

In the above example, the statements between the Then and the End If keywords are not executed if the value of the active cell is less than or equal to 50. Notice that the If.. .Then statement must end with the keywords End If. How does Visual Basic make a decision? It evaluates the condition it finds between the If.. .Then keywords.

Let's try to evaluate the following condition: ActiveCell.Value >50

1. Select any cell in a blank worksheet and enter 50.

2. Switch to the Visual Basic Editor window.

3. Activate the Immediate window.

4. Enter the following statement, and press Enter when you're done. ? ActiveCell.Value >50

When you press Enter, Visual Basic writes the result of this test—false. When the result of the test is false, Visual Basic will not bother to read the statement following the Then keyword in your code. It will simply go on to read the next line of your procedure, if there is one. However, if there are no more lines to read, the procedure will end.

5. Now change the operator to less than or equal to, and have Visual Basic evaluate the following condition:

? ActiveCell.Value <= 50

This time, the test returns true, and Visual Basic will jump to whatever statement or statements it finds after the Then keyword.

6. Close the Immediate window.

Now that you know how Visual Basic evaluates conditions, let's try the If.. .Then statement in a VBA procedure.

1. Open a new workbook and save it as Chap05.xls.

2. Switch to the Visual Basic Editor screen and rename the VBA project Decisions.

3. Insert a new module in the Decisions (Chap05.xls) project and rename this module IfThen.

4. In the IfThen module, enter the following procedure:

Sub SimpleIfThen()

Dim weeks As String weeks = InputBox("How many weeks are in a year:", "Quiz") If weeks<>52 Then MsgBox "Try Again" End Sub

The SimpleIfThen procedure stores the user's answer in the variable named weeks. The variable's value is then compared with the number 52. If the result of the comparison is true (that is, if the value stored in the variable weeks is not equal to 52), Visual Basic will display the message "Try Again."

5. Run the SimpleIfThen procedure and enter a number other than 52.

6. Rerun the SimpleIfThen procedure and enter the number 52.

When you enter the correct number of weeks, Visual Basic does nothing. The procedure simply ends. It would be nice to display a message when the user guesses right.

7. Enter the following instruction on a separate line before the End Sub keywords:

If weeks = 52 Then MsgBox "Congratulations!"

8. Run the SimpleIfThen procedure again and enter 52.

When you enter the correct answer, Visual Basic does not execute the statement MsgBox "Try Again." When the procedure is executed, the statement to the right of the Then keyword is ignored if the result from evaluating the supplied condition is false. As you recall, a VBA procedure can call another procedure. Let's see whether it can also call itself.

Tip 5-1: Two Formats of the If...Then Statement

The If...Then statement has two formats — single line and multi-line. The short format is good for statements that fit on one line, like:

If secretCode <> 01W01 Then MsgBox "Access denied" or

If secretCode = 01W01 Then alpha=True : beta = False

Here, secretCode, alpha, and beta are the names of variables. In the first example, Visual Basic displays the message "Access denied" if the value of the secretCode variable is not equal to 01W01. In the second example, Visual Basic sets the value of the variable alpha to True and variable beta to False when the secretCode value is equal to 01W01. Notice that the second statement to be executed is separated from the first by a colon.

The multi-line If.Then statement is clearer when there are more statements to be executed when the condition is true or when the statement to be executed is extremely long, as in the following example:

If ActiveSheet.Name = "Sheetl" Then ActiveSheet.Move after:=Sheets _ (Worksheets.Count)

End If

In this example, Visual Basic will examine the active sheet name. If it is "Sheet1," the condition ActiveSheet .Name = "Sheet1" will be true, and Visual Basic will proceed to execute the line following the Then keyword. As a result, the active sheet will be moved to the last position in the workbook.

9. Modify the first If statement in the SimplelfThen procedure, as follows:

If weeks <> 52 Then MsgBox "Try Again" : SimpleIfThen

We added a colon and the name of the SimpleIfThen procedure to the end of the existing If.. .Then statement. If the user enters the incorrect answer, he will see a message, and as soon as he clicks the OK button in the message box, he will get another chance to supply the correct answer—the input box will appear again. The user will be able to keep on guessing for a long time. In fact, he won't be able to exit the procedure gracefully until he supplies the correct answer. If he clicks Cancel, he will have to deal with the unfriendly error message "Type mismatch." You saw in the previous chapter how to use the On Error GoTo label statement to go around the error, at least temporarily until you learn more about error handling in Chapter 13. For now, you may want to revise your SimpleIfThen procedure as follows:

Sub SimpleIfThen()

Dim weeks As String On Error GoTo VeryEnd weeks = InputBox("How many weeks are in a year:", "Quiz") If weeks<>52 Then MsgBox "Try Again": SimpleIfThen If weeks=52 Then MsgBox "Congratulations!" VeryEnd: End Sub

10. Run the SimplelfThen procedure a few times by supplying incorrect answers. The error trap that you added to your procedure allows the user to quit guessing without having to deal with the ugly error message.

0 0

Post a comment