Then [alternate_instructions]] [Else [default_instructions]] End If
Here's how you can use this syntax to rewrite the GreetMe procedure:
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-Then construct 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-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() 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-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 contains an empty string (not a numeric value) if InputBox is 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-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 alternate version, only the If-Then statement that evaluates to True is 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's IIf function
VBA offers an alternative to the If-Then construct: the IIf function. This function takes three arguments and works much like Excel's IF worksheet 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 IIf function. The message box displays Zero if cell A1 contains a 0 or is empty and displays Nonzero if 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 n is 0 (zero):
MsgBox IIf(n = 0, 0, 1 / n)
Select Case constructs
The Select Case construct 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 Case is as follows:
Select Case testexpression [Case expressionlist-n [instructions-n]] [Case Else [default_instructions]] End Select
The following example of a Select Case construct shows another way to code the GreetMe examples presented in the preceding section:
Sub GreetMe() Dim Msg As String Select Case Time Case Is < 0.5 Msg = "Good Morning" Case 0.5 To 0.75 Msg = "Good Afternoon" Case Else Msg = "Good Evening" End Select MsgBox Msg End Sub
And here's a rewritten version of the Discount example using a Select Case construct. This procedure assumes that Quantity is always an integer value. For simplicity, the procedure performs no error checking.
Sub Discount3() Dim Quantity As Variant Dim Discount As Double Quantity = InputBox("Enter Quantity: ") Select Case Quantity Case "" Exit Sub Case 0 To 24 Discount = 0.1 Case 25 To 49 Discount = 0.15 Case 50 To 74 Discount = 0.2 Case Is >= 75 Discount = 0.25 End Select MsgBox "Discount: " & Discount End Sub
The Case statement also can use a comma to separate multiple values for a single case. The following procedure uses the VBA Weekday function to determine whether the current day is a weekend (that is, the Weekday function returns 1 or 7). The procedure then displays an appropriate message.
Sub GreetUser1() Select Case Weekday(Now) Case 1, 7 MsgBox "This is the weekend" Case Else MsgBox "This is not the weekend" End Select End Sub
The following example shows another way to code the previous procedure:
Sub GreetUser2() Select Case Weekday(Now) Case 2, 3, 4, 5, 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub
Here's another way to code the procedure, using the To keyword to specify a range of values:
Sub GreetUser3() Select Case Weekday(Now) Case 2 To 6 MsgBox "This is not the weekend" Case Else MsgBox "This is the weekend" End Select End Sub
To demonstrate the flexibility of VBA, here is a final example in which each case is evaluated until one of the expressions evaluates to True:
Sub GreetUser4() Select Case True Case Weekday(Now) = 1 MsgBox "This is the weekend" Case Weekday(Now) = 7 MsgBox "This is the weekend" Case Else MsgBox "This is not the weekend" End Select End Sub
Any number of instructions can be written after each Case statement, and they're all executed if that case evaluates to True. If you use only one instruction per case, as in the preceding example, you might want to put the instruction on the same line as the Case keyword (but don't forget the VBA statement-separator character, the colon). This technique makes the code more compact. Here's an example:
Sub Discount3()
