unit 8: control statements
TRANSCRIPT
![Page 1: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/1.jpg)
Excel Macros Level 1
Control Functions
![Page 2: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/2.jpg)
M. Campbell - 2010 2
Control Structures
All applications can be written in terms of: Sequence Structures Selection Structures (or Selection Statements) Repetition Structures (or Repetition Statements)
4/29/2010
p. 93
![Page 3: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/3.jpg)
M. Campbell - 2010 3
Flowchart Legend
4/29/2010
![Page 4: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/4.jpg)
M. Campbell - 2010 4
Sequence Structures
Typically built into languageAllows statements to be
executed one after another
Add x and y together.Display the result.
4/29/2010
p. 93
![Page 5: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/5.jpg)
M. Campbell - 2010 5
Selection Structures
Allows code to be executed based on conditions
If the number is even display it
Otherwise, do not display it
4/29/2010
![Page 6: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/6.jpg)
M. Campbell - 2010 6
Repetition Structures
Allows code to be repeated as necessary
While the number is less than or equal to 100, display it.
Once it is over 100, end the program
4/29/2010
![Page 7: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/7.jpg)
M. Campbell - 2010 7
The If…Then Statement
Selection Statement:
If condition Then' statements go here
ElseIf condition2 Then' more statements
Else' more statements
End If4/29/2010
p. 93
![Page 8: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/8.jpg)
M. Campbell - 2010 8
The For Loop
Repetition Statement:
For counter = start To end' statements go here
Next counter
4/29/2010
p. 94
![Page 9: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/9.jpg)
M. Campbell - 2010 9
The For Loop
By default a For loop: Sets counter to the value start Executes the code within the loop body Increments counter Continues looping until counter exceeds the end
value
Can use Exit For to leave For loop early
4/29/2010
p. 95
![Page 10: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/10.jpg)
M. Campbell - 2010 10
The For Loop
Can change the step size:For i = 1 To 10 Step 2
' code block goes hereNext i
Can change the direction:For i = 1 To 10 Step -5
' code block goes hereNext i
4/29/2010
p. 95
![Page 11: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/11.jpg)
M. Campbell - 2010 11
The For Each Loop
Repetition Statement:
For Each objectVar In collection' code block goes here
Next objectVar
4/29/2010
p. 96
![Page 12: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/12.jpg)
M. Campbell - 2010 12
The For Each Loop
Loops through a collection of objectsEach iteration places the next object in the
variable objectVar
4/29/2010
p. 96
![Page 13: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/13.jpg)
M. Campbell - 2010 13
The Do Loop
Repetition Statement:
Do While condition' code block goes here
Loop
Repeats the code block while condition is true
4/29/2010
p. 96
![Page 14: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/14.jpg)
M. Campbell - 2010 14
The Do Loop
Repetition Statement:
Do' code block goes here
Loop While condition
Repeats the code block while condition is trueWill execute at least once (as condition is checked last)
4/29/2010
p. 96
![Page 15: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/15.jpg)
M. Campbell - 2010 15
The Do Loop
Repetition Statement:
Do Until condition' code block goes here
Loop
Repeats the code block until condition becomes true
4/29/2010
p. 96
![Page 16: Unit 8: Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022081512/55897877d8b42a3f1e8b45fe/html5/thumbnails/16.jpg)
M. Campbell - 2010 16
The Select Case Statement
Selection Statement:
Select Case testVarCase value1
' code to execute if testVar = value1Case value2
' code to execute if testVar = value2Case Else
' statements to execute otherwiseEnd Select
4/29/2010