Excel 2019 Power Programming with VBA. Michael Alexander

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() 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

      TIP

      VBA exits a Select Case construct as soon as a True case is found. Therefore, for maximum efficiency, you should check the most likely case first.

      Select Case structures can also be nested. The following procedure, for example, uses the VBA TypeName function to determine what is selected (a range, nothing, or anything else). If a range is selected, the procedure executes a nested Select Case and tests for the number of cells in the range. If one cell is selected, it displays One cell is selected. Otherwise, it displays a message with the number of selected rows.

      Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows" End Select Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub

This procedure also demonstrates the use of Case Else, a catchall case. You can nest Select Case constructs as deeply as you need, but make sure that each Select Case statement has a corresponding End Select statement.

      This procedure demonstrates the value of using indentation in your code to clarify the structure. For example, take a look at the same procedure without the indentations:

      Sub SelectionType() Select Case TypeName(Selection) Case "Range" Select Case Selection.Count Case 1 MsgBox "One cell is selected" Case Else MsgBox Selection.Rows.Count & " rows"Case "Nothing" MsgBox "Nothing is selected" Case Else MsgBox "Something other than a range" End Select End Sub

      Fairly incomprehensible, eh?

      Looping blocks of instructions

      Looping is the process of repeating a block of instructions. You might know the number of times to loop, or the number may be determined by the values of variables in your program.

      The following code, which enters consecutive numbers into a range, demonstrates what is considered to be a bad loop. The procedure uses two variables to store a starting value (StartVal) and the total number of cells to fill (NumToFill). This loop uses the GoTo statement to control the flow. If the iCount variable, which keeps track of how many cells are filled, is less than the value of NumToFill, the program control loops back to DoAnother.

      Sub BadLoop() Dim StartVal As Integer Dim NumToFill As Integer Dim iCount As Integer StartVal = 1 NumToFill = 100 ActiveCell.Value = StartVal iCount = 1 DoAnother: ActiveCell.Offset(iCount, 0).Value = StartVal + iCount iCount = iCount + 1 If iCount < NumToFill Then GoTo DoAnother Else Exit Sub End Sub

This procedure works as intended, so why is it an example of bad looping? Programmers generally frown on using a GoTo statement when not absolutely necessary. Using GoTo statements to loop is contrary to the concept of structured coding. (See the “What is structured programming?” sidebar.) A GoTo statement makes the code much more difficult to read because representing a loop using line indentations is almost impossible. In addition, this type of unstructured loop makes the procedure more susceptible to error. Furthermore, using lots of labels results in spaghetti code—code that appears to have little or no structure and has a tangled flow.

      Because VBA has several structured looping commands, you almost never have to rely on GoTo statements for your decision-making.

      For-Next loops

      The simplest type of a