O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

10.2. Implementing the Trapezoidal Rule in VBA

Problem

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.

Solution

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.

Discussion

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required