1 / 17

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

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

meir
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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!

More Related