Michael Alexander - Excel 2019 Power Programming with VBA

The following procedure demonstrates an If- Thenstructure without an Elseclause. The example deals with time, and VBA uses a date-and-time serial number system similar to Excel's. The time of day is expressed as a fractional value—for example, noon is represented as .5. The VBA Timefunction 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

Another way to code this routine is to use multiple statements, as follows:

Sub GreetMe1a() If Time < 0.5 Then MsgBox "Good Morning" End If End Sub

Note that the Ifstatement has a corresponding End Ifstatement. In this example, only one statement is executed if the condition is True. You can, however, place any number of statements between the Ifand End Ifstatements.

If you want to display a different greeting when the time of day is after noon, add another If- Thenstatement, as follows:

Sub GreetMe2() If Time < 0.5 Then MsgBox "Good Morning" If Time >= 0.5 Then MsgBox "Good Afternoon" End Sub

Notice that we used >= (greater than or equal to) for the second If- Thenstatement. This covers the remote chance that the time is precisely 12 p.m.

Another approach is to use the Elseclause of the If- Thenconstruct. Here's an example:

Sub GreetMe3() If Time < 0.5 Then MsgBox "Good Morning" Else _MsgBox "Good Afternoon" End Sub

Notice that we used the line continuation sequence; If- Then- Elseis actually a single statement.

If you need to execute multiple statements based on the condition, use this form:

Sub GreetMe3a() If Time < 0.5 Then MsgBox "Good Morning" ' Other statements go here Else MsgBox "Good Afternoon" ' Other statements go here End If End Sub

If you need to expand a routine to handle three conditions (for example, morning, afternoon, and evening), you can use either three If- Thenstatements or a form that uses ElseIf. The first approach is 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.75 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 if the first condition is satisfied (that is, it's 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. For more complex applications, however, you need another syntax.

If condition Then [true_instructions] [ElseIf condition-n Then [alternate_instructions]] [Else [default_instructions]] End If

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

Sub GreetMe5() If Time < 0.5 Then 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- Thenconstruct ends. In other words, the extraneous conditions aren't evaluated. Although this syntax makes for greater efficiency, some 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- Elseconstructs (without using ElseIf). This procedure is efficient and also easy to understand. Note that each Ifstatement has a corresponding End Ifstatement.

Sub GreetMe6() If Time < 0.5 Then MsgBox "Good Morning" Else If Time >= 0.5 And Time < 0.75 Then MsgBox "Good Afternoon" Else If Time >= 0.75 Then MsgBox "Good Evening" End If End If End If End Sub

The following is another example that uses the simple form of the If- Thenconstruct. This procedure prompts the user for a value for Quantityand then displays the appropriate discount based on that value. Note that Quantityis declared as a Variantdata type. This is because Quantitycontains an empty string (not a numeric value) if InputBoxis cancelled. To keep the procedure simple, it doesn't perform any other error checking. For example, it doesn't 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 >= 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

Notice that every If- Thenstatement in this procedure is always executed, and the value for Discountcan change. The final value, however, is the desired value.

The following procedure is the previous one rewritten to use the alternate syntax. In this alternate version, only the If- Thenstatement that evaluates to Trueis actually executed.

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 Else Discount = 0.25 End If MsgBox "Discount: " & Discount End Sub

VBA offers an alternative to the If- Thenconstruct: the IIffunction. This function takes three arguments and works much like Excel's IFworksheet function. The syntax is as follows:

IIf(expr, truepart, falsepart)

expr: (Required) Expression you want to evaluate

truepart: (Required) Value or expression returned if expr is True

falsepart: (Required) Value or expression returned if expr is False

The following instruction demonstrates the use of the IIffunction. The message box displays Zeroif cell A1 contains a 0or is empty and displays Nonzeroif cell A1 contains anything else.

MsgBox IIf(Range("A1") = 0, "Zero", "Nonzero")

It's important to understand that the third argument ( falsepart) is always evaluated, even if the first argument ( expr) is True. Therefore, the following statement generates a division-by-zero error if the value of nis 0(zero):

MsgBox IIf(n = 0, 0, 1 / n)

The Select Caseconstruct is useful for choosing among three or more options. This construct also works with two options, and it is a good alternative to If- Then- Else. The syntax for Select Caseis as follows:

Select Case testexpression [Case expressionlist-n [instructions-n]] [Case Else [default_instructions]] End Select