program flow o sequence o selection o repetition - cribmecribme.com/cu/data/computer...
TRANSCRIPT
1
GEEN 1300Introduction to Engineering Computing
Class meeting #13Wednesday, October 7th
Excel & Visual Basic for Applications (VBA)
Program flowo sequence
1Homework #6, posted, due next Wednesday
o selectiono repetition
Program Flow
3 fundamental structures
sequence
selection
repetitionSequence
execute one statement at a time, in sequential order
This is the encompassingstructure of all programs:
Begin
2
End
2
Selection
program branching based on decisions
Program does one thingth d di
logicalcondition
TF
do this,if TRUE
do this,if FALSE
or another dependingwhether a logical conditionevaluates to TRUE or FALSE
3
There a several specific structures that providefor selection.
Repetition
provides for looping or iteration
there must be something inthe loop that affects the decision;
donel i
TF
p ;otherwise you’ll never get out!
4
looping?
There are several specific types ofrepetition structures
3
Selection Structures in VBA
One-way If
logical TF
If <condition> Then<do this> statements
End If
General form:“If-Then”
condition
do this,if TRUE
otherwise,just go on
d
Example:
If Xval > 10 ThenYval = Xval – 10Xval = 1
End If
5
One-line, one-statement form:
If n = 2 Then MsgBox “Yo, bubba”
Note: no End If req’d
Indentation shownis not req’d, but isgood style. Do it.
One-way If
“If-Then”
Example: Create a function that squaresa numerical quantity and carries the signof that quantity to its square.
f = x2
x < 0?
TF
f = - f
6
4
Two-way If
“If-Then-Else”
logical TF
General form:
If <condition> Then<statements>
Else< t t t >condition
do this,if TRUE
else do this,if FALSE
<statements>End If
Example:
If Switch ThenM B “Li ht i ON”
7
MsgBox “Light is ON”Else
MsgBox “Light is OFF”End If
“Switch” would be a Boolean (logical)variable here, previously set to TRUE or FALSE
This is the mostcommon Ifstructure used
Two-way If
“If-Then-Else”
Example: a 2-piece engineering formula
16
R 2000Rf R0.079
R 2000
1 4
R 2000R
R <2000 ?
TF
Fluid = 16/R0.079R1/4
Fluid =
8
5
Multi-alternative If“If-Then-ElseIf ”
condition1
TF
code
If <condition 1> Thencode block 1
ElseIf <condition 2> Thencode block 2
El If < diti > Th condition2
TF
conditionn
TF
codeblock
1codeblock
2
ElseIf <condition n> Thencode block n
Elseelse code block
End If
9
else do this ifall conditions
fail
codeblock
n
Notice: subsequent if tests come off theFALSE branch of the previous tests
Multi-alternative If Example
Develop a VBA function to compute temperature (C) fromvoltage (mV) for a thermocouple (Type J, iron-constantan).
2 3 4
5 6 7
T 19.528 V 1.2286 V 1.0752 V 0.59087 V
0 17257 V 0 028132 V 0 0023963 V
5 6 7
5 8
0.17257 V 0.028132 V 0.0023963 V
8.3823 10 V 8.095 V 0.000 mV
A
2 3 4 4
6 5 8 6 10 7
T 19.784 V 0.20012 V 0.010370 V 2.5497 10 V
3.5852 10 V 5.3443 10 V 5.0999 10 V
0.000 V 42.919 mV
B
10
Regressed polynomials from NIST
2 3 4
6 5
T 3113.6 300.54 V 9.9477 V 0.17028 V 0.0014303 V
4.7389 10 V 42.919 V 69.553 mV
C
6
mV <-8.095 ?
TF
mV <=0 ?
TF "mV out ofrange low"
Multi-alternative If Example
0 ?
mV <=42.919 ?
TF useformula
Auseformula
B
g
mV <=69.553 ?
TF
useformula
C"mV out of
hi h"
11
Crange high"
Note: the Else part is optional anddoesn’t need to be there if there isno Else consequence
Multi-alternative If Example
12
7
Multi-alternative If Example
13
Select-Case Select Case <test expression>Case <list 1>
code block 1Case <list 2>
code block 2
whichcase
?
Case 1 Case 2 Case 3 Case else
Case Elseelse code block
End Select
14[often used for “menu”
alternatives]
Case 1 Case 2 Case 3 Case else
Case Elseis optional
9
Repetition Structures in VBA
general loop: Do . . . Loop
count-controlled loop: For . . . Next
General Do Loop
Dopre-test code block
If <condition> Then Exit Dopost-test code block
Loop
General Do . . . Loop“mid-test loop”
17
Loopdone
looping?
TF
Dox = Sqr(x)
If x < 1.1 Then Exit Dox = x^1.5
Loop
Example:
Example of Mid-test Loop -- Input Validation
inputvalid
?
TF
get input
correctionmessage
18
message
10
Special Cases of the General Do . . . Loop
Do – While “pre-test loop” Do While <condition>loop code
Loop
conditionTF
loop code
Do While AOKAOK = FALSE
Loop
Example:
19
p
There is no “pre-test” code,and the loop is continued onTRUE / exited on FALSE
Do – Until “post-test loop”
Doloop code
loop code
donelooping
TF
pLoop Until <condition>
Doi = i + 1
Loop Until i > ilim
Example:
20
looping?
There is no “post-test” code.
11
Count-controlled Iteration
For . . . Next
index = startY
For index = start To limit Step incrementloop code
Next index
Example:
if Step incrementis left out, a stepof 1 is default
index = start
index = index+ increment
indexpasseslimit ?
loop code
N
sum = 0For i = 1 to n
sum = sum + x(i)Next i
Here, x is an arraywith n elements and
21
Used frequently with arrays
with n elements andx(i) refers to the ith
element of the array
We will see more of this structure in coming classes
Count-controlled Iteration
For . . . Next
Example
n
i 1
n n 1i
2
Validate
22
12
By using combinations of the sequence, selection andrepetition structures, we can construct algorithms toaccomplish a wide variety of computations requiredin engineering and science.
Where to from here?
implementing numerical methods like bisectionand linear interpolation automatically with VBA
working with array data types
23
array-based numerical methods
user interfaces: input boxes, message boxesand user forms