Topic 11 | Decisions and Looping
In this section we learn more about programming using the decision statements ‘If’ and ‘ElseIf’ as well
other statements such as ‘InputBox’ and ‘Select Case’. We also explore looping and meeting
conditions.
Introduction | Learning Outcomes | Study Time: Week 8
In this section we learn more about programming using the decision statements ‘If’ and ‘ElseIf’ as well
other statements such as ‘InputBox’ and ‘Select Case’. We also explore looping and meeting
conditions.
The learning objectives are to:
Explore use of single and multiple ‘If’ statements
Understand how to use ‘ElseIf’
Examine how to use other statements such as InputBox’
and ‘Select Case’
Explore use of looping and meeting conditions
The ‘If’ Statement
Decision-making in VBA (as in most programming languages) follows the logic of natural language.
Consider the code:
‘ How to make friends and money
If you give me £5 then I’ll be your best friend else I’ll tell
people you’re mean.
In VBA style this becomes:
‘ How to make friends and money
If you give me £5 Then
I’ll be your best friend
Else
I’ll tell people you’re mean
End If
The primary difference is that, instead of a full-stop, VBA requires you to use an End If. This is
analogous to the way you needed an End Sub or End Function: it explicitly states that you’ve
finished your decision-making stage.
Example | ‘If’ Statement
The following is a simple macro that makes use of the built-in function Time.
The time returned by the Time function can be compared with values such as #12:00# – the # symbols
tell VBA it should interpret 12:00 as a time.
Sub Greeting()
If Time < #12:00# Then MsgBox "Good Morning" Else MsgBox "Good Afternoon" End If End Sub Example | 'ElseIf' Statement We can introduce additional decision points to the code with ElseIf. Sub Greeting() If Time < #12:00# Then MsgBox "Good Morning" ElseIf Time < #18:00# Then MsgBox "Good Afternoon" Else MsgBox "Good Evening" End If End Sub Using 'If' Without An 'End If' For very brief If statements - where the whole statement fits on one line - it is OK to omit the End If. In this style, the previous code could have been written as: Sub DayOrNight1() If Time < #12:00# Then MsgBox "Good Morning" If Time >= #12:00# And Time < #18:00# Then MsgBox "Good Afternoon" If Time >= #18:00# Then MsgBox “Good Evening”
End Sub
However, if the decision is more complicated or if ElseIf is being used, If should be used in the ‘If
block’ format.
If Condition Then
Statement
Elseif
Statement
End If
Information | VBA Statements Over Multiple Lines
A statement in VBA needs to be written on one line, otherwise VBA will
treat the code as two incomplete statements and will return an error
message. However, long statements can be split over two lines if an
underscore (_) is used to link them together.
Example | The Statement
If Time >= #12:00# And Time < #18:00# Then MsgBox "Good Morning" could be written as If Time >= #12:00# And Time < #18:00# _ Then MsgBox "Good Morning" so that VBA treats the two lines as one statement. Multiple 'If' Statements Have a look at the following code. Even though it's not commented, work out what you think it does. Sub ShowDiscount() Dim intQuantity As Integer 'Got to be whole apples Dim dblDiscount As Double 'It’s a percentage intQuantity = InputBox("Enter Quantity: ") If intQuantity > 0 Then dblDiscount = 0.1
If intQuantity >= 25 Then dblDiscount = 0.15 If
intQuantity >= 50 Then dblDiscount = 0.2
If intQuantity >= 75 Then dblDiscount = 0.25
MsgBox “Discount: ” & dblDiscount
End Sub
If you have tried the code out, you will have discovered that the ‘If’ statements are evaluated in order,
so it is important that we code them in the appropriate sequence for our needs.
The ‘InputBox’ Statement
You will have noticed the use of the InputBox statement in the previous code. The InputBox
together with the MsgBox form handy interfaces between the user and the spreadsheet.
When using an InputBox statement to allow the user to input data you will need to check that the
user has put in data that is acceptable and of the expected type.
This is where If statements come into their own.
For example, in the previous example we need to check that the quantity is a positive number as
negative apples do not make sense in most people’s realities!
Example | ‘ElseIf’
You could write the code like this:
Sub ShowDiscount()
Dim intQuantity As Integer ‘Got to be whole apples
Dim dblDiscount As Double ‘It’s a percentage
intQuantity = InputBox(“Enter Quantity: “) If
intQuantity < 0 Then MsgBox "You can't have a negative quantity of apples" Exit Sub End If If intQuantity < 25 Then dblDiscount = 0.1 ElseIf intQuantity < 50 Then dblDiscount = 0.15 ElseIf intQuantity < 75 Then dblDiscount = 0.2 ElseIf intQuantity >= 75 Then
dblDiscount = 0.25
End If
MsgBox “Discount: ” & dblDiscount
End Sub
The ElseIf statements make explicit the importance of the order of these tests. Other checks on the
data input should be made.
What if 12.5 or “23 apples” was entered or the entry written as “twenty five”?
The ‘Select Case’ Statement
Eventually you will discover that you are working on a problem and trying to marshal too many Ifs
and ElseIfs.
You will wonder whether there isn’t an easier way to express the logic of your decision. That is when
you will want the Select Case statement.
Consider the code:
Sub CaseCondition() Select Case
ActiveCell.Value
Case Is < 0.2 MsgBox ("Too Small") Case 0.2 To 0.4 MsgBox ("Small") Case 0.6 To 0.8 MsgBox ("Big") Case Is > 0.8
MsgBox (“Too Big”)
Case Else
MsgBox (“Just Right”)
End Select
End Sub
This is a neat way to express several different conditions applied to the same piece of data, in this case
the value of the cell currently selected in the worksheet.
Activity 11.1 | Create a Macro: Evaluate Text
The ‘If’ and ‘Select Case’ statements can be formulated for a whole variety
of conditions, not just numeric ones.
In a blank worksheet enter the following list of fruit, one word to a cell:
Now create a new macro which tests the content of the ActiveCell and returns a different message
depending on the type of fruit. My macro looks like this:
Sub Fruit() Select Case
ActiveCell.Value
Case “apple”
MsgBox (“Russet or Bramley?”)
Case “lime”
MsgBox (“Mmmm, limes!”)
Case Else
MsgBox (“Sorry, none left”)
End Select
End Sub
Try and make yours different, with additional options for lemon, orange or banana.
Note that the test value needs to be in quotation marks when it is a character string and not
a number.
Try a condition such as
Case Is < "lime" What happens? Efficient and Elegant Code What will become clear over time is that there is more than one way to write code for a particular purpose. Sometimes it's clear that one way is more efficient than another, e.g. the second example of ShowDiscount is (slightly) more efficient and elegant than the first. However, sometimes it is not so clear which method is most appropriate and two versions may arguably be as good as each other. As your programs / macros get longer this will become more of an issue. There will be marks to be gained for using efficient and elegant code in your assignment. The 'For' Loop In the spreadsheets section you met the 'For' loop: For x = 1 To Range("B1").Value - 1 Range("A3").Offset(x, 0).Select ActiveSheet.Paste Next x This is one kind of loop which VBA offers us. In this kind of loop we know how many times we're going to repeat the actions. In simple 'For' loops, we can hard-code this. For example, we could specify a loop to run 365 times for the number of days in 2017. For intDay = 1 to 365 .... Next intDay 'For' loops are useful either when we know: a. Explicitly how many times the code must run. b. Or, this value can be found and input as a variable (as is the case in the first example above). Activity 11.2 | Looping In this task you will take an integer input from the user. This integer will then be displayed in a column and multiplied by 1, by 2, by 3, by 4 etc for the number of times specified by the user. You will also need to obtain this number as a second input. Some tips: One way of getting input from the user is by using the InputBox function which we met in the previous topic. intBase = InputBox("Enter Number for Multiples: ") The cell currently being used at any given time is ActiveCell. You may want to consider this when you loop. The result should look something like this: Loops | Meeting Conditions Sometimes it isn’t possible to know in advance how many times a loop should run. If I strike a match to light it, it might light first time, or second time, or it might even take me ten times if I'm outside or the match is slightly damp. If I am programmed to try a strict five times, that isn't always going to work. It may even lead to dangerous complications if the match lit before the fifth time! What I need to do is to continue to strike the match until it lights and so I'll be okay if it takes ten strikes, fifty strikes or a hundred strikes. VBA offers two slightly different ways to program a loop based on testing for a condition. Which one you use will depend on how you choose to express the condition. Examples How do they work? Do While the match is unlit Strike the match against the matchbox Loop As long as the condition is true, the loop continues. As soon as the condition is false, the loop ends. Do Until the match is lit Strike the match against the matchbox As long as the condition is not true, the loop continues. As soon as the condition is true, the loop ends. Loop Avoid Infinite Loops Notice that there is a danger here - what if the match simply never lights? Am I doomed to standing in the rain aimlessly striking a damp match for the rest of my life? Well, yes! Recall if you've ever sat at your PC and it has simply frozen, perhaps with the Windows egg timer spinning ... and spinning and waiting ... and waiting ... until you had to switch off the PC in despair?! There's a very good chance that some problem, bug or issue in the programming of Windows or the software that you were using went into a loop that it could not get out of. This is called an "infinite loop" and it is a very bad thing, so be careful to ensure that at some point you can get out of your loop or you may cause problems. Activity 11.3 | Meeting Conditions Start with a blank spreadsheet and enter some integers into the cells in Column A, starting with cell A1. It's up to you how many rows below A1 you put integers into: anything between five and a hundred will demonstrate what we want to try out. We want code which will look at the contents of Cell A1, will multiply it by 2 and put the result into B1. We then want it to do the same with the contents of A2, putting the result into B2. We want it to continue working downwards until it runs out of numbers to do this. Two tips: You could force VBA to start off with A1 as the ActiveCell (the selected cell) by using Worksheets("Sheet1").Range("A1").Activate We are going to be stuck in this loop until we come across a cell that doesn't have anything in it. So we could choose to test for the ActiveCell being empty ... Do Until ActiveCell.Value = Empty ...or not empty…. Do While ActiveCell.Value <> Empty
Summary
In this section we examined programming statements such as ‘If’, ‘ElseIf’ , ‘InputBox’ and ‘Select
Case’. We also explored looping and meeting conditions.
In this section we worked with:
Single and multiple ‘If’ statements
The ‘ElseIf’ statement
Other statements such as InputBox’ and ‘Select Case’
Looping and meeting conditions
This was the final topic in the MS924D | Spreadsheet Modelling and Demand Forecasting
module.