Page 249 - Excel for Scientists and Engineers: Numerical Methods

226 EXCEL NUMERICAL METHODS

argument deriv-formula is a reference to a cell containing the derivative in the
form of worksheet formula.
A more advanced version that handles multiple differential equations will be
presented later.

Option Explicit
Function Rungel (x-variable, y-variable, deriv-formula, interval)
'Runge-Kutta method to solve ordinary differential equations.
'Solves problems involving a single first-order differential equation.
'Derivative expression passed as an argument.
Dim FormulaText As String
Dim XAddress As String, YAddress As String
Dim X As Double, Y As Double
Dim H As Double, result As Double
'GET THE FORMULA AND REFERENCE ARGUMENTS
FormulaText = deriv-formula.Formula
'Make all references absolute
FormulaText = Application.ConvertFormula(FormulaText, xlAl , xlAl , -
xl Absolute)
XAddress = x-variable.Address 'absolute is default
X = x-variable.Value
YAddress = y-variable.Address 'absolute is default
Y = y-variable.Value
Rungel = RKI (XAddress, YAddress, X, Y, interval, FormulaText)
End Function
......................................................
Private Function RKI (XAddress, YAddress, X, Y, H, FormulaText)
' Calculate the RK terms
Dim TI As Double, T2 As Double, T3 As Double, T4 As Double
Dim result As Double
Call eval(XAddress, YAddress, X, Y, FormulaText, result)
TI = result H
Call eval(XAddress, YAddress, X + H I 2, Y + TI / 2, FormulaText, result)
T2 = result * H
Call eval(XAddress, YAddress, X + H 12, Y + T2 12, FormulaText, result)
T3 = result * H
Call eval(XAddress, YAddress, X + H, Y + T3, FormulaText, result)
T4 = result * H
RKI = Y + (TI + 2 *T2 + 2 * T3 + T4) 16
End Function
......................................................
Sub eval(XRef, YRef, XValue, Walue, ForrnulaText, result)
'Evaluates the derivative formula. Replaces each instance of, e.g., $A$2 in
formula with number value, e.g., 0.20, then evaluates.
'Must do this replacement from end of formula to beginning.
'Modified 03/08/06 to handle possible un-intended replacement of e.g., $A$2 in
$A$22.
'Method: replace $A$2 with value & " "
'so that $A$22 becomes "0.20 2" and this formula evaluates to an error.