Page 145 - Excel for Scientists and Engineers: Numerical Methods

122 EXCEL: NUMERICAL METHODS

T = Formulastring
'Now do substitution of all instances of x reference with decremented x value
For J = NRepl To 1 Step -1
temp = Application.Substitute(T, XRef, NewX2 & " 'I, J)
If IsError(EvaIuate(temp)) Then GoTo pt2
T = temp
pt2: Next J
NewY2 = Evaluate0
d2ydx2 = (NewY1 + NewY2 - 2 * OldY) / Abs((NewX1 - OldX) * (NewX2 - OldX))
EndFunction
Figure 6-21. Function procedure to calculate second derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', module 'SecondDeriv')
Figure 6-22 illustrates the use of the dydx and d2ydx2 custom functions. The
formula in cell 84 is
=aa*A4"3+ bb*A4"2+cc*A4+dd
(aa, bb, cc, dd are named ranges. The formula in cell C4 is
=dydx(B4,A4,1)

Figure 6-22. Using Function procedures to calculate
first and second derivatives of a function.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet 'First and Second Derivs')
Note the use of the optional argument scale-factor that prevents an error in
cells C9 and F9 when the value of the independent variable in cell A9 is zero.