Page 138 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 115

Figure 6-16. A chart of a function and its first derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')
The advantage of using a Sub procedure is that the derivative can be
obtained easily, even for the most complicated worksheet formulas. All of the
difficult calculations are done when the spreadsheet updates after the new value
of x is entered in, for example, cell A9. The disadvantage of a Sub procedure is
that if changes are made to precedent cells in the worksheet, the Sub procedure
must be run in order to update the calculations.

First Derivative of a Worksheet Formula
Calculated by Using a VBA Function Procedure
Using the Finite-Difference Method
Unlike the Sub procedure described in the preceding section, a Function
procedure automatically recalculates each time changes are made to precedent
cells. A Function procedure to calculate the first derivative of a formula in a cell
would be very useful. However, a function procedure can't use the approach of
the preceding section (i.e., changing the value of the cell containing the x value),
since a function procedure can't change the contents of other cells. A different
approach will have to be found.
The following VBA code illustrates a simple Function procedure to
calculate the first derivative dy/& of a formula in cell, using the same approach
that was used in the preceding section: the procedure calculates OldX, OldY,