Lecture Note
Numerical Analysis of Engineering Systems
-
University:
California State University, Northridge -
Course:
ME 309 | Numerical Analysis of Engineering Systems Academic year:
2014
-
Views:
83
Pages:
13
Author:
u1cen279b
Outline
More VBA Review
• Review last class
– VBE Editor, Variables and Declarations,
Arithmetic Operators and Statements
•
•
•
•
•
•
Mechanical Engineering 309
Numerical Analysis of
Engineering Systems
Logical and Relational Operators
More on variables and constants
Program decisions using If statements
Conditional looping (Do)
Count-controlled looping (For – Next)
Work on programming assignment
Review Declaring Variables
Multiline: space
underscore
• Use Option Explicit to force declarations
Project
Explorer
– Common data types: long, double, string,
Boolean and date
– Variant data type can handle any type
The Visual Basic
Editor (VBE)
• Is default for variables not assigned a type
– Examples of Dim statements used to declare
variables
Dim s As String ‘Comment here
Dim y As Double, z As Double
Dim x, y As Double ‘Do not use
Properties
Window
Code Window
3
Review Relational Operators
Logical Operators
•
•
•
•
•
Relational
Combine true/false values
Precedence
Operators are Not, And, Or
Not
x And y is true if both x and y are true And
Or
x Or y is true if either x or y is true
Not x is true if x is false and is false if x
is true (Not is like a unary –)
• Is the following true or false?
(6 < 3) Or ( (12 > 2 ) And (Not (6<>6)))
6 < 3 Or 12 > 2 And Not 6<>6
• Program logic requires choices based
on expressions that are true or false
• Relational operators compare other
variables and have true or false results
• The operators are <, <=, =, >, >=, <>
– Usual definitions with <> as not equal
• Literally <> is less than or greater than
– Use abs(x – y) <= eps * (abs(x) + abs(y))
for equality test on real variables
5
6
1 Operators in Precedence Order
Exercise
• A year is a leap year if
Parentheses ( Evaluate me first )
Exponentiation ^
Unary minus – (E.g. –x)
Multiply/Divide * /
Integer Division \
Remainder Mod (E.g. 7 Mod 4 = 3)
Addition/Subtraction + –
String concatenation &
Relational < <= = >= > <>
Logical in following order: Not And Or
– It is evenly divisible by 4 and not evenly
divisible by 100
– Or it is evenly divisible by 400
• Y is evenly divisible by 4 if Y Mod 4 = 0
• Write an expression that is true if a year is
a leap year
( (year Mod 4 = 0) and not(year mod 100 = 0) ) or( year mod 400 = 0)
year Mod 4 = 0 and not year mod 100 = 0 or year mod 400 = 0
7
8
Variable Scope, Persistence
Examples
• Scope is where a variable is defined
• Persistence is how long it is defined
• Variables declared in a procedure have
procedure scope and persistence
Sub test()
Dim count as Long
count = count + 1
End Sub
– They are defined only for that procedure
– They are reinitialized each time the
procedure is called
– They are called local variables
– Use Static instead of Dim for persistence in
a procedure variable between calls
Each time sub test
is called the
variable count is
set to zero
All variables are
initialized to zero
Sub test2()
Static count as Long
count = count + 1
End Sub
Each time sub test2
is called the variable
count starts with its
value from the
previous call
9
Scope and Persistence II
10
Another Example
• Variables declared in declarations section
of module (before first procedure) exist for
all procedures in module
• For a workbook with multiple modules
– Module variables in declarations section with
Dim or Private have scope of module
– Variables set as Public in declarations have
scope of all modules are global variables
Do not
use
global
variables
in to
– Limit
use
of module
module or
and
global
variables
your programs
foristhis
course
provide
code that
easier
to understand
11
• k and x are module
variables (Dim and
Private are equivalent)
• d is a global variable that
could be see by routines
in another module (not
shown here)
• Note use of MsgBox for
output shown below
Note use
of “ _” for
multiline
statements
Option Explicit
String
Dim k as Long
constant
Private x as Double
& is string
Public d as Date
concateSub First()
nation
Call Second()
operator
MsgBox "k = " & k & _
vbCrLf & "x = " & x & _
vbCrLf & "d = " & d
End Sub
vbCrLf
moves
Sub Second()
output to
k=2
new line
x = 4 * atn(1)
d = Now()
12
End Sub
2 Type Conversion
Defined Constants in Office
• Last slide showed example of type
conversion done automatically
– “k = ” & k converted numeric k to string
• When mixed types are used VBA tries
to determine the conversion desired by
the user and does it
• You can use a set of functions that do
the conversions: CDbl(var), CLng(var),
CStr(var), CInt(var), CBool(var)
• Symbolic constants from macro
recorder for Office (prefix mso), Excel
(prefix xl) and VBA (prefix vb)
• These usually define options which are
normally simple integers
• Giving the constants a name makes
them easier to remember
• If you want blue, you can use color =
vbBlue; you don’t need the blue number
13
Comments and Colors
14
Choice Statements
• Comments may be placed in the code
on a new line or at the end of a
completed line, not after a continuation
must
have a Boolean value
If Then of true or false
End If
is false>
• The If statement
– Start with an apostrophe
– Default color is green in VBE
• Keywords (Function, If, As, …) are blue
– Editor will convert letters to upper case
– Will change colors or do case conversion if
spelling is not correct
• Alternative version for one statement in If
If Then
15
If – Else If
If Then
ElseIf Then
ElseIf Then
May have zero or
many ElseIf’s
Else
End If
17
16
If – Else If Explained
– If any condition is true, the
statements following the If
or Else If are executed
– Once any statements are
executed transfer to the first
statement after the End If
– Statements for only the first
true condition are executed
– The Else block is optional
• If no conditions are true those
statements are executed
If Then
then
then
Else
10
• What is the code to compute y for any x?
If x < 0 then
y = 0
>=10then
and x < 1 then
ElseIf x <
If x < 0 then
y = 0
ElseIf x < 1 then
y = x
ElseIf x <= 10 then
y = x^2
Else
ElseIf x > 10
y = 100
End If
Do not put a space
between the else
and the if of ElseIf
Can type in lower or
upper case – VBE
will rewrite in its
preferred case
19
Looping
20
Count Controlled Loop
• Count control loop repeats code a fixed
number of times
• Conditional looping repeats while a
condition is true or until a condition is
false
• Both types of loops may be nested
• May use Exit For or Exit Do statements
to exit loop before normal exit
For = to
Next
For = to _
Step
Next
If Step is not specified, = 1* *Increment
may be
is an integer (or long) variable
negative,
is the initial value of
22
but not zero
21
Count Controlled Loop
Count Controlled Examples
For = to Step
Next
• For loop executes repeatedly with the
increasing by the each time
– Value of counter may be used in loop
• Loop not executed if becomes greater
than so loop is executed nTimes times
– nTimes = ( – ) / + 1
• Loop not executed if nTimes <= 0
For = 1, is the last
value of used in the loop
23
For k = 1 to 11 Step 3
MsgBox k
Next k Colon (:) allows multiple
statements per line
x = 1 : term = x : sum = term
For n = 1 to 10
term = term * x / n
sum = sum + term
Next k
relErr = abs(sum/exp(x) – 1)
𝑒𝑥 = 1 + 𝑥 +
Code at left
computes ex
for x = 1 with
relative error
of 1x10-8
3
𝑥2 𝑥
+ +⋯
2! 3!
24
4 Nested For Loops
Conditional Loop
• Focus on the loops and ignore x(k) =
statements that use arrays, which we
haven’t covered yet
k index in
reverse order
For k = n To 1 Step -1
(from high to
x(k) = a(n,n+1)
low)
For j = k+1 to n
x(k) = x(k) – a(k,j) * x(j)
What happens to the j loop, the
Next j
first time in the k loop when k = n?
Next k
= n = n + 1
nTimes = [n - (n+1)]/1 +1 = 0, so
25
loop is not executed
Looping Exercises
is a condition (can be true or false)
are statements executed in the
loop (which should change the condition)
Do
if _
Then Exit Do
Loop
Do While
Loop
Do Until
Loop
Do
Do
Loop While Loop Until
Note tests before or after loop
26
Looping Exercises II
• Open workbook for Looping Exercise from
What’s New section of web site
– Uses Taylor series to compute sin(x)
– Enter different x values and observe results
– View code for mySine and mySine2 functions
• mySine uses fixed number of terms
• mySine2 uses fixed allowable relative error
– Modify functions to allow user inputs for
• Total number of terms in mySine
• Relative error and maximum iterations in mySine2
• What you will have to do
– Modify function headers to introduce new
variables for number of terms in mySine
and allowed error and maximum allowed
iterations in mySine2
– Modify code to use values of these
variables in place of current constant
values for these three terms
– Change function calls in worksheet to allow
user to enter values for the new variables
27
Looping Exercises III
28
mySine Code
Present
Worksheet
Insert rows for variables for iterations in mySine
and allowed error and maximum allowed
iterations in mySine2 and edit formulas
29
mySine = x
For loop limit is number
term = x
of terms minus one
For k = 1 To 4
term = -term * x ^ 2 / _
((2 * k + 1) * (2 * k))
mySine = mySine + term
Next k
30
5 mySine2 Code
First Programming Assignment
mySine2 = x
term = x
Allowed
Maximum
k = 1
relative error
iterations
Do
oldTerm = term
term = -term * x ^ 2 / _
((2 * k + 1) * (2 * k))
mySine2 = mySine2 + term
Converged = Abs(term - oldTerm) <= _
0.0001 * Abs(mySine2)
k = k + 1
Loop Until Converged Or k > 100
• Compute x(t) and y(t) from t = 0 to t =
tmax for a trajectory for given V0 and q
𝑦 = 𝑉0 𝑡𝑠𝑖𝑛𝜃 −
𝑔𝑡 2
2
𝑥 = 𝑉0 𝑡𝑐𝑜𝑠𝜃
𝑡𝑚𝑎𝑥 =
2𝑉0 𝑠𝑖𝑛𝜃
𝑔
• Six different approaches using
combinations of cell references (B3),
range names, user-defined function,
array function and macro
– Work on first four parts – cell formulas,
user defined functions and range names
31
32
Tonight’s Tasks
𝑡𝑚𝑎𝑥 =
• Open Excel workbook from assignment
link on home page of course web site
– Contains worksheets for first two parts of
assignment and the VBA code from the
assignment document (do not have to copy)
2𝑉0 𝑠𝑖𝑛𝜃
𝑔
=2*B1*sin(radians((B2))/B3
=B5/B4
=???
=B10+$B$6
𝑦 = 𝑉0 𝑡𝑠𝑖𝑛𝜃 −
• Can also open document for first programming
assignment from same web location
𝑔𝑡 2
2
– On the Cells tab complete the cell entries as
described at the end of the last class
𝑥 = 𝑉0 𝑡𝑐𝑜𝑠𝜃
• See next slide
33
Part 2: User-Defined Function
• Here is code given in assignment
Can use in
any procedure
Option Explicit
Const g As Double = 9.80665
Const degreesToRadians As Double = _
3.14159265358979 / 180
Function maxTime(ByVal v0 As Double, _
ByVal theta As Double) as Double
maxTime = 2 * v0 * Sin(theta * _
degreesToRadians) / g
End Function
• To use function in spreadsheet
=maxTime(, )
B1 – in web worksheet – B2
35
=$B$1*cos((radians($B$2))*B10
34
Part 2: User-Defined Function II
• Use maxTime code as example to write
UDFs for x and y
Function maxTime(v0 As Double, _
theta As Double) as Double
maxTime = 2 * v0 * Sin(theta * _
degreesToRadians) / g
End Function
𝑦 = 𝑉0 𝑡𝑠𝑖𝑛𝜃 −
𝑔𝑡 2
2
𝑥 = 𝑉0 𝑡𝑐𝑜𝑠𝜃
𝑡𝑚𝑎𝑥 =
2𝑉0 𝑠𝑖𝑛𝜃
𝑔
• What arguments are required for x and y?
• What are statements to compute x and y
• How do you call functions from worksheet?
36
6 More on First Program
Call to maxTime
function here
UDF
Worksheet
Formula from Task 1
gives time step
Use same formula to
increment time and
copy to all cells
Call to x function Call to y function
here, copied to
here, copied to
all cells
all cells
• Array expressions: formulas that are entered into multiple cells at the same time
• Array functions: functions whose values
are entered into multiple cells as arrays
• When entering an array formula or
using an array function you must press
Control+Shift+Enter instead of Enter
• You are asked to modify VBA code to
use an array formula for the trajectory
37
Result of Array Formula Exercise
Instructions:
Enter values for initial velocity in
m/s in cell B1 and initial angle in
degrees in cell B2.
Select a range of 3 columns with
the desired number of rows to
display the trajectory plus the
header.
With the full range still selected
type one of the following formulas
in the formula bar:
=trajectory(B1,B2)
=trajectory(Initial_velocity, _
Initial_angle)
Press control+shift+enter to enter
39
the array formula.
38
Function trajectory(v0 As Double, theta As Double) As _
Variant
Dim userRows As Long
Dim userColumns As Long
'Statements below determine rows and columns
userRows = Application.Caller.Rows.Count
userColumns = Application.Caller.Columns.Count
If userColumns < 3 Then
‘Removed code with error message
Else
trajectory = calculateTrajectory(v0, theta, userRows)
End If
CalculateTrajectory returns an array which
End Function
is returned to the worksheet
It is also used as a function for the final
40
part of the assignment
Sub getTrajectory()
Use of Macro
‘Comments and declarations not shown here
• User enters data
then clicks button
to get trajectory
• Macro that button
executes is given
on exercise
v0 = Range("b1").Value
theta = Range("b2").Value
nRows = Range("b3").Value + 2
Cells(startRow, startCol) _
.CurrentRegion.ClearContents
Range(Cells(startRow, startCol),
Cells(startRow + nRows - 1,
startCol + 2)) _
.Value = calculateTrajectory _
(v0, theta, nRows)
– Uses function
calculateTrajectory
used in previous
part of assignment
41
End Sub
42
7 Note to LSC
Choice Function
• Write and test a VBA function that takes a
value from a worksheet and returns an
index (to the worksheet) based on the
value ranges shown in the table below:
• The remaining slides were present in
the file from Fall 2012, but were not
used in the Spring 2014 Presentation.
Only slides 1-42 were placed in the
online presentation.
43
Solution to Exercise
Values
Index
Values
Index
x<0
Err Low
1≤x<2
4
0 ≤ x < 0.1
1
2≤x<5
5
0.1 ≤ x < 0.5
2
5 ≤ x ≤ 10
6
0.5 ≤ x < 1
3
x > 10
Err High
44
Solution to Exercise II
Function label(x As Double) As Variant
If x < 0 Then
label = "Value too low"
ElseIf x < 0.1 Then
label = 1
ElseIf x < 0.5 Then
label = 2
ElseIf x < 1 Then
label = 3
ElseIf x < 2 Then
label = 4
ElseIf x < 5 Then
45
Select Case
ElseIf x < 5 Then
label = 5
ElseIf x <= 10 Then
label = 6
Else
label = "Value too high"
End If
End Function
• Will later see solution for Select Case
46
Select Case Example
• Lists possible values (or ranges of
values) called cases for a variable
Select Case
Case
Only first case found to
End Select
be true is executed
47
Select Case x
Case a
Case b to c
Case d, e, f to g
Case Is >= h
=h>
Case Else
End Select
48
8 Select Case Solution
Redo Problem with Select Case
• Get an index based on the value ranges
shown in the table below
• Problem is inclusive nature of Case a to b
• Solution is to write cases in reverse order
Values
Index
Values
Index
x<0
Err Low
1≤x<2
4
0 ≤ x < 0.1
1
2≤x<5
5
0.1 ≤ x < 0.5
2
5 ≤ x ≤ 10
6
0.5 ≤ x < 1
3
x > 10
Err High
Function label(x As Double) As Variant
Select Case x
Case Is > 10
label = "Value too high"
Case 5 To 10
label = 6
Case 2 To 5
label = 5
Case 1 To 2
label = 4
Case 0.5 To 1
label = 3
Select Case Solution
Excel Function Example
Case 0.1 To 0.5
label = 2
Case 0 To 0.1
label = 1
Case 0.5 To 1
label = 3
Case Else
label = "Value too low"
End Select
•
•
•
•
Compute loan payment using PMT
PMT(Rate, Periods, LoanAmount)
Sample of PMT
How does PMT
know B1 is rate?
• PMT uses the cell values in the function
call as rate, periods, amount to find PMT
• When using a function arguments to
function must be in correct order
End Function
52
From PMT to Your UDF
Writing Your UDF
• Whoever wrote the PMT function
decided on the order of the arguments
• Similarly when you write a UDF, you
decide on the order of the arguments
• Anyone who uses your function must
follow the order you set
• The function may be called from a
worksheet or other VBA procedure
• The header has the following form
Function ( ) As
– is the name of the function
– is the data type for the function
– may be blank or have one or
more entries of the form As
•
•
•
•
– But the order must be correct
53
is a variable used in the function
is the data type for that variable
Separate multiple entries in the list by commas
Arguments provide input data to function
54
9 Writing Your UDF II
Using Your UDF
• The function has the following form
Function ( ) As
=
End Function
Function vCyl ( R as Double, H as Double ) As _
Double
vCyl = 4 * atn(1) * R^2 * H
End Function
• Use by cell entries in worksheet
=vCyl( B1, B2)
=vCyl( 1, 20)
=vCyl( radiusName, heightName)
• Call from other VBA procedures
V = vCyl( radius, height)
cylVol = vCyl( 1, 20)
v10cyls = 10 * vCyl( rad, hgt)
55
Arrays
56
Two-dimensional Arrays
• Arrays can be visualized as data on an
experimental variable
– Could describe pressure data points
mathematically as P1, P2, etc.
– In VBA we can represent these data points
as P(1), P(2), etc.
– We call the numbers (1, 2, etc.) indices or
subscripts
• We can use constants or variables for the
subscripts: P(4), P(k), where k has a value
Consider an experiment where you vary the current
over six levels, the voltage over four levels and
measure the efficiency, e, of an electromechanical
device. The data for each combination of current
and voltage can be represented as shown below
I(1)
I(2)
I(3)
I(4)
I(5)
V(1)
e(1,1) e(1,2) e(1,3) e(1,4) e(1,5) e(1,6)
V(2)
e(2,1) e(2,2) e(2,3) e(2,4) e(2,5) e(2,6)
V(3)
e(3,1) e(3,2) e(3,3) e(3,4) e(3,5) e(3,6)
V(4)
e(4,1) e(4,2) e(4,3) e(4,4) e(4,5) e(4,6)
57
Declaring Arrays
I(6)
58
Dimensioning Arrays
• Arrays must be declared as arrays
• The maximum size of the array must be
specified in the Dim statement
• In VBA the lowest array subscript is
zero by default
– Can use Option Base 1 in declarations
section to change default lowest subscript
to one
• Can also set lowest subscript on each
individual array
59
• Can declare arrays as follows
Dim I(1 to 6) as double
Dim V(1 to 4) as double
Dim e(1 to 4, 1 to 6) as double
• Size below depends on Option Base
What is lowest subDim I(6) as double
script for these arrays?
Dim V(4) as double
Zero or one depending
Dim e(4, 6) as double on Option Base
60
10 Using Arrays
Two-Dimensional Arrays
• Arrays components are referenced by
their subscripts
• This is often done in a For loop
For k = 0 to 100
x(k) = sin(k * PI / 100) ‘PI=3.14…
Next k
• x is an array with 101 components giving
sin(x) for 0 x p, with Dx = p/100
61
Dynamic Arrays
• Use nested for loops
– Use example of current and voltages
For k = 1 to 4
For j = 1 to 6
Power(k,j) = I(j) * V(k)
Next j Recall table:
V was in rows
Next k
I was in columns
Power(k,j) is Power(row, column)
Are k and j indices correct?
62
Passing Arrays to Procedures
• What if you do not know array size until
program is actually running?
• Use Dim a() to tell compiler that a is an
array then use ReDim with actual
dimensions
Sub getArray( N as long) as Variant
Dim x() as Double : ReDim X(1 to N)
• Can go from Dim a() as Double to any
size ReDim: ReDim a(1 to 10, 6 to 12)
63
Determining Array Bounds
• Declare array in argument list with
parentheses to indicate array
Use this for any
Sub mine( A() as double)
size array.
‘No dim statement for A
Variant arrays
A(2,3) =
do not need ()
• Calling program sets actual dimensions
on array and uses only the following
Dim B(1 to 10, 1 to 6) as double
Call mine(B)
64
Worksheet Arrays to VBA
• The UBound and LBound functions
determine the upper and lower bounds
of unknown array dimensions
• For a two-dimensional array, A(m,k)
• Passed as a range of cells
• First step is to set a type variant variable equal to the input range variable
– The variant variable is now an twodimensional array
– May have single row or single column, but
is still a two-dimensional array
– Lower bound is always one
– Can use UBound to get sizes
– LBound(A,1) is the lower bound of m
– UBound(A,1) is the upper bound of m
– LBound(A,2) is the lower bound of k
– UBound(A,2) is the upper bound of k
65
66
11 Worksheet Array Example
Worksheet Array Example II
Function getMean (Ain As Range) _
As Double
Dim A as Variant, m as Long, k as Long
Dim sum as double, cells as Long
Dim nRows as Long, nCols as Long
A = Ain : nRows = UBound(A,1)
nCols = UBound(A, 2) : cells = nRows * nCols
For k = 1 to nRows
For m = 1 to nCols
Code from red line to
sum = sum + A(k,m)
end on next slide
Next m
Dim sum as double, cells as Long
Dim nRows as Long, nCols as Long
A = Ain : nRows = UBound(A,1)
nCols = UBound(A, 2) : cells = nRows * nCols
For k = 1 to nRows
For m = 1 to nCols
sum = sum + A(k,m)
Next m
Next k
getMean = sum / cells
End Function
67
68
Function array2wks() As Variant
Dim userRows As Long
Dim userColumns As Long
Dim workArray() as Double
'Statements below determine rows and columns
userRows = Application.Caller.Rows.Count
userColumns = Application.Caller.Columns.Count
ReDim workArray(1 to userRows, 1 to userColumns)
VBA Array to Worksheet
• VBA steps to return array to worksheet
– Declare the function type as Variant
– In the function or sub declare a working
array for calculations
• Use application.caller for dimensions
– Write the code for values in working array
– At end of function set =
‘Place code here to compute all
‘components of workArray
• To use the function: select cells; enter
function in formula bar; Cont+Shift+Enter
69
Passing by Reference/Value
array2wks = workArray
End Function
70
Passing by Reference/Value 2
• Consider the following function call
Call mySub( a, b)
Sub mySub( x as Long, y as Long)
x=2*x
Answer: The value of a
will have the new value
y=x/y
of x computed in myFunc
End Sub
• By default VBA passes memory
locations of variables to procedures
– This is known as pass by reference
• Alternative is pass by value
• What happens to the value of a in the
calling program because of the x = 2 * x?
71
– This simply sends the procedure the value
stored in the memory location
– To use pass by value enter the keyword
ByVal before the variable in the header
Sub mySub( Byval x as Long, y as Long)
72
12 Strings
Debugging
• Consider only variable length
• Use Dim str as String
• Use & or + as concatenation operator to
join two strings
• Len(str) gives length of string
• Left, Right, and Mid give substrings in
same manner as worksheet functions
• InStr function searches for substrings
73
• Debugger allows you to step through a
program and see intermediate values
– Useful to find location of errors
• Items to use in debugger
– Breakpoints stop execution at certain points
– Step-by-step execution
– Intermediate and Watch windows
– Hover mouse over variable to get its value
– Change statement to be executed next
74
Help
• Help systems for Excel and VBA
• Search function does not always return
what you are looking for
• If you know the keyword, type it, place
the cursor in the keyword, and press F1
• Sometimes a Google search for “VBA
” works
75
ME 309 – Numerical Analysis of Engineering Systems
13 Numerical Analysis of Engineering Systems
of 13
Recommended Documents
⏰ Deadline pressure?
Get your assignment done in just 3 hours. Quick, easy, and available 24/7.
Report
Tell us what’s wrong with it:
Thanks, got it!
We will moderate it soon!
Free up your schedule!
Our EduBirdie Experts Are Here for You 24/7! Just fill out a form and let us know how we can assist you.
Take 5 seconds to unlock
Enter your email below and get instant access to your document