Page 144 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 121

Option Explicit
Function d2ydx2(expression, variable, Optional scale-factor) As Double
'Custom function to return the second derivative of a formula in a cell.
'expression is F(x), variable is x.
'Uses central difference formula.
'scale-factor is used to handle case where x = 0.
'Workbook can be set to either RICI- or Al-style.
Dim OldX As Double, OldY As Double
Dim NewXl As Double, NewX2 As Double
Dim NewYl As Double, NewY2 As Double
Dim XRef As String
Dim delta As Double
Dim Formulastring As String, T As String
Dim temp As String
Dim NRepl As Integer, J As Integer
delta = 0.0001
'Get formula and value of cell formula (y).
Formulastring = expression.Formula 'Returns Al-style formula
OldY = expression.Value
'Get reference and value of argument (x).
OldX = variable.Value
XRef = variable.Address 'Default is A1 -style absolute reference
'Handle the case where x = 0.
'Use optional scale-factor to provide magnitude of x.
'If not provided, returns #DIVO!
If OldX e> 0 Then
NewXl = OldX * (1 + delta)
NewX2 = OldX * (1 - delta)
Else
If IsMissing(sca1e-factor) Or scale-factor = 0 Then -
d2ydx2 = CVErr(xlErrDiv0): Exit Function
NewXl = scale-factor delta
NewX2 = -scale-factor delta
End If
'Convert all references to absolute
'so that only text that is a reference will be replaced.
Formulastring = Application.ConvertFormula(FormulaString, xlAl, xlAl , -
xlAbsolute)
T = Formulastring
NRepl = (Len(T) - Len(Application.Substitute(T, XRef, "'I))) I Len(XRef)
'Do substitution of all instances of x reference with incremented x value
For J = NRepl To 1 Step -1
temp = Application.Substitute(T, XRef, NewXl & " ", J)
If IsError(Evaluate(temp)) Then GoTo ptl
T = temp
ptl: Next J
'Evaluate the expression.
NewYl = Evaluate(T)