The twoway If

The one-way If statement is applied when you want the program to execute one set of instructions if some condition is true, otherwise do nothing. However, you may want the program to execute one set of instructions if some condition is true, else execute another set of instructions. This type of logical construct is called a two way if statement. The general syntax is:

If ComparisonTest Then

One or more VBA statements Else

One or more VBA statements End If

We can see that whereas If executes code based on the comparison test's true result, the Else statement executes code based on the comparison test's false result. Else is an optional part of the If statement and specifies the code that executes if the comparison test is false.

If we look at the previous example, we can see that there is a problem. If a valid range is selected then the range is highlighted, if not, then nothing happens. However, in this event, we might want to prompt the user to enter a valid range, perhaps using a MsgBox. A two-way If structure has been used to implement this change in Listing 6.2. Note that the comparison test is the same, but an Else alternative displays the error message (see Figure 6.2).

'Highlights selected user range Sub GetRange () Dim Rng As Range

Set Rng = Application.InputBox (prompt: -'Enter range", Type: =8) If Not (Rng Is Nothing) Then _

Listing 6.2

Rng.Seleet

Indentation helps to identify the code inside the If statement's body

Else

MsgBox "Please choose a valid range'

End If End Sub

Please choose a valid range

Figure 6.2 The error message from Listing 6.2

This next example shows how the two-way If can be used. This macro will input a person's sex and display the message "You can retire at 60" if the sex of the person is female, otherwise display the message "You can retire at the age of 65". It uses a string variable to store the sex of the person, which is input through an InputBox command. The two-way If then checks if the sex is female and outputs the corresponding message. Otherwise, it displays the alternative message.

Listing 6.3 Another two-way If example

Sub retireAge () Dim sex As String sex = InputBox ("Input the sex of the person", "Person's Sex") If (sex="female").__

Then MsgBox "You can retire at the age of 60" Else

MsgBox "You can retire at 65" End Sub

Parentheses are not required around the test but they help separate it from the rest of the code

Figure 6.3 Screenshot from Listing 6.3
0 0

Post a comment