IfThen constructs

Perhaps the most commonly used instruction grouping in VBA is the If-Then construct. This common instruction is one way to endow your applications with decision-making capability. Good decision making is the key to writing successful programs. A successful Excel application essentially boils down to making decisions and acting on them.

The basic syntax of the If-Then construct is

If condition Then true_instructions [Else false_instructions]

The If-Then construct is used to execute one or more statements conditionally. The Else clause is optional. If included, it lets you execute one or more instructions when the condition that you're testing is not True.

The following procedure demonstrates an If-Then structure without an Else clause. The example deals with time. VBA uses a similar date-and-time serial number system as Excel. The time of day is expressed as a fractional value — for example, noon is represented as .5. The VBA Time function returns a value that represents the time of day, as reported by the system clock. In the following example, a message is displayed if the time is before noon. If the current system time is greater than or equal to .5, the procedure ends, and nothing happens.

Sub GreetMe1()

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

If you want to display a different greeting when the time of day is after noon, add another If-Then statement, like so:

Sub GreetMe2()

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 noon. Another approach is to use the Else clause of the If-Then construct. For example,

Sub GreetMe3()

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

Notice that I used the line continuation sequence; If-Then-Else is actually a single statement.

If you need to expand a routine to handle three conditions (for example, morning, afternoon, and evening), you can use either three If-Then statements or a nested If-Then-Else structure. The first approach is the simpler:

Sub GreetMe4()

If Time < 0.5 Then MsgBox "Good Morning"

If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon" If Time >= 0.75 Then MsgBox "Good Evening" End Sub

The value 0.7 5 represents 6 p.m.— three-quarters of the way through the day and a good point at which to call it an evening.

In the preceding examples, every instruction in the procedure gets executed, even in the morning. A more efficient procedure would include a structure that ends the routine when a condition is found to be True. For example, it might display the Good Morning message in the morning and then exit without evaluating the other, superfluous conditions. True, the difference in speed is inconsequential when you design a procedure as small as this routine. But for more complex applications, you need another syntax:

If condition Then

[true_instructions] [ElseIf condition-n Then

[a!ternate_instructions]] [Else

[defau!t_instructions]] End If

Here's how you can use this syntax to rewrite the GreetMe procedure:

Sub GreetMe5()

MsgBox "Good Morning"

ElseIf Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon"

Else

MsgBox "Good Evening" End If End Sub

With this syntax, when a condition is True, the conditional statements are executed and the If-Then construct ends. In other words, the extraneous conditions are not evaluated. Although this syntax makes for greater efficiency, some may find the code to be more difficult to understand.

The following procedure demonstrates yet another way to code this example. It uses nested If-Then-Else constructs (without using ElseIf). This procedure is efficient and also easy to understand. Note that each If statement has a corresponding End If statement.

Sub GreetMe6()

MsgBox "Good Morning"

Else

If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon"

Else

MsgBox "Good Evening" End If End If End If End Sub

The following is another example that uses the simple form of the If-Then construct. This procedure prompts the user for a value for Quantity and then displays the appropriate discount based on that value. Note that Quantity is declared as a Variant data type. This is because Quantity will contain an empty string (not a numeric value) if the InputBox is cancelled. To keep it simple, this procedure does not perform any other error checking. For example, it does not ensure that the quantity entered is a non-negative numeric value.

Sub Discount1()

Dim Quantity As Variant

Dim Discount As Double

Quantity = InputBox("Enter Quantity: ")

If Quantity = "" Then Exit Sub

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

Notice that each If-Then statement in this procedure is always executed, and the value for Discount can change. The final value, however, is the desired value.

The following procedure is the previous one rewritten to use the alternate syntax. In this case, the procedure ends after executing the True instruction block.

Sub Discount2()

Dim Quantity As Variant

Dim Discount As Double

Quantity = InputBox("Enter Quantity: ")

If Quantity = "" Then Exit Sub

If Quantity >= 0 And Quantity < 25 Then

Discount = 0.1 ElseIf Quantity < 50 Then

Discount = 0.15 ElseIf Quantity < 75 Then

Discount = 0.2 ElseIf Quantity >= 75 Then

Discount = 0.25 End If

MsgBox "Discount: " & Discount End Sub

I find nested If-Then structures rather cumbersome. As a result, I usually use the If-Then structure only for simple binary decisions. When you need to choose among three or more alternatives, the Select Case structure is often a better construct to use.

0 0

Post a comment