**Basic theory**

Almost two weeks after my initial post about numerical integration in Excel I return to this subject but with a different approach. So, below you will find a custom VBA function that is able to calculate the integral of a given expression â€“ as a function of xi â€“ using the composite Simpsonâ€™s rule. Before we go to the function letâ€™s first refer to the original Simpsonâ€™s rule.

According to Wikipedia, â€śthe Simpsonâ€™s rule is a method for numerical integration, the numerical approximation of definite integrals. Specifically, it is the following approximation:

If the interval of integration [a, b] is in some sense â€śsmallâ€ť, then Simpsonâ€™s rule will provide an adequate approximation to the exact integral. By small, what we really mean is that the function being integrated is relatively smooth over the interval [a, b]. For such a function, a smooth quadratic interpolant like the one used in Simpsonâ€™s rule will give good results.

However, it is often the case that the function we are trying to integrate is not smooth over the interval. Typically, this means that either the function is highly oscillatory, or it lacks derivatives at certain points. In these cases, Simpsonâ€™s rule may give very poor results. One common way of handling this problem is by breaking up the interval [a, b] into a number of small sub-intervals. Simpsonâ€™s rule is then applied to each sub-interval, with the results being summed to produce an approximation for the integral over the entire interval. This sort of approach is termed the composite Simpsonâ€™s rule.

Suppose that the interval [a, b] is split up in n sub-intervals, with n an even number. Then, the composite Simpsonâ€™s rule is given by:

**VBA code**

Option Explicit

Function SimpsonIntegral(InputFunction As String, Xstart As Double, _

Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Xend As Double, NumberOfIntervals As Long) As Variant

'----------------------------------------------------------------------------------------------------------

Â Â Â 'Calculates the integral of InputFunction using the Composite Simpson's Rule.

Â Â Â 'InputFunction is the function to integrate, expressed as a function of "xi".

Â Â Â 'Examples: "5*xi + 3", "4*xi^5 + 5*xi^2 + 3*xi + 5", "cos(xi) + tan(xi)".

Â Â Â 'Xstart is the initial value of xi.

Â Â Â 'Xend is the final value of xi.

Â Â Â

Â Â Â 'NumberOfIntervals used for integration (rounded up to an even number).

Â Â Â 'Bear in mind that this value should be large enough in order to achieve

Â Â Â 'a sufficeint degree of accuracy. As a drawback it takes longer computational time to complete.

Â Â Â

Â Â Â 'Function used directly in a worksheet: =SimpsonIntegral("32*xi^5 + 5*xi^3 + 12*xi + 1"; 0; 100; 1000)

Â Â Â 'Note that depending on your settings you might need to change the ";" with ",".

Â Â Â 'Function called in VBA: Result = SimpsonIntegral("4*xi^3 + 5*xi^(-1/2) + 5", 1, 10, 100)

Â Â Â

Â Â Â 'By Christos Samaras

Â Â Â 'Date: 23/6/2013

Â Â Â 'https://myengineeringworld.net/////

'----------------------------------------------------------------------------------------------------------

Â Â Â 'Declaring the necessary variables.

Â Â Â Dim iÂ Â Â Â Â Â Â Â Â Â As Long

Â Â Â Dim TheStepÂ Â Â Â As Double

Â Â Â Dim CumulativeÂ As Double

Â Â Â

Â Â Â 'Check if the NumberOfIntervals is a valid number.

Â Â Â If NumberOfIntervals < 1 Then

Â Â Â Â Â Â Â SimpsonIntegral = "The number of intervals must be > 0!"

Â Â Â Â Â Â Â Exit Function

Â Â Â End If

Â Â Â Â Â Â Â Â Â Â Â

Â Â Â On Error GoTo ErrorHandler

Â Â Â

Â Â Â 'Check if the initial and final value of xi are different.

Â Â Â If Xstart = Xend Then

Â Â Â Â Â Â Â SimpsonIntegral = "Xstart must be different than Xend!"

Â Â Â Â Â Â Â Exit Function

Â Â Â End If

Â Â Â

Â Â Â 'Make the NumberOfIntervals even number.

Â Â Â NumberOfIntervals = 2 * NumberOfIntervals

Â Â Â

Â Â Â 'Calculating the step value.

Â Â Â TheStep = (Xend - Xstart) / NumberOfIntervals

Â Â Â

Â Â Â 'Calculating the initial value for Xstart.

Â Â Â Cumulative = FunctionResult(InputFunction, Xstart)

Â Â Â 'Loop for odd values.

Â Â Â For i = 1 To NumberOfIntervals - 1 Step 2

Â Â Â Â Â Â Â Cumulative = Cumulative + 4 * FunctionResult(InputFunction, Xstart + i * TheStep)

Â Â Â Next i

Â Â Â

Â Â Â 'Loop for even values.

Â Â Â For i = 2 To NumberOfIntervals - 2 Step 2

Â Â Â Â Â Â Â Cumulative = Cumulative + 2 * FunctionResult(InputFunction, Xstart + i * TheStep)

Â Â Â Next i

Â Â Â 'Calculating the final value for Xend.

Â Â Â Cumulative = Cumulative + FunctionResult(InputFunction, Xend)

Â Â Â

Â Â Â 'Finally, return the result of integration.

Â Â Â SimpsonIntegral = Cumulative * TheStep / 3

Â Â Â

Â Â Â Exit Function

Â Â Â 'In case of an error show an appropriate message.

ErrorHandler:

Â Â Â SimpsonIntegral = "Unable to calculate the integral of " & InputFunction & "!"

Â Â Â Exit Function

Â Â Â

End Function

Private Function FunctionResult(MyFunction As String, x As Double) As Double

Â Â Â

Â Â Â 'Evaluates a given expression (as a function of xi) for a given xi value.

Â Â Â 'Example: FunctionResult("5*xi + 3", 2) returns 5*2 + 3 = 13

Â Â Â

Â Â Â 'By Christos Samaras

Â Â Â 'Date: 23/6/2013

Â Â Â 'https://myengineeringworld.net/////

Â Â Â

Â Â Â FunctionResult = Evaluate(WorksheetFunction.Substitute(MyFunction, "xi", x))

Â Â Â

End Function

**Comparison with the Trapezoidal rule**

In all of the cases that I tried the above function the results were better compared to my previous code. For example, we saw that for the function f(x) = 4*x^2, for x = 0 to 9 the Trapezoidal rule returns the value 978, whereas the composite Simpsonâ€™s rule returns 972, which is the correct value:

So, here is another method to calculate the integral of a given expression in Excel. I hope to find it useful.

**Download it from here**

The file can be opened withÂ **Excel 2007 **or newer. Please enable macros before using it.

**Read also**

Numerical Integration In Excel Using The Trapezoidal Rule

Calculating The Area Of A Simple Polygon Using The Shoelace Algorithm