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 = (F(xMin) + F(xMax)) / 2# For i = 1 To (n - 1) y = F(xMin + (dx * i)) sum = sum + y Next i sum = sum * dx Trapezoidal = sum End Function |

This function takes three arguments: the lower and upper bounds of the interval of integration, `xMin` and `xMax`, and the number of intervals over which to divide the range of x-values, `n`.

The first task upon entering this function is to declare a few local variables. `dx` will store the computed spacing of the x-values given the number of intervals, `n`. The computed y-value is stored in the variable `y`. `sum` is used to accumulate the products of the coefficients and the `y` values.

After declaring these local variables, the function goes on to compute `dx`, which is just the x-range divided by the number of intervals. `sum` is also initialized to the average of the results of the function evaluated at the upper and lower bounds of integration. This step takes care of the first and last terms in the trapezoidal rule formula involving the 1/2 coefficient. Next, the function enters a `For` loop on the index variable `i` from `1` to `n-1` to accumulate the function evaluated at each computed x-point. This step takes care of all the middle sums in the trapezoidal rule formula.

The first calculation within the `For` loop evaluates `y` for a given `x` value by calling the function `F` as defined in Example 10-2. Here, `x` is computed by multiplying `dx` and `i`. Upon exiting the loop, `sum` is multiplied by `dx` to yield the final result.

To actually use these functions in a spreadsheet you need only type the formula `=trapezoidal(0,1,10)` into any given cell. Here, I used the same range and number of samples as used in the example from Recipe 10.1. As expected, the result in this case is exactly the same as before, that is, 0.746.

Using the VBA approach discussed here allows you to easily change the number of samples used in the computation to improve the accuracy of the result. Increasing the argument `n` results in more samples at a smaller x-spacing, resulting in greater accuracy. You can also change the upper and lower bounds of integration very easily using this approach. You can achieve both of these changes by taking the spreadsheet approach, but it requires you to reconstruct your columns of data.

You can integrate other analytic functions using the VBA functions shown here. All you need to do is change the `F` function to use your new analytic expression; the function `trapezoidal` remains unaffected.

I mentioned in Recipe 10.1 that some practitioners apply an iterative approach using the trapezoidal rule to adjust the number of sample intervals until the end result stops changing by an amount greater than some given tolerance. You can easily implement such a scheme using the VBA functions shown in this recipe. As an illustration, I added another custom VBA function as shown in Example 10-4.

Example 10-4. IterateTrapezoidal function

Public Function IterateTrapezoidal(xMin As Double, xMax As Double, n As Integer, tol As Double) As Double Dim value As Double Dim previousValue As Double Dim difference As Double Dim counter As Integer Dim nNew As Integer previousValue = Trapezoidal(xMin, xMax, n) difference = 99999 counter = 0 nNew = n While ((counter < 100) And (difference > tol)) nNew = nNew * 2 previousValue = value value = Trapezoidal(xMin, xMax, nNew) difference = Abs(value - previousValue) counter = counter + 1 Wend IterateTrapezoidal = value End Function |

This function, called `IterateTrapezoidal`, takes four arguments. The first three are the same as discussed earlier for the function `trapezoidal`. The fourth argument is new and represents the tolerance, `tol`, which tells the iterative algorithm when to stop.

The scheme goes like this: first, we compute a result using `TRapezoidal` just as we did earlier, using the supplied default number of sample intervals, `n`. Then we double `n`, recompute the result, and compare it with the previous result to see how much the result changed given the change in `n`. If the change is greater than `tol`, then we double `n` again and recompute. This repeats until the difference is less than `tol`, at which time the loop is exited and the last computed result is returned. This algorithm works pretty well and is one way to automate the process of choosing an appropriate `n` when applying the trapezoidal rule.

You do have to be careful with this approach. You'll notice that I put a counter in the `While` loop shown in Example 10-4. You could encounter a situation where the specified tolerance is too low and the solution never converges. The counter will eventually kick execution out of the loop, to prevent the function from getting stuck in an infinite loop. You do also have to keep in mind round-off error and precision. Basically, don't specify an unrealistically small tolerance, and keep in mind the magnitude of the result you're expecting. If you're expecting a result on the order of 10,000 ± 100, a tolerance of 1e-5 is probably way too small!

See Also

Refer back to Recipe 10.1 for more discussions on the trapezoidal rule.

Using Excel

- Introduction
- Navigating the Interface
- Entering Data
- Setting Cell Data Types
- Selecting More Than a Single Cell
- Entering Formulas
- Exploring the R1C1 Cell Reference Style
- Referring to More Than a Single Cell
- Understanding Operator Precedence
- Using Exponents in Formulas
- Exploring Functions
- Formatting Your Spreadsheets
- Defining Custom Format Styles
- Leveraging Copy, Cut, Paste, and Paste Special
- Using Cell Names (Like Programming Variables)
- Validating Data
- Taking Advantage of Macros
- Adding Comments and Equation Notes
- Getting Help

Getting Acquainted with Visual Basic for Applications

- Introduction
- Navigating the VBA Editor
- Writing Functions and Subroutines
- Working with Data Types
- Defining Variables
- Defining Constants
- Using Arrays
- Commenting Code
- Spanning Long Statements over Multiple Lines
- Using Conditional Statements
- Using Loops
- Debugging VBA Code
- Exploring VBAs Built-in Functions
- Exploring Excel Objects
- Creating Your Own Objects in VBA
- VBA Help

Collecting and Cleaning Up Data

- Introduction
- Importing Data from Text Files
- Importing Data from Delimited Text Files
- Importing Data Using Drag-and-Drop
- Importing Data from Access Databases
- Importing Data from Web Pages
- Parsing Data
- Removing Weird Characters from Imported Text
- Converting Units
- Sorting Data
- Filtering Data
- Looking Up Values in Tables
- Retrieving Data from XML Files

Charting

- Introduction
- Creating Simple Charts
- Exploring Chart Styles
- Formatting Charts
- Customizing Chart Axes
- Setting Log or Semilog Scales
- Using Multiple Axes
- Changing the Type of an Existing Chart
- Combining Chart Types
- Building 3D Surface Plots
- Preparing Contour Plots
- Annotating Charts
- Saving Custom Chart Types
- Copying Charts to Word
- Recipe 4-14. Displaying Error Bars

Statistical Analysis

- Introduction
- Computing Summary Statistics
- Plotting Frequency Distributions
- Calculating Confidence Intervals
- Correlating Data
- Ranking and Percentiles
- Performing Statistical Tests
- Conducting ANOVA
- Generating Random Numbers
- Sampling Data

Time Series Analysis

- Introduction
- Plotting Time Series Data
- Adding Trendlines
- Computing Moving Averages
- Smoothing Data Using Weighted Averages
- Centering Data
- Detrending a Time Series
- Estimating Seasonal Indices
- Deseasonalization of a Time Series
- Forecasting
- Applying Discrete Fourier Transforms

Mathematical Functions

- Introduction
- Using Summation Functions
- Delving into Division
- Mastering Multiplication
- Exploring Exponential and Logarithmic Functions
- Using Trigonometry Functions
- Seeing Signs
- Getting to the Root of Things
- Rounding and Truncating Numbers
- Converting Between Number Systems
- Manipulating Matrices
- Building Support for Vectors
- Using Spreadsheet Functions in VBA Code
- Dealing with Complex Numbers

Curve Fitting and Regression

- Introduction
- Performing Linear Curve Fitting Using Excel Charts
- Constructing Your Own Linear Fit Using Spreadsheet Functions
- Using a Single Spreadsheet Function for Linear Curve Fitting
- Performing Multiple Linear Regression
- Generating Nonlinear Curve Fits Using Excel Charts
- Fitting Nonlinear Curves Using Solver
- Assessing Goodness of Fit
- Computing Confidence Intervals

Solving Equations

- Introduction
- Finding Roots Graphically
- Solving Nonlinear Equations Iteratively
- Automating Tedious Problems with VBA
- Solving Linear Systems
- Tackling Nonlinear Systems of Equations
- Using Classical Methods for Solving Equations

Numerical Integration and Differentiation

- Introduction
- Integrating a Definite Integral
- Implementing the Trapezoidal Rule in VBA
- Computing the Center of an Area Using Numerical Integration
- Calculating the Second Moment of an Area
- Dealing with Double Integrals
- Numerical Differentiation

Solving Ordinary Differential Equations

- Introduction
- Solving First-Order Initial Value Problems
- Applying the Runge-Kutta Method to Second-Order Initial Value Problems
- Tackling Coupled Equations
- Shooting Boundary Value Problems

Solving Partial Differential Equations

- Introduction
- Leveraging Excel to Directly Solve Finite Difference Equations
- Recruiting Solver to Iteratively Solve Finite Difference Equations
- Solving Initial Value Problems
- Using Excel to Help Solve Problems Formulated Using the Finite Element Method

Performing Optimization Analyses in Excel

- Introduction
- Using Excel for Traditional Linear Programming
- Exploring Resource Allocation Optimization Problems
- Getting More Realistic Results with Integer Constraints
- Tackling Troublesome Problems
- Optimizing Engineering Design Problems
- Understanding Solver Reports
- Programming a Genetic Algorithm for Optimization

Introduction to Financial Calculations

- Introduction
- Computing Present Value
- Calculating Future Value
- Figuring Out Required Rate of Return
- Doubling Your Money
- Determining Monthly Payments
- Considering Cash Flow Alternatives
- Achieving a Certain Future Value
- Assessing Net Present Worth
- Estimating Rate of Return
- Solving Inverse Problems
- Figuring a Break-Even Point

Index

Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))

ISBN: 0596008791

EAN: 2147483647

EAN: 2147483647

Year: N/A

Pages: 206

Pages: 206

Authors: David M Bourg

Similar book on Amazon

Flylib.com © 2008-2020.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net