Chapter 7

1. Chapter 7 Control Logic and 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 If 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 loop and columns are always on the inside of the 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: • Select Case ProductIndex • Case Is <= 3 • UnitPrice = 1.2 * UnitCost • Case 4 To 6 • UnitPrice = 1.3 * UnitCost • Case 7 • UnitPrice = 1.4 * UnitCost • Case Else • UnitPrice = 1.1 * UnitCost • End Select

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.