Flow Control: Using If-Then-Else and Select Case

 

Another aspect of programming that will never be recorded by the macro recorder is the concept of flow control. Sometimes, you do not want every line of your program to be executed every time that you run the macro.  VBA offers two excellent choices for flow control – the If...Then…Else construct and the Select…Case construct.

 

Basic Flow Control: If…Then…Else

 

The most common device for program flow control is the If statement. Say that you have a list of products as shown in Figure 5.13. You would like to loop through each product in the list and copy it to either a Fruits list or a Vegetables list. As a beginning programmer, I was tempted to loop through the rows twice; I wanted to loop through once looking for fruit and a second time looking for vegetables. However, there is no need to loop through twice. On a single loop, you can use an If…Then…Else construct to copy each row to the correct place.

 

 Figure 5.13

 

Conditions

 

Any If statement needs a condition that is being tested. The condition should always evaluate to TRUE or FALSE. Here are some examples of simple and complex conditions:

  • If Range(“A1”).value = “Title” Then
  • If Not Range(“A1”).Value = “Title” then
  • If Range(“A1”).Value = “Title” And Range(“B1”).Value = “Fruit”
  • If Range(“A1”).Value = “Title” Or Range(“B1”).Value = “Fruit”

 

If…Then…End If

 

After the If statement, you may include one or more program lines that will be executed only if the condition is met. You should then close the If block with an End If line. Here is a simple example of an If statement.

 

Sub ColorFruitRedBold()

    FinalRow = Cells(65536, 1).End(xlUp).Row

   

    For i = 2 To FinalRow

        If Cells(i, 1).Value = "Fruit" Then

            Cells(i, 1).Resize(1, 3).Font.Bold = True

            Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3

        End If

    Next i

           

    MsgBox "Fruit is now bold and red"

End Sub

 

Either/Or Decisions:  If…Then…Else…End If

 

Sometimes you will want to do one set of statements if the condition is true and another set of statements if the condition is not true. To do this with VBA, the second set of conditions would be coded after the Else statement. There is still only one End If statement associated with this construct. Let’s say that you want to color the fruit red and the vegetables green.

 

Sub FruitRedVegGreen()

    FinalRow = Cells(65536, 1).End(xlUp).Row

   

    For i = 2 To FinalRow

        If Cells(i, 1).Value = "Fruit" Then

            Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3

        Else

            Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50

        End If

    Next i

           

    MsgBox "Fruit is red / Veggies are green"

End Sub

 

Using If…ElseIf…End If for multiple conditions.

 

You will notice that our product list includes one item that is classified as an herb. We really have three conditions for which to test. It is possible to build and If…EndIf structure with multiple conditions. First, test to see if the record is a fruit. Next, use an ElseIf to test if the record is a vegetable. Next, test to see if the record is an Herb. Finally, if the record is none of those then highlight the record as an error.

 

Sub MultipleIf()

    FinalRow = Cells(65536, 1).End(xlUp).Row

   

    For i = 2 To FinalRow

        If Cells(i, 1).Value = "Fruit" Then

            Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3

        ElseIf Cells(i, 1).Value = "Vegetable" Then

            Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50

        ElseIf Cells(i, 1).Value = "Herbs" Then

            Cells(i, 1).Resize(1, 3).Font.ColorIndex = 5

        Else

            ' This must be a record in error

            Cells(i, 1).Resize(1, 3).Interior.ColorIndex = 6

        End If

    Next i

           

    MsgBox "Fruit is red / Veggies are green / Herbs are blue"

End Sub

 

Using Select Case … End Select for multiple conditions

 

When you have many different conditions, it becomes unwieldy to use many ElseIf statements. VBA offers another construct known as the Select Case construct. In our running example, we always want to check the value of the Class in column A. This value is called the test expression. The basic syntax of this construct would start with the word Select Case followed by the test expression.

 

Select Case Cells(i, 1).Value

 

Thinking about our problem in English, you might say “in the case where the record is fruit, then color the record with red”. VBA uses a shorthand version of this. You write the word Case followed by the literal “Fruit”. Any statements that follow Case “Fruit” will be executed whenever the test expression is a fruit. After these statements, you would have the next Case statement – Case “Vegetables”. You would continue in this fashion, writing a Case Statement, followed by the program lines that will be executed if that case is true.

 

After you’ve listed all the possible conditions that you can think of, you may optionally include a “Case Else” section at the end. This section will include what the program should do if the test expression matches none of your cases. 

 

Finally, close the entire construct with the End Select statement.

 

The following program does the same operation as the previous macro, but uses a Select Case statement.

 

Sub SelectCase()

    FinalRow = Cells(65536, 1).End(xlUp).Row

   

    For i = 2 To FinalRow

        Select Case Cells(i, 1).Value

            Case "Fruit"

                Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3

            Case "Vegetable"

                Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50

            Case "Herbs"

                Cells(i, 1).Resize(1, 3).Font.ColorIndex = 5

            Case Else

        End Select

    Next i

           

    MsgBox "Fruit is red / Veggies are green / Herbs are blue"

End Sub

Complex Expressions in Case Statements

 

It is possible to have fairly complex expressions in case statements. You might want to perform the same actions for all berry records:

 

            Case "Strawberry", "Blueberry", "Raspberry"

                AdCode = 1

 

If it makes sense, you might code a range of values in the Case statement:

      Case 1 to 20

            Discount = 0.05

      Case 21 to 100

            Discount = 0.1

           

You can include the keyword Is and a comparison operator such as > or <.

      Case Is < 10

            Discount = 0

      Case is > 100

            Discount = 0.2

      Case Else

            Discount = 0.10

 

Nesting If Statements

 

It is possible and common to nest an If statement inside another If statement. This is a situation where it is very important to use proper indenting. You will find that you often have several End If lines at the end of the construct. By having proper indenting, it is easier to tell which End If is associated with a particular If.

 

The final macro has a lot of logic. Our discount rules are as follows:

  • For Fruit, quantities under 5 cases get no discount
  • Quantities from 5 to 20 cases get a 10% discount
  • Quantities above 20 cases get a 15% discount
  • For Herbs, quantities under 10 cases get no discount
  • Quantities from 10 cases to 15 cases get a 3% discount
  • Quantities above 15 cases get a 6% discount
  • For Vegetables except Asparagus, 5 cases and above earn a 12% discount
  • Asparagus requires 20 cases for a discount of 12%
  • None of the discounts apply if the product is on sale this week. The sale price is 25% off the normal price. This week’s sale items are Strawberry, Lettuce and Tomatoes.

 

The code to execute this logic follows.

 

Sub ComplexIf()

    FinalRow = Cells(65536, 1).End(xlUp).Row

   

    For i = 2 To FinalRow

        ThisClass = Cells(i, 1).Value

        ThisProduct = Cells(i, 2).Value

        ThisQty = Cells(i, 3).Value

       

        ' First, figure out if the item is on sale

        Select Case ThisProduct

            Case "Strawberry", "Lettuce", "Tomatoes"

                Sale = True

            Case Else

                Sale = False

        End Select

       

        ' Figure out the discount

        If Sale Then

            Discount = 0.25

        Else

            If ThisClass = "Fruit" Then

                Select Case ThisQty

                    Case Is < 5

                        Discount = 0

                    Case 5 To 20

                        Discount = 0.1

                    Case Is > 20

                        Discount = 0.15

                End Select

            ElseIf ThisClass = "Herbs" Then

                Select Case ThisQty

                    Case Is < 10

                        Discount = 0

                    Case 10 To 15

                        Discount = 0.03

                    Case Is > 15

                        Discount = 0.05

                End Select

            ElseIf ThisClass = "Vegetables" Then

                ' There is a special condition for asparagus

                If ThisProduct = "Asparagus" Then

                    If ThisQty < 20 Then

                        Discount = 0

                    Else

                        Discount = 0.12

                    End If

                Else

                    If ThisQty < 5 Then

                        Discount = 0

                    Else

                        Discount = 0.12

                    End If

                End If ' Is the product asparagus or not?

            End If ' Is the product a vegetable?

        End If ' Is the product on sale?

           

        Cells(i, 4).Value = Discount

       

        If Sale Then

            Cells(i, 4).Font.Bold = True

        End If

           

    Next i

           

    Range("D1").Value = "Discount"

           

    MsgBox "Discounts have been applied"

   

End Sub