Chapter 7 Control Logic and Loops If Constructions

Chapter 7 Control Logic and 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. – Examples: Total <= 200; Street. Name = “Data”; Found. It = True (Found. It is a Boolean). • Versions of If condition: • ‘ No Else statement – – – – – If Number. Ordered <= 200 Then Unit. Cost = 1. 30 End If ‘Use of Else statement If Number. Order <= 200 Then Unit. Cost = 1. 30 Else Unit. Cost = 1. 25 End If

If Constructions • • • ‘Use of the Else. If statement If Number. Ordered <= 200 Then Unit. Cost = 1. 30 Else. If Number. Order <= 300 Then Unit. Cost = 1. 25 Else. If Number. Order <= 400 Then Unit. Cost = 1. 20 Else Unit. Cost = 1. 15 End If

Nested If Constructions • • • • • • ‘ Creates a Triangle Dim r As Integer, c As Integer r=1 Range("a 1: j 10"). Select With Selection. Horizontal. Alignment = xl. Center. Vertical. Alignment = xl. Center End With Selection. Font. Name = "Arial". Size = 16 End With While r <= 10 c=1 While c <= r Sheet 3. Cells(r, c) = "*" c=c+1 Wend r=r+1 Wend Note: rows are always on the outside of the loop and columns are always on the inside of the loop.

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

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: Select Case Product. Index Case Is <= 3 Unit. Price = 1. 2 * Unit. Cost Case 4 To 6 Unit. Price = 1. 3 * Unit. Cost Case 7 Unit. Price = 1. 4 * Unit. Cost Case Else Unit. Price = 1. 1 * Unit. Cost End Select

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 – Sheet 2. 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.

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

For Each Loops • • • • • Dim ws As Worksheet, Found As Boolean Found = False For Each ws In Active. Workbook. 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.

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 Active. Workbook. Worksheets • If Left(ws. Name, 5) = “Sheet” Then • Counter = Counter + 1 • End If • Next • Msg. Box “There are “ & Counter & “ sheets with a name starting with Sheet. ” • Note: For Each does not provide a counter.

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 Has. Formula property. • Dim cell As Range, Counter As Integer • Counter = 0 • For Each cell in Range(“Data”) • If cell. Has. Formula = True Then Counter = Counter + 1 • Next • Msg. Box “There are “ & Counter & “ cells in the Data range that contain formulas.

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.