VBA Function For Simpson's Rule (i.e. Numerical Integration)

[wellinth]

I wrote a VBA function to implement Simpson's rule. This finds the area under a curve between two points without evaluating an integral analyticaly. A couple people have posted messages on this bulletin board asking how to do that so, here's my solution:

Function Simpson(a As Double, b As Double, n As Integer) As Double
'This function calculates the area under the curve y(x) from
'x=a to x=b using Simpson's rule with n intervals
'Note: n must be even
'
'Local variables
Dim h As Double, sum As Double, term As Double
Dim x As Double
Dim i As Integer
'Do error checking

If n = 0 Or n Mod 2 = 1 Then
Simpson = 0#
MsgBox "Sorry # of intervals has to be > 0 and even"
Exit Function
End If
h = (b - a) / n
x = a
sum = 0#
For i = 1 To n Step 2
term = y(x) + 4 * y(x + h) + y(x + 2 * h)
sum = sum + term
x = x + 2 * h
Next i
Simpson = sum * h / 3
End Function


Function y(x)

'This function computes the pdf of the standard normal
'distribution at x.
y = Application.NormDist(x, 0, 1, False)


End Function

With n = 10, Simpson gives the same answer as Excel's normdist function so, I think my code works. Try it! Any comments are welcome.

Thanks,

- Tom Wellington

PS I created the Simpson function by "jazzing up" the Integral function on page 195 of Bernard Liengme's book (see reference below). Thanks to professor Liengme for letting me post my modification here.

Reference: Bernard V. Liengme, A Guide to Microsoft Excel for Scientists and Engineers. 2nd Edition, 2000. Woborn, MA

 

[Taha]

Thank you Mr.Wellington;

I try that,but I can't reach the result.
What could I do after writing the function in macro?
(I wrote =Simpson(10, 20 , 10) in the cell,but it says ambiguos name detected:Simpson)
I can't understand why it says like this, what could I do?

PS Sorry,I'm new in VBA.

 

[z99boots]

Is there a version available that will work when the function is in x and y and just not x. function result is in a table of data, not an equation.

Thanks

Z