You see how easy it is to apply the trapezoidal rule in a spreadsheet, but you'd like to implement the rule in VBA to save having to set up the columns of data as was done in the previous recipe. You'd also like to be able to quickly change the spacing between *x*-values to improve accuracy.

Add two custom VBA functions as discussed here. See Recipe 2.2 to learn the basics of writing VBA functions if you don't know them already.

Let's reconsider the example from Recipe 10.1. This time, instead of setting up the table of data shown in Figure 10-1 and using the `SUMPRODUCT`

function in cell C20, you need only write two simple VBA functions.

The first function we need is simply a function to compute a *y*-value for a given *x*-value using the known analytic function under consideration. Example 10-2 shows just such a function for computing the analytic function from the previous example.

Example 10-2. VBA function for computing y = f(x)

Public Function F(x As Double) As Double F = Exp(-(x ^ 2)) End Function

As you can see, it's a fairly simple function. It takes a given *x*-value as an argument and returns the corresponding *y*-value using the given analytic function. The other required VBA function is shown in Example 10-3.

Example 10-3. VBA function for trapezoidal rule

Public Function Trapezoidal(xMin As Double, xMax As Double, n As Integer) As Double Dim dx As Double Dim sum As Double Dim y As Double dx = (xMax - xMin) / n sum ...

Start Free Trial

No credit card required