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.

What is structured programming? Does it matter?

If you hang around with programmers, sooner or later you hear the term structured programming. This term has been around for decades, and programmers generally agree that structured programs are superior to unstructured programs. So, what is structured programming? And can you write such things using VBA?

The basic premise of structured programming is that a routine or code segment should have only one entry point and one exit point. In other words, a block of code should be a stand-alone unit. A program cannot jump into the middle of this unit, nor can it exit at any point except the single exit point. When you write structured code, your program progresses in an orderly manner and is easy to follow — unlike a program that jumps around in a haphazard fashion. This pretty much rules out using the GoTo statement.

In general, a structured program is easier to read and understand. More importantly, it's also easier to modify when the need arises.

VBA is indeed a structured language. It offers standard structured constructs such as If-Then-Else, For-Next loops, Do-Until loops, Do-While loops, and Select Case structures. Furthermore, it fully supports module code constructions. If you're new to programming, you should try to develop good structure programming habits early on. End of lecture.

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 =

"Morning"

If Time >= 0.5 And Time <

0.75 Then Msg = "Afternoon"

If Time >= 0.75 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" Else

If Time >= 0.5 And Time

< 0.75 Then Msg = "Afternoon"

Else

If Time >= 0.75 Then

Msg = "Evening"

MsgBox "Good " & Msg

End Sub

The example works fine but could be simplified a bit by omitting the last If-Then part. Because the routine has already tested for two conditions (morning and afternoon), the only remaining condition is evening. 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.75 Then _ Msg = "Afternoon" Else Msg = "Evening" MsgBox "Good " & Msg End Sub

Using Elself

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

[e/sestatements]] End If

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

Sub GreetMe5()

Dim Msg As String If Time < 0.5 Then Msg = "Morning"

ElseIf Time >= 0.5 And Time < 0.75 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 = InputBox("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 >= 75 Then Discount = 0.

.25

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 Elself 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 >= 25 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