Page 186 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 8 ROOTS OF EQUATIONS 163

A Newton-Raphson Custom Function

The Newton-Raphson method can also be used in the form of a custom
function. The VBA code is shown in Figure 8-24.

Option Explicit
Function NewtRaph(expression, variable, Optional initial-value)
'Finds a root of a function by Newton-Raphson method.
'Expression must be a reference to a cell containing a formula.
'Variable must be a cell reference (cannot be a name).
'Initial-value can be a number, reference or omitted.
'Reference style can be either Al-style or R1 C1-style.
Dim Formulastring As String, XRef As String
Dim delta-x As Double, tolerance As Double
Dim XI As Double, X2 As Double, X3 As Double
Dim Y1 As Double, Y2 As Double
Dim m As Double
Dim I As Integer, J As Integer, NRepl As Integer
Dim temp As String, T As String, dummy As String

'Get F(x) and x.
Formulastring = expression.Formula
If Left(FormulaString, 1) <Z "="
Then NewtRaph = CVErr(xkrNA): Exit Function
XRef = variable.Address
'Convert all references to absolute
'so that only text that is a reference will be replaced.
FormulaString = Application.ConvertFormula(FormulaString, xlAl, xlAl , -
xl Absolute)

'Handle initial values that cause problems
If IsMissing(initia1-value) Then initial-value = variable
If initial-value = "" Then initial-value = variable

'Set delta-x for numerical differentiation, stopping tolerance
delta-x = 0.00000001
tolerance = 0.0000000001

'Perform the Newton-Raphson procedure
XI = initial-value
For I = 1 To 100 '100 iterations maximum
T = Formulastring 'Start with original formula each time thru loop
'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.
NRepl = (Len(T) - Len(Application.Substitute(T, XRef, ""))) I Len(XRef)
For J = NRepl To 1 Step -1
temp = Application.Substitute(T, XRef, XI & " ", J)