170 likes | 274 Views
Lab 6 (1) Range Review, Control Logic and Loops. ► Control Logic and Loops ► Exercise. Object Browser. View Object Browser F2 (In VBE). Control Logic – Chpt 7. Logical constructions control the sequence of statements in a procedure, and thus enable a
E N D
Lab 6 (1) Range Review, Control Logic and Loops ► Control Logic and Loops ► Exercise
Object Browser • View Object Browser • F2 (In VBE)
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
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:
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)
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
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
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
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
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
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
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
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
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
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
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
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!