Study Guide
Chapter 20 VBA Code to do This with the Computeris
-
University:
Kentucky State University -
Course:
COS 232| Programming I Academic year:
2024
-
Views:
316
Pages:
30
Author:
Lamghari6kx
A VBA code to do this with the computer is Sub Splines()
Dim i As Integer, n As Integer
Dim x(100) As Single, y(100) As Single, xu As Single, yu As Single
Dim xint(100) As Single
Dim dy As Single, d2y As Single
Sheets("Sheet1").Select
Range("a5").Select
n = ActiveCell.Row
Selection.End(xlDown).Select
n = ActiveCell.Row - n
Range("a5").Select
For i = 0 To n
x(i) = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
y(i) = ActiveCell.Value
ActiveCell.Offset(1, -1).Select
Next i
Range("d5").Select
nint = ActiveCell.Row
Selection.End(xlDown).Select
nint = ActiveCell.Row - nint
Range("d5").Select
For i = 0 To nint
xint(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Next i
Range("e5").Select
For i = 0 To nint
Call Spline(x(), y(), n, xint(i), yu, dy, d2y)
ActiveCell.Value = yu
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dy
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = d2y
ActiveCell.Offset(1, -2).Select
Next i
Range("a5").Select
End Sub
Sub Spline(x, y, n, xu, yu, dy, d2y)
Dim e(10) As Single, f(10) As Single, g(10) As Single, r(10) As Single, d2x(10) As Single
Call Tridiag(x, y, n, e, f, g, r)
Call Decomp(e(), f(), g(), n - 1)
Call Substit(e(), f(), g(), r(), n - 1, d2x())
Call Interpol(x, y, n, d2x(), xu, yu, dy, d2y)
End Sub
Sub Tridiag(x, y, n, e, f, g, r)
Dim i As Integer
f(1) = 2 * (x(2) - x(0))
g(1) = x(2) - x(1)
r(1) = 6 / (x(2) - x(1)) * (y(2) - y(1))
r(1) = r(1) + 6 / (x(1) - x(0)) * (y(0) - y(1))
For i = 2 To n - 2
e(i) = x(i) - x(i - 1)
f(i) = 2 * (x(i + 1) - x(i - 1))
g(i) = x(i + 1) - x(i)
r(i) = 6 / (x(i + 1) - x(i)) * (y(i + 1) - y(i))
r(i) = r(i) + 6 / (x(i) - x(i - 1)) * (y(i - 1) - y(i))
Next i
e(n - 1) = x(n - 1) - x(n - 2)
f(n - 1) = 2 * (x(n) - x(n - 2))
r(n - 1) = 6 / (x(n) - x(n - 1)) * (y(n) - y(n - 1))
r(n - 1) = r(n - 1) + 6 / (x(n - 1) - x(n - 2)) * (y(n - 2) - y(n - 1))
End Sub
Sub Interpol(x, y, n, d2x, xu, yu,
Dim i As Integer, flag As Integer
Dim c1 As Single, c2 As Single, c3
Dim t1 As Single, t2 As Single, t3
flag = 0
i = 1
Do
If xu >= x(i - 1) And xu <= x(i)
dy, d2y)
As Single, c4 As Single
As Single, t4 As Single
Then c1 = d2x(i - 1) / 6 / (x(i) - x(i - 1))
c2 = d2x(i) / 6 / (x(i) - x(i - 1))
c3 = y(i - 1) / (x(i) - x(i - 1)) - d2x(i - 1) * (x(i) - x(i - 1)) / 6
c4 = y(i) / (x(i) - x(i - 1)) - d2x(i) * (x(i) - x(i - 1)) / 6
t1 = c1 * (x(i) - xu) ^ 3
t2 = c2 * (xu - x(i - 1)) ^ 3
t3 = c3 * (x(i) - xu)
t4 = c4 * (xu - x(i - 1))
yu = t1 + t2 + t3 + t4
t1 = -3 * c1 * (x(i) - xu) ^ 2
t2 = 3 * c2 * (xu - x(i - 1)) ^ 2
t3 = -c3
t4 = c4
dy = t1 + t2 + t3 + t4
t1 = 6 * c1 * (x(i) - xu)
t2 = 6 * c2 * (xu - x(i - 1))
d2y = t1 + t2
flag = 1
Else
i = i + 1
End If
If i = n + 1 Or flag = 1 Then Exit Do
Loop
If flag = 0 Then
MsgBox "outside range"
End
End If
End Sub
Sub Decomp(e, f, g, n)
Dim k As Integer
For k = 2 To n
e(k) = e(k) / f(k - 1)
f(k) = f(k) - e(k) * g(k - 1)
Next k
End Sub
Sub Substit(e, f, g, r, n, x)
Dim k As Integer
For k = 2 To n
r(k) = r(k) - e(k) * r(k - 1)
Next k
x(n) = r(n) / f(n)
For k = n - 1 To 1 Step -1
x(k) = (r(k) - g(k) * x(k + 1)) / f(k)
Next k
End Sub 20.11 The best fit equation can be determined by nonlinear regression as
[B ] =
98.84[ F ]
0.8766 + [ F ]
120
100
80
60
40
20
0
0
10
20
30
40
50
60
Disregarding the point (0, 0), The r2 can be computed as
St = 9902.274
Sr = 23.36405
r2 =
9902 .274 − 23 .364
= 0.9976
9902.274
20.12 The Excel Solver can be used to develop a nonlinear regression to fit the parameters. The
result (along with a plot of –dA/dt calculated with the model versus the data estimates) are
shown below. Note that the 1:1 line is also displayed on the plot. 20.13 The Excel Solver can be used to develop a nonlinear regression to fit the parameters. The
result (along with a plot of the model versus the data estimates) are shown below. Note that
the 1:1 line is also displayed on the plot.
20.14 The standard errors can be computed via Eq. 17.9
sy/ x =
Sr
n−2
Thus, Model C seems best because its standard error is lower.
20.15 A plot of the natural log of cells versus time indicates two straight lines with a sharp break
at 2. Trendline can be used to fit each range separately with the exponential model as
shown in the second plot.
2
1
0
-1 0
2
4
6
-2
-3
8
7
6
5
4
3
2
1
0
y = 0.4953e 0.4000x
R2 = 1.0000
y = 0.1000e 1.2000x
R2 = 1.0000
0
1
2
3
4
5
6
7
20.16 (This problem was designed by Theresa Good of Texas A&M.) The problem can be solved
with Microsoft Excel: 20.17
Plot the data using Excel:
Surface Area vs Weight
Surface Area, squared meters
2.35
2.3
2.25
2.2
2.15
y = 0.4149x0.3799
R2 = 0.9711
2.1
2.05
60
70
80
90
100
Weight, kg
+
A = 0.4149W0.3799 with R-squared = 0.9711. Therefore, a = 0.4149 and b = 0.3799. The
predicted surface area for a 95 kg human is approximately: A = 0.4149 (95) 0.3799 = 2.34 m2 20.18 The Excel Trend Line tool can be used to fit a power law to the data:
6000
5000
4000
3000
2000
y = 65.768x0.7497
R2 = 0.9935
1000
0
0
100
200
300
The logarithmic slope relating the mass and metabolism is 0.75.
20.19 20.20 20.21 The problem is set up as the following Excel Solver application. Notice that we have
assumed that the model consists of a constant plus two bell-shaped curves:
f (x) = c +
k1e
− k12 ( x − a1 )
π
+
The resulting solution is
k2 e
− k 22 ( x − a 2 )
π Thus, the retina thickness is estimated as 0.31 – 0.25 = 0.06. 20.29 Clearly the linear model is not adequate. The second model can be fit with the Excel
Solver: Notice that we have reexpressed the initial rates by multiplying them by 1×105. We did this
so that the sum of the squares of the residuals would not be miniscule. Sometimes this will
lead the Solver to conclude that it is at the minimum, even though the fit is poor. The
solution is:
Although the fit might appear to be OK, it is biased in that it underestimates the low values
and overestimates the high ones. The poorness of the fit is really obvious if we display the
results as a log-log plot:
Notice that this view illustrates that the model actually overpredicts the very lowest values.
The third and fourth models provide a means to rectify this problem. Because they raise [S]
to powers, they have more degrees of freedom to follow the underlying pattern of the data.
For example, the third model gives: Finally, the cubic model results in a perfect fit:
Thus, the best fit is
v0 =
2.4311 × 10 −5 [S ]
0. 4 + [ S ] 20.49 This problem was solved using an Excel spreadsheet.
0.01
1
10
100
2.6363
creep rate
y = 4E-06x
2
R = 0.9997
Series1
Power (Series1)
0.001
0.0001
stress
20.50 This problem was solved using an Excel spreadsheet.
7
y = 0.685x + 2.779
R2 = 0.9771
6
5
4
3
2
1
0
0
1
2
3
s he a r s t r a i n r a t e
µ = 0.685
τy = 2.779 N/m2
4
5
6 r2 = 0.9771
20.51 This problem was solved using an Excel spreadsheet.
12
y = 0.7276x0.543
R2 = 0.9891
10
8
6
4
2
0
0
20
40
60
80
s he ar s t rai n rat e
µ = 0.7276
n = 0.543
n
Sr
Number of model
parameters fit
sy/x
15
Model A
135
Model B Model C
90
72
2
3
4
3.222517 2.738613 2.558409
100
120
140
Chapter 20 VBA Code to do This with the Computeris
of 30
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