The IfThen structure

Okay, I'll say it: If-Then is VBA's most important control structure. You'll probably use this command on a daily basis (at least I do). As in many other aspects of life, effective decision making is the key to success in writing programs. If this book has the effect I intend, you'll soon share my philosophy that a successful Excel application boils down to making decisions and acting upon them.

The If-Then structure has this basic syntax:

If condition Then statements [Else elsestatements]

Use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, if included, lets you execute one or more statements if the condition you're testing is not true. Sound confusing? Don't worry; a few examples make this crystal clear.

If-Then examples

The following routine demonstrates the If-Then structure without the optional Else clause:

Sub GreetMe()

If Time < 0.5 Then MsgBox "Good Morning" End Sub

The GreetMe procedure uses VBA's Time function to get the system time. If the current system time is less than .5 (in other words, before noon), the routine displays a message. If Time is greater than or equal to .5, the routine ends and nothing happens.

To display a different greeting if Time is greater than or equal to .5, add another If-Then statement after the first one:

Sub

GreetMe()

If Time < 0.5 Then MsgBox '

'Good Morning"

If Time >= 0.5 Then MsgBox

"Good Afternoon"

End

Sub

Notice that I used >= (greater than or equal to) for the second If-Then statement. This covers the extremely remote chance that the time is precisely 12:00 p.m.

An If-Then-Else example

Another approach to the preceding problem uses the Else clause. Here's the same routine recoded to use the If-Then-Else structure:

Sub GreetMe()

If Time < 0.5 Then MsgBox "Good Morning" Else _ MsgBox "Good Afternoon" End Sub

Notice that I use the line continuation character (underscore) in the preceding example. The If-Then-Else statement is actually a single statement. But

VBA provides a slightly different way of coding If-Then-Else constructs that use an End-If statement. Therefore, the GreetMe procedure can be rewritten as:

Sub

GreetMe()

If Time < 0

.5 Then

MsgBox

"Good Morning"

Else

MsgBox

"Good Afternoon"

End If

End

Sub

In fact, you can insert any number of statements under the If part, and any number of statements under the Else part.

What if you need to expand the GreetMe routine to handle three conditions: morning, afternoon, and evening? You have two options: Use three If-Then statements or use a nested If-Then-Else structure. Nesting means placing an If-Then-Else structure within another If-Then-Else structure. The first approach, the three statements, is simplest:

Sub GreetMe2()

Dim Msg As String

If Time < 0.5 Then Msg = 1

"Morning"

If Time >= 0.5 And Time <

0.75 Then Msg =

"Afternoon"

If Time >= 0.7 5 Then Msg =

= "Evening"

MsgBox "Good " & Msg

End Sub

The Msg variable gets a different text value, depending on the time of day. The final MsgBox statement displays the greeting: Good Morning, Good Afternoon, or Good Evening.

The following routine performs the same action but uses a nested If-Then-Else structure:

Sub

GreetMe3()

Dim Msg

As String

If Time

< 0.5 Then

Msg

= "Morning"

End If

If Time

>= 0.5 And Time < 0.7 5 Then

Msg

= "Afternoon"

End If

If Time

>= 0.7 5 Then

Msg

= "Evening"

End If

MsgBox '

'Good " & Msg

End

Sub

The example works fine but could be improved. The routine has to test for all conditions, be it morning, afternoon, or evening. If the first test is met, no further tests are needed. Here's the modified procedure:

Sub

GreetMe4()

Dim Msg As String

If Time < 0.5 Then

Msg = "Morning"

Else

If Time >= 0.5 And Time < 0.7 5 Then

Msg = "Afternoon"

Else

Msg = "Evening"

End If

End If

MsgBox "Good " & Msg

End

In both of the previous examples, every statement in the routine is executed — even in the morning. A more efficient structure would exit the routine as soon as a condition is found to be true. In the morning, for example, the procedure should display the Good Morning message and then exit — without evaluating the other superfluous conditions.

With a tiny routine like this, you don't have to worry about execution speed. But for larger applications in which speed is important, you should know about another syntax for the If-Then structure. The ElseIf syntax follows:

If condition Then

[statements] [Elself condition-n Then

[elsestatements]] End If

Here's how you can rewrite the GreetMe routine by using this syntax:

Sub GreetMe5()

Dim Msg As String If Time < 0.5 Then

Msg = "Morning" Elself Time >= 0.5 And Time < 0.7 5 Then

Msg = "Afternoon" Else

Msg = "Evening" End If

MsgBox "Good " & Msg End Sub

When a condition is true, VBA executes the conditional statements and the If structure ends. In other words, VBA doesn't waste time evaluating the extraneous conditions, which makes this procedure a bit more efficient than the previous examples. The trade-off (there are always trade-offs) is that the code is more difficult to understand. (Of course, you already knew that.)

Another If-Then example

Here's another example that uses the simple form of the If-Then structure. This procedure prompts the user for a quantity and then displays the appropriate discount, based on the quantity the user enters:

Sub ShowDiscount()

Dim Quantity As Integer Dim Discount As Double Quantity = lnputBox("Enter Quantity:") If Quantity > 0 Then Discount = 0.1 If Quantity >= 25 Then Discount = 0.15 If Quantity >= 50 Then Discount = 0.2 If Quantity >= 7 5 Then Discount = 0.2 5 MsgBox "Discount: " & Discount End Sub

A workbook that contains this section's examples can be downloaded from this book's Web site.

Notice that each If-Then statement in this routine is executed and the value for Discount can change as the statements are executed. However, the routine ultimately displays the correct value for Discount.

The following procedure performs the same tasks by using the alternative ElseIf syntax. In this case, the routine ends immediately after executing the statements for a true condition.

Sub ShowDiscount2()

Dim Quantity As Integer

Dim Discount As Double

Quantity = InputBox("Enter Quantity:

If Quantity > 0 And Quantity < 25 Then

Discount = 0.1

ElseIf Quantity >= 2 5 And Quantity <

50

Then

Discount = 0.15

ElseIf Quantity >= 50 And Quantity <

75

Then

Discount = 0.2

ElseIf Quantity >= 75 Then

Discount = 0.25

End If

MsgBox "Discount: " & Discount

End Sub

Personally, I find these multiple If-Then structures rather cumbersome. I generally use the If-Then structure for only simple binary decisions. When a decision involves three or more choices, the Select Case structure offers a simpler, more efficient approach.

0 0

Post a comment