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"
Case Else
End Select
' Figure out the discount
If
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
Cells(i, 4).Font.Bold = True
End If
Next i
Range("D1").Value = "Discount"
MsgBox "Discounts have been applied"
End Sub