Page 142 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 119

Option Explicit
Function dydx(expression, variable, Optional scale-factor) As Double
'Custom function to return the first derivative of a formula in a cell.
'expression is F(x), variable is x.
'scale-factor is used to handle case where x = 0.
'Workbook can be set to either R1 C1- or Al-style.
Dim OldX As Double, NewX As Double, OldY As Double, NewY As Double
Dim delta As Double
Dim NRepl As Integer, J As Integer
Dim Formulastring As String, XRef As String, dummy as String
Dim T As String, temp As String
I delta = 0.00000001
'Get formula and value of cell formula (y).
Formulastring = expression.Forrnula 'Returns A1 -style formula; default is
absolute.
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 <> 0 Then
NewX = OldX (1 + delta)
Else
If IsMissing(sca1e-factor) Or scale-factor = 0 Then -
dydx = CVErr(xlErrDiv0): Exit Function
NewX = scale-factor delta
End If
'Convert all references to absolute
'so that only text that is a reference will be replaced.
T = Application.ConvertFormula(FormulaString, xlAl , xlA1, xlAbsolute)
'Do substitution of all instances of x reference with value.
'Substitute reference, e.g., $A$2,
'with a number value, e.g., 0.2, followed by a space
'so that $A$25 becomes 0.2 5, which results in an error.
'Must replace from last to first.
NRepl = (Len0 - Len(Application.Substitute(T, XRef, "'I))) / Len(XRef)
For J = NRepl To 1 Step -1
temp = Application.Substitute(T, XRef, NewX & " 'I, J)
If IsError(Evaluate(temp)) Then GoTo ptl
T = temp
ptl: Next J
NewY = Evaluate0
dydx = (NewY - OldY) / (NewX - OldX)
End Function

Figure 6-19. Improved Function procedure to calculate first derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', module 'FirstDeriv')