Page 140 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 117

(8) Calculate NewY, the new value of the function, by applying the Evaluate
method to the new formula string.
(9) Calculate and return the first derivative.

Function FirstDerivDemo(expression, variable) As Double
'Custom function to return the first derivative of a formula in a cell.
Dim OldX As Double, OldY As Double, NewX As Double, NewY As Double
Dim Formulastring As String, XAddress As String

Formulastring = expression.Formula
OldY = expression.Value
XAddress = variable.Address 'Default is absolute reference
OldX = variable.Value
NewX = OldX * 1.00000001
Formulastring = Application.ConvertFormula(FormulaString, xlAl , xlAl , -
xlAbsol Ute) 'Convert all references in formula to absolute
Formulastring = Application.Substitute(FormulaString, XAddress, NewX)
NewY = Evaluate(Formu1aString)
FirstDerivDemo = (NewY - OldY) / (NewX - OldX)
End Function
Figure 6-17. Function procedure to demonstrate calculation of first derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', module 'Demo')

Examples of the first derivative of some worksheet formulas calculated by
the custom function are shown in Figure 6-18. The formula in cell D3 is
= FirstDerivDemo (C3,B3)

The formulas labeled "exact" in column E are the appropriate formulas from
differential calculus for the first derivative of the respective functions. For
example, the formula in cell E3 is
=9*B3"2+ 10*B3-5

Figure 6-18. Using a simple Function procedure to calculate some first derivatives.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet 'Demo Function')