Page 232 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 9 SYSTEMS OF SIMULTANEOUS EOUATIONS 209

Option Explicit
Option Base 1
..............................................................
Function SimultEqNL(equations, variables, constants)
'Newton iteration method to find roots of nonlinear simultaneous equations
Dim I As Integer, J As Integer, K As Integer, N As Integer
Dim Nlterations As Integer
Dim R As Integer, C As Integer
Dim VarAddr() As String, FormulaString() As String
Dim con() As Double, A() As Double, B() As Double
Dim V() As Double
Dim Y1 As Double, Y2 As Double
Dim tolerance As Double, incr As Double

N = equations.Rows.Count
K = variables.Rows.Count
If K = 1 Then K = variables.Columns.Count
If K e. N Then SimultEqNL = CVErr(x1ErrRef): Exit Function
ReDim VarAddr(N), FormulaString(N), V(N), con(N)
ReDim A(N, N + I), B(N, N + 1)

tolerance = 0.000000000001 'Convergence criterion.
incr = 0.0000000001 'Increment for numerical differentiation.
Nlterations = 50

For I = 1 To N
VarAddr( I) = variables( 1) .Address
Next

'Initial values
For I = 1 To N
con(l) = constants(l).Value
V(I) = variables(l).Value: If V(1) = 0 Then V(1) = 1
Next

For J = 1 To Nlterations
'Create N x N matrix of partial derivatives.
For R = 1 To N
ForC=IToN
' Formulastring is formula in which all but one variable in each equation
' is replaced by current values.
FormulaString(R) = Application.ConvertFormula(equations(R).Formula, -
xlAl , xlAl, xlAbsolute)
For I = 1 To N
If I c> C Then FormulaString(R) = Application.Substitute( -
Formula St ring (R) , VarAdd r( I), V( I))
Next I
'Calculate partial derivative (central differences).
Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), -
V(C) * (1 + incr)))
Y 1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), -