Lab 6 (1) Range Review, Control Logic and Loops

1. Lab 6 (1) Range Review, Control Logic and Loops ► Control Logic and Loops ► Exercise

2. Object Browser • View Object Browser • F2 (In VBE)

3. Control Logic – Chpt 7 Logical constructions control the sequence of statements in a procedure, and thus enable a decision making capability. The following two constructions are used most frequently in VBA: If Constructions Case Constructions

4. If Constructions (1) • Basic syntax (In one single line):Ifcondition Thenstatement A [Elsestatement B] Example: If grade > 90 Then MsgBox ″Good Performance! ″ Else _ MsgBox ″ Need to work harder. ″ • More complicated syntax:Ifcondition1 Then statements1 [ElseIfcondition2 Then statements2 ElseIfcondition3 Then statements3 …… Else other statements] End IF Example:

5. If Constructions (2) Sub GreetMe() If Time < 0.5 Then MsgBox "Good morning!" & " It’s now " & Time, , “The Time” ElseIf Time >= 0.5 And Time < 0.75 Then MsgBox "Good afternoon!" & " It’s now " & Time, , “The Time” Else MsgBox "Good evening!" & " It’s now " & Time, , “The Time” End If End Sub (In VBA, Timefunction returns a value that represents current time of the day. The time of day is expressed as a fractional value, i.e.12 pm: Time = 0.5, 6pm: Time = 0.75)

6. If Constructions (3) • Nested If statements (under such circumstances, indentation is very important for ease of reading):Example: If Product = “Widgets”Then If NumberOrdered <= 200Then UnitCost = 1.30 Else UnitCost = 1.20 End IF ElseIf Product = “Gadgets”Then If NumberOrdered <= 500Then UnitCost = 2.70 ElseIf NumberOrdered <= 600Then UnitCost = 2.60 Else UnitCost = 2.50 End If Else UnitCost = 2.00 End IF

7. Case Constructions (1) • If constructions can become fairly complex when there are multiple conditions with each condition having its own codes. • Case construction is a good alternative for choosing among three or more options • General Syntax: Select CaseVariableName Case Value1 statements1 Case Value2 statements2 … … [Case Else other statements] End Select

8. Case Constructions (2) Example: Dim UnitPrice as single, UnitCost as single UnitCost = InputBox(“Please enter the unit cost:”,”Unit Cost” Select Case ProductIndex Case Is <= 3 [Is keyword for comparison operators] UnitPrice = 1.2 * UnitCost Case 4 To 6 [To keyword specifies a range of values] UnitPrice = 1.3 * UnitCost Case 7 UnitPrice = 1.4 * UnitCost Case Else UnitPrice = 1.1 * UnitCost End Select MsgBox “The unit cost was “ & format(UnitCost,”$##.#0) & “the unit price was “ & format(UnitPrice,”$##.#0) & “.”,,”Unit Price” ‘ Is and To are keywords

9. Loops Perhaps the single most useful feature of computer programs is their ability to loop – to repeat the same type of task any number of times. In VBA, the following two types of loops are used most often: For loops – you determine when the loop is done Do loops – program determines when the loop is done

10. For-Next Loops • Syntax: For counter=startvalueToendvalue [Stepstepvalue] statements Next [counter] • counter is the name of a numeric variable and it keeps track of how many times the statements are repeated – common names: i, j, k… • startvalue, endvalue, and stepvalue must be numeric and they can be either positive or negative, integer or non-integer (default stepvalue is 1) • Example: add 1, 3, 5, …, 49 Sub AddOddInteger() Dim Sum As Integer, i As Integer Sum = 0 For i = 1 To 49 Step 2 Sum = Sum + i Next i MsgBox "The sum of odd numbers from 1-50 is " & Sum End Sub • For loops can also be nested

11. For Each Loops (1) • For Each loops are used to loop through all objects in a collection • Syntax: Dim itm As Object For Eachitm in Collection statements Next • Object and Collection will vary depending on the type of collection

12. For Each Loops (2) • Example: search through all worksheets of the active workbook for a sheet named Data. If you find one, you can exit the loop immediately. Sub FindWorksheet() Dim ws As Worksheet, Found As Boolean Found = False For Each ws in ActiveWorkbook.Worksheets If ws.Name = “Data” Then Found = True Exit For End If Next If Found = True Then MsgBox “There is a worksheet named Data.” Else MsgBox “There is no worksheet named Data.” End If End Sub

13. Do Loops (1) The Do loops can be used when you need to loop while some condition holds or until some condition holds. There are following four variations of Do loops: 1. Do Until…Loop • Syntax: Do UntilCondition statements Loop 2. Do While…Loop • Syntax: Do WhileCondition statements Loop

14. Do Loops (2) 3. Do… Loop Until • Syntax: Do statements Loop Until Condition 4. Do…Loop While • Syntax: Do statements Loop WhileCondition • Major difference: the statements in the loop in the first two variations might never be executed, but they will certainly be executed at least once in the last two variations • Exit Do statement can be used to exit a Do Loop prematurely • Be careful of infinite loops

15. Do Loops (3) Count = 1 Do While Count <= 3 MsgBox Count Count = Count + 1 Loop Count = 1 • Example: Do While… Loop Count <= 3 F Loop 3 times T Display Count Count = Count + 1

16. Do Loops (4) Count = 1 Do MsgBox Count Count = Count + 1 Loop Until Count >3 Count = 1 • Example: Do …Loop Until Display Count Loop 3 times Count = Count + 1 Count > 3 T F

17. Assignment 1 • Rewrite the previous GreetMe example by using the Case Construction instead of the IF-Then Construction. • Hint: Time is not inputted or declared. It is a VBA keyword. Create a sub and use the case construction without declaring any variables!