1 / 13

Control Logic and Loops

Control Logic and Loops. While, For, and Do loops. If Constructions. Used when there are one or more possible conditions, each of which requires its own code. A condition is any expression that is either true or false.

ahmed-case
Télécharger la présentation

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. Control Logic and Loops While, For, and Do loops

  2. If Constructions • Used when there are one or more possible conditions, each of which requires its own code. • A condition is any expression that is either true or false. • Examples: Total <= 200; StreetName = “Data”; FoundIt = True (FoundIt is a Boolean). • Versions of If condition: • ‘ No Else statement • If NumberOrdered <= 200 Then • UnitCost = 1.30 • End If • ‘Use of Else statement • If NumberOrder <= 200 Then • UnitCost = 1.30 • Else • UnitCost = 1.25 • End If

  3. If Constructions • ‘Use of the ElseIf statement • If NumberOrdered <= 200 Then • UnitCost = 1.30 • ElseIf NumberOrder <= 300 Then • UnitCost = 1.25 • ElseIf NumberOrder <= 400 Then • UnitCost = 1.20 • Else • UnitCost = 1.15 • End If

  4. Nested While Constructions • ‘ Creates a Triangle • Dim r As Integer, c As Integer • r = 1 • Range("a1:j10").Select • With Selection • .HorizontalAlignment = xlCenter • .VerticalAlignment = xlCenter • End With • With Selection.Font • .Name = "Arial" • .Size = 16 • End With • While r <= 10 • c = 1 • While c <= r • Sheet3.Cells(r, c) = "*" • c = c + 1 • Wend • r = r + 1 • Wend • Note: rows are always on the outside of the nested loop and columns are always on the inside of the nested loop.

  5. Compound (And, Or, Not) Conditions • While r <= 10 • c = 1 • While c <= 10 • If r = 1 Or r = 10 Then • Sheet1.Cells(r, c) = "*" • ElseIf r > 1 And c <> 1 And c < 10 Then • Sheet1.Cells(r, c) = " " • ElseIf r > 1 And r < 10 And c = 1 Then • Sheet1.Cells(r, c) = "*" • ElseIf r > 1 And r < 10 And c = 10 Then • Sheet1.Cells(r, c) = "*" • End If • c = c + 1 • Wend • r = r + 1 • Wend • The Not condition will reverse the test condition. • Example: If Not (V1 >= 200) Then ‘ The test is actually V1 < 200

  6. Case Constructors • Note: There are three ways values are specified after the key Case Is : <=3, 4 To 6, and 7 are accepted test conditions. Is and To are keywords. The syntax rules are as follows- Delimited list of one or more of the following forms: expression, expression To expression, Is comparisonoperator expression. • The To keyword specifies a range of values. • Use the Is keyword with comparison operators (except Is and Like) to specify a range of values. If not supplied, Is keyword is automatically inserted. • Code Example: • Private Sub cmdEnter_Click() • Dim message As String • mAccessCode = txtDisplay.Text • ' txtDisplay.Text = "" • Select Case mAccessCode • Case Is < 1000 ' Panic codes • message = "Access Denied" • Beep • Case 1645 To 1689 • message = "Technician Personnel" • Case 8345 • message = "Custodial Services" • Case 55875 • message = "Special Services" • Case 999898, 1000006 To 1000008 • message = "Scientific Personnel" • Case Else • message = "Access Denied" • End Select • lstLogEntry.AddItem Now & Space$(3) & message • End Sub

  7. For Loops • Syntax: For I = First To Last [Step Increment] • I is the counter; First is the beginning value; Last is the end of the loop. The default increment is one. • Note: The Step is optional and allows you increase the counter. • Example: For x = 1 To 20 Step 2 • Sheet2.Cells(x, 1) = x • Next • Code will write x to the spreadsheet in increments of 2, thus 1,3,5,7,9,11,13,15,17,19 will be written. • The counter can be made to count backwards by making the Step a negative number.

  8. Nested For Loops • Dim r As Integer, c as Integer • For r = 1 To 10 • For c = 1 To r • Sheet1.Cells(r, c) = "*" • Next • Next • As stated before, the columns change faster than the rows.

  9. For Each Loops • 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 • This is generally used with a collection of objects. • Syntax: Dim item As Object • For Each item In Collection • statements • Next • Where item is a generic name for a particular item in a collection. • Note: In the above code Object has been replaced by Worksheet.

  10. For Each Loops • The code below generalizes the previous code so it counts the number of worksheets with the name that starts with “Sheet”. The code uses the Left string function. • Dim ws As Worksheet, Counter As Integer • Counter = 0 • For Each ws In ActiveWorkbook.Worksheets • If Left(ws.Name, 5) = “Sheet” Then • Counter = Counter + 1 • End If • Next • MsgBox “There are “ & Counter & “ sheets with a name starting with Sheet.” • Note: For Each does not provide a counter.

  11. For Each With Ranges • The Range object is a particular type of collection. • There is no Ranges collection, but the singular Range acts like a collection. • The individual items in the collection are the cells in the Range. • The following code counts formulas in the range using the HasFormula property. • Dim cell As Range, Counter As Integer • Counter = 0 • For Each cell in Range(“Data”) • If cell .HasFormula = True Then Counter = Counter + 1 • Next • MsgBox “There are “ & Counter & “ cells in the Data range that contain formulas.

  12. Do Loops • For loops execute a set number of times. There will be times when you need to loop while a certain condition is true or until some condition holds. • There are four variations of the Do Loop: two check a condition at the top and two check the condition at the bottom. • Syntax: (1) Do Until condition • Statements • Loop • (2) Do While condition • Statements • Loop • (3) Do • Statements • Loop Until condition • (4) Do • Statements • Loop While condition • Conditions at the top are checked before going through the body of the loop. The statements in the body will only be executed only if the condition is false for Until or true for While. • Conditions at the bottom of loop allow the program to decide whether to go through the loop again. This guarantees at least one execution of the code. • Exit Do statement is the same as Exit For statement and allows premature exit of the loop. • Possibility of infinite loops is a problem with Do loops since there is no counter, it is left up to the programmer to provide one. If you do not provide a counter the loop will never end. • If you are stuck in an infinite loop Ctrl + Break will end the program.

  13. Do Loops Coded Examples • Dim product As Integer • product = 2 • Do While product <=1000 • product = product * 2 • Loop • Dim product As Integer • product = 2 • Do Until product >1000 • product = product * 2 • Loop • Dim counter As Integer, column As Integer • counter = 1: column = 1 • Do • Sheet1.Cells(1, column) = counter • counter = counter + 1 • column = column +1 • Loop While counter <= 10 • Dim counter As Integer, column As Integer • counter = 1: column = 1 • Do • Sheet1.Cells(1, column) = counter • counter = counter + 1 • column = column +1 • Loop Until counter = 10

More Related