Page 136 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 113

Option Explicit
Option Base 1
...........................................
Sub Derivs()
Dim z As Integer, N As Integer
Dim Old-Ys() As Double, New-Ys() As Double, Old-Xs() As Double,
Dim Derivs() As Double, increment As Double
Dim known-& As Object, known-Ys As Object, cel As Object

increment = 0.00000001

'Use the Set keyword to create an object variable
Set known-Ys = Application.lnputBox -
("Select the range of Y values", "STEP 1 OF 3, , , , , , 8)
N = known-Ys.Count
ReDim Old-Ys(N), New-Ys(N), Old-Xs(N), Derivs(N)
z=l
For Each cel In known-Ys
Old-Ys(z) = cel.Value
z=z+1
Next cel
Set known-Xs = ApplicationhputBox -
,
("Select the range of X values", "STEP 2 OF 3, , , , 8)
,
z=l
For Each cel In known-Xs
Old-Xs(z) = cel.Value
cel.Value = Old-Xs(z) * (1 + increment)
z=z+1
Next cel
z=l
For Each cel In knownYs
New-Ys(z) = cel.Value
z=z+I
Next cel
z=l
For Each cel In known-Xs
cel.Value = Old-Xs(z)
z=z+l
Next cel

Application.lnputBox("Select the destination for derivatives", -
"STEP 3 OF 3, , , , , , 8)Select
For z = 1 To N
Derivs(z) = (New-Ys(z) - Old-Ys(z)) / (increment * Old-Xs(z))
ActiveCell.Offset(z - 1, O).Value = Derivs(z)
Next

End Sub
Figure 6-14. Sub procedure to calculate first derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', module 'Derivs')