Page 157 - Excel for Scientists and Engineers: Numerical Methods

134 EXCEL: NUMERICAL METHODS

differentiation functions of the previous chapter. The Formula property of the
cell is used to get the worksheet formula to be differentiated into the VBA code
as text. Then the SUBSTITUTE worksheet function is used to replace the
variable of interest by an incremented value, and the Evaluate method used to get
the new value of the formula. These values are used to calculate the area of each
panel, and the areas of the panels are summed to obtain the area under the curve.
This function procedure can be used to integrate an expression F(x) defined
by a worksheet formula, between specified lower and upper limits a and b
respectively. A table of function values is not required.
b
A= [F(x)dx (7-8)
a
The syntax of the function is Integrate(expression, variable, from-lower,
to-upper). The argument expression is the integrand, the expression to be
integrated. The argument variable is the variable of integration, and the
arguments from-lower and to-upper are the lower and upper limits of integration,
respectively. The VBA code is shown in Figure 7-8. Function procedures for
both trapezoidal (IntegrateT) and Simpson's rule (Integrates) methods are shown.
The range of x values over which the integration is to be performed
(to-upper - from-lower) is divided into N panels. The user can adjust the
accuracy of the integration by changing the value of N in the procedure, with a
concomitant increase in calculation time.

Option Explicit
Function IntegrateT(expression, variable, from-lower, to-upper)
'Simple trapezoidal area integration
Dim Formulastring As String, T As String, Xref As String
Dim H As Double, area As Double, X As Double
Dim N As Integer, K As Integer, J As Integer
Dim NRepl As Integer
Dim temp As String
Dim F1 As Double, F2 As Double
FormulaString = expression.Formula
T = Application.ConvertFormula(FormulaString, xlAl , xlAl , xlAbsolute)
XRef = variable.Address
N = 1000
H = (to-upper - from-lower) / N
area = 0
X = from-lower
NRepl = (Len(T) - Len(Application.Substitute(T, XRef, "'I))) / Len(XRef)
For K = 1 To N