Page 141 - Excel for Scientists and Engineers: Numerical Methods

118 EXCEL: NUMERICAL METHODS

Improving the VBA Function Procedure

The simple procedure shown in Figure 6-17 requires some modification.
First, the simple procedure replaces all instances of XRef, the reference to the
independent variable x, in Formulastring with a number value. For example, a
cell reference such as A2 will be replaced with a number value such as 0.05. But
there are cases where the substring A2 should not be replaced. Our procedure
needs to handle the following possibilities, all of which contain the substring A2
within Formulastring:
(i) the reference XRef and references in Formulastring may be relative,
absolute or mixed,
(ii) FormulaString contains a name such as BETA2,
(iii) Formulastring contains a reference such as AA2, or
(iv) FormulaString contains a reference such as A25.
By using the Address property to obtain an absolute reference (e.g., $A$2)
and using the ConvertFormula method to convert all references in
FormulaString to absolute, we have already eliminated problems arising from
cases (i), (ii), and (iii). Only case (iv) poses a problem: the substring $A$2 in
$A$25 will be substituted by 0.05, yielding 0.055. And so, as is often the case
with computer programming, a project that initially appeared to be simple
requires some additional programming.
We could write a formula parser that would break Formulastring into its
component parts and inspect each one. Not impossible, but that would require
extensive programming. A much simpler solution turns out to be the following:
by means of a loop, we replace each instance of, for example, A2 individually,
and, instead of replacing the reference with a number (e.g., 0.05), we replace the
reference with the number concatenated with the space character (e.g., 0.05 0).
We then evaluate the resulting string after each substitution. The reference
$A$25 yields the string 0.05 5. When evaluated, this gives rise to an error, and
an On Error GoTo statement is used so that the faulty substitution is not
incorporated into the FormulaString to be evaluated. Inspection of the code in the
latter half of the procedure in Figure 6-21 should make the process clear.

A second problem with the simple procedure of Figure 6-17 is that when x =
0, NewX = OldX, NewY = OldY and the procedure returns a #VALUE! error. The
error produced by a zero value for the independent variable x is handled by
adding an additional optional argument scale-factor. The syntax of the function
is dydx(expression, reference, Optional scale-factoq. If x is zero, a value for
scale-factor must be entered by the user. Scale-factor is used to calculate the Ax
for numerical differentiation. Scale-factor should be the same order of
magnitude as typical x values used in the formula.
The Function procedure is shown in Figure 6-19.