Page 139 - Excel for Scientists and Engineers: Numerical Methods

116 EXCEL: NUMERICAL METHODS

NewX and NewY in order to calculate AxlAy. But in this function procedure, both
the worksheet formula and the independent variable are passed to the function as
arguments. The procedure is shown simply to illustrate the method; a number of
modifications, to be described later, will be necessary in order to produce a
"bulletproof" procedure.
The basic principle used in this Function procedure is the following:
(i) The two arguments of the function are references to the independent
variable x and the cell containing the formula to be differentiated, F(x).
(ii) Use the Value property to obtain the values of the arguments; these are
OldX and OldY.
(iii) Use the Formula property of the cell to get the worksheet formula to be
differentiated as the text variable FormulaText.
(iv) Use the SUBSTITUTE worksheet function to replace references to the x
variable in FormulaText by the incremented x value, NewX.
(v) Use the Evaluate method to get the new value of the formula. This is
NewY.

Since other procedures in this chapter and in subsequent chapters will use the
same method for modifying and evaluating a formula, it will be worthwhile to
examine the VBA code shown in Figure 6-17. The syntax of the function is
FirstDerivDemo(expression,variab/e). The nine lines of code in this procedure
perform the following actions:
Get Formulastring, the worksheet formula (as text) by using the Formula
property of expression.
Get OldY, the value of the worksheet formula, by using the Value property
of expression.
Get XRef, the reference to the independent variable x, by using the Address
property of variable. The address will be an Al-style absolute reference
Get OldX, the value of the independent variable x, by using the Value
property of variable.
Calculate NewX, the incremented value of the independent variable, by
multiplying OldX by 1.000000001.
Convert all references in Formulastring to absolute by using the
ConvertFormula method.
Replace all instances of XRef in Formulastring by the value of the new
variable NewX. This is done by using the SUBSTITUTE worksheet
function. For example, the formula string
=3*$B$3"3+5*$B$3"2-5*$B$3+11
when cell $6$3 contains the value 2, is converted to
=3*2.00000002"3+5*2.00000002"2-5*~+11.