Lecture4_vba.pdf

Lecture 4: Making Decision Prof. Carolina Peña Ortega, MsC Universidad de la Salle

Outline  This lecture covers the VBA decision statements o If…Then o If…Then…Else o If…Then…ElseIf o Select Case

The Decision Structure The decision structure allows a program’s logic to have more than one path of execution.

Order of Statement Execution  Thus far, our code has been executed sequentially in a sequence structure  To write meaningful programs we need multiple paths of execution o Some statements should be executed under certain circumstances in a decision structure o This lecture presents the means to execute statements conditionally o Next lecture presents the means to execute the same statements repeatedly

The Decision Structure  Flowchart of a typical decision structure  Evaluate the condition o Is it cold outside?  Execute or skip over some code

True Is it cold outside? False

o If yes, wear a coat Wear a coat.

The If...Then Statement The If...Then statement causes other statements to execute only when an expression is true.

General Format If expression Then statement more statements may follow)

End If  If the expression is True, execute the statements between If…Then and End If

 Otherwise, the statements are skipped  New keywords used above: If, Then, End

If…Then Example  If…Then statement example: 'Computer program that uses the If … Then Statement Sub prog01() Dim num As Integer

num = InputBox("Write an integer number") If num < 100 Then ActiveCell.Value = "Number: " & num ActiveCell.Offset(1, 0).Value = "The number is less than 100"

End If End Sub

If…Then Rules  The If and the Then must be on the same line  Nothing other than a comment can appear after the Then keyword, on the same line.

 The End If must be on a separate line. Only a comment may follow it on the same line.

If…Then Programming Style  The code between the If…Then and the End If is indented  VBA does not require this

 It is a convention among programmers to aid in the readability of programs  By default, the VBA editor will automatically do this indentation as you enter your program

Example: Using Relational Operators with Math Operators 'Using Relational Operators with Math Operators Sub prog02() Dim intX As Integer, intY As Integer Dim intA As Integer, intB As Integer intX = InputBox("write the X number ") intY = InputBox("write the Y number ") intA = InputBox("write the A number ") intB = InputBox("write the B number ") If intX + intY > intA - intB Then ActiveCell.Value = "It is true!" End If

End Sub

The If...Then...Else Statement The If...Then...Else statement executes one group of statements if the Boolean expression is true and another group of statements if the Boolean expression is false.

13

Flowchart

False

Display Message Nice weather we’re having!

Temperature < 40?

True

Display Message A little cold, isn’t it?

General Format If expression Then statement (more statements may follow) Else statement (more statements may follow) End If

 If the expression is True o execute the statements between If…Then and Else  If the expression is False o execute the statements between Else and End If

Two Mutually Exclusive Choices  The If…Then…Else has two choices o The condition will either be True or False o So either the Then clause or Else clause will be executed o These are two mutually exclusive choices

If…Then … Else Example 'Program that uses the If ... Then ... Else Statement Sub prog03() Dim num As Integer num = InputBox("Write an integer number") ActiveCell.Value = "Number = " & num If num < 100 Then ActiveCell.Offset(1, 0).Value = "The number is less than 100" ActiveCell.Offset(1, 0).Interior.Color = RGB(255, 0, 0) Else ActiveCell.Offset(1, 0).Value = "The number is not less than 100" ActiveCell.Offset(1, 0).Interior.Color = RGB(0, 0, 255) End If End Sub

If…Then vs. If…Then…Else  The If…Then construct will execute or ignore a group of statements (do something or do nothing)  The If…Then…Else construct will execute one group of statements or another group (do this or do that)

Exercise  Write a program to compute and display a person’s weekly salary as determined by the following conditions: if the hours worked are less than or equal to 40, the person receives $12.00 per hour; otherwise, the person receives $480.00 plus $17.00 for each hour worked over 40 hours. The program should request the hours worked as input and display the salary as output. You should use the cells show in the image on the right and the worksheet 2.

The If...Then...ElseIf Statement The If...Then...ElseIf statement is like a chain of If...Then...Else statements. They perform their tests, one after the other, until one of them is found to be true.

20

Flowchart Very cold? False Chilly?

True

True

Wear a heavy jacket

Wear a light jacket

False Windy?

True

Wear a windbreaker

False Hot? False

True

Wear no jacket

General Format If expression Then statement (more statements may follow) ElseIf expression Then statement (more statements may follow) (put as many ElseIf statements as necessary) Else statement (more statements may follow) End If  This construction is like a chain of If...Then...Else statements  The Else part of one statement is linked to the If part of another

Multiple Possible Choices  The If…Then…ElseIf statement allows for an entire series of possible choices  Each of the series of conditions in an If…Then…ElseIf is tested in sequence  When a condition is true, the remaining conditions are ignored  The order of the conditions is vital o Wrong order can result in wrong decision - called a logic error o What if it’s chilly and windy? o If windy is tested before chilly, you’d go out with a windbreaker when you need a jacket

Example of ElseIf Usage Sub prog04() Dim temperature As Integer temperature = InputBox("Write the temperature") ActiveCell.Value = "The temperature is " & temperature If Temperature < 40 Then ActiveCell.Offset(1, 0).Value = "Wear a heavy jacket" ElseIf

Temperature <= 65 Then

ActiveCell.Offset(1, 0).Value = "Wear a light jacket"

ElseIf Temperature < 80 Then ActiveCell.Offset(1, 0).Value = "Wear no jacket" End If End Sub

Example of ElseIf Usage (2) 1. Does the order of these conditions matter? 2. What happens if we reverse the order?

Using a Trailing Else If Temperature < 40 Then

 A sequence of ElseIf statements may end with a plain Else, called a trailing Else  If none of the conditions are True, the trailing Else statement(s) will be executed  The trailing Else catches any value that falls through the cracks

ActiveCell.Offset(1, 0).Value = "Wear a heavy jacket" ElseIf Temperature < 65 Then

ActiveCell.Offset(1, 0).Value = "Wear a light jacket " Else ActiveCell.Offset(1, 0).Value = "Wear no jacket " End If

Exercise  The following table shows the approximate speed of sound in air, water, and steel. Medium

Speed (feet per second)

Air

1100

Water

4900

Steel

16400

 Write a program that displays a menu allowing the user to select air, water, or steel. After the user has made a selection, he or she should be asked to enter the distance a sound wave will travel in the selected medium. The program will then display the amount of time it will take.  Input validation: Check that the user has selected one of the available choices from the menu. Do not accept distances less than 0.  You must to use InputBox and MsgBox functions.

The Select Case Statement In a Select Case statement, one of several possible actions is taken, depending on the value of an expression.

The Select Case Statement  Similar to If…Then…ElseIf o Performs a series of tests o Conditionally executes the first true condition

 Select Case is different in that: o A single test expression may be evaluated o The test expression is listed once o The possible values of the expression are then listed with their conditional statements

 Case Else may be included and executed if none of the values match the expression

Select Case General Format Select Case TestExpression [Case ExpressionList [one or more statements]] [Case ExpressionList [one or more statements]] ' Case statements may be repeated ' as many times as necessary. [Case Else [one or more statements]] End Select

Select Case Flowchart Example

Test Expression

False



False



False

True

True

True

Operation 1

Operation 2

Operation 3

Default

Operation 4

Select Case Statement Example Sub prog05()

Dim Day As Integer Day = InputBox("Write 1 to Monday, 2 to Tuesday ... ") Select Case Day Case 1 MsgBox ("Day 1 is Monday.") Case 2 MsgBox ("Day 2 is Tuesday.") Case 3 MsgBox ("Day 3 is Wednesday.")

Case Else MsgBox ("That value is invalid.") End Select End Sub

More about the Expression List: Multiple Expressions 

The Case statement’s expression list can contain multiple expressions, separated by commas Sub prog06() Dim Number As Integer, Status As String Number = InputBox("Write numbers from 1 to 10 ") Select Case intNumber Case 1, 3, 5, 7, 9 Status = "Odd" Case 2, 4, 6, 8, 10 Status = "Even" Case Else Status = "Out of Range" End Select ActiveCell.Value = Status End Sub

More about the Expression List: String Values  The Case statement can test string values Sub prog07() Dim Animal As String Animal = InputBox("Write an animal

Select Case Animal Case "Dogs", "Cats" MsgBox ("House Pets") Case "Cows", "Pigs", "Goats" MsgBox ("Farm Animals") Case "Lions", "Tigers", "Bears" MsgBox ("Oh My!") End Select End Sub

")

More about the Expression List: Relational Operators 

You can use relational operators in the Case statement



The Is keyword represents the test expression in the relational comparison Sub prog08() Dim Temperature As Double Temperature = InputBox("Write the temperature ") Select Case Temperature Case Is <= 75 ActiveCell.Value = "Too Cold!" Case Is >= 100 ActiveCell.Value = "Too Hot!" Case Else ActiveCell.Value = "Just Right!" End Select End Sub

More about the Expression List: Ranges of Values Select Case Score

 You can determine whether the test expression falls within a range of values  Requires the To keyword o Smaller number on the left o Larger number on the right o Numbers on each side are included in the range

Case Is >= 90

Grade = "A" Case 80 To 89 Grade = "B" Case 70 To 79 Grade = "C" Case 60 To 69 Grade = "D" Case 0 To 59 Grade = "F" Case Else MsgBox("Invalid Score") End Select

Exercise: Select Case  The following table shows the approximate speed of sound in air, water, and steel. Medium

Speed (feet per second)

Air

1100

Water

4900

Steel

16400

 Write a program that displays a menu allowing the user to select air, water, or steel. After the user has made a selection, he or she should be asked to enter the distance a sound wave will travel in the selected medium. The program will then display the amount of time it will take.  Input validation: Check that the user has selected one of the available choices from the menu. Do not accept distances less than 0.  You must to use InputBox and MsgBox functions.

References  [1] Alexander, Michael; Kusleika, Dick. Excel 2016 Power Programming with VBA. Reprint Edition. John Willey & Sons. 2017.  [2] Walkenbach, John. Excel 2013 Power Programming with VBA. John Willey & Sons. 2013.

 [3] Gaddis, Tony; Irvine, Kip R. Starting Out with Visual Basic. Student Value Edition. Pearson. 2016.