VBA to carry out Simpson's Rule for Tabulated (Equispaced) Data

[bloodtalon]

There are several codes for Simpson's Rule for functions in excel. However, I couldn't find any for tabulated data. I am trying to make a simple one. However, it is not doing anything. The biggest issue is, I think, the input arguments are not arrays. And I have tried some things but it's not working. The attached file has the code. I am trying to calculate the value in Cell C5 using the x data in B13:B17 and y data in C13:C17. The error happens in 21768this line, I believe, when trying to debug it (just exits and returns VALUE!):

h = x(1) - x(0)

[Paul_Hossler]

The x and f are ranges, so you can make them into arrays using .Transpose

Answer not exact, so I might be off by a loop some where




Option Explicit

Function Simpson13(x As Range, f As Range) As Variant
Dim n1 As Long, n2 As Long, n As Long, m As Long
Dim a1 As Variant, a2 As Variant
Dim h As Double, I As Double

n1 = x.Cells.Count
n2 = f.Cells.Count

If n1 <> n2 Then
' exit if arrays not equally-sized
Simpson13 = CVErr(xlErrNum)
Exit Function
End If

'make arrays - index starts at 1 and ends at n1
a1 = Application.WorksheetFunction.Transpose(x)
a2 = Application.WorksheetFunction.Transpose(f)

n = n1 - 1 ' <<<<<<<<<<<< start checking here
h = a1(2) - a1(1)
I = a2(2)
For m = 2 To n - 1 Step 2
I = I + 4 * a2(m) + 2 * a2(m + 1)
Next m

I = I + 4 * a2(n - 1) + a2(n)

Simpson13 = I * h / 3

End Function

[bloodtalon]

Thanks a lot. I had a mistake in the formula earlier. But it's fixed now.