Passing a function defined in a cell to a VBA function

[ssheikh]

I have a numeric integration function using Simpson rule as follows:

Function SimpsonInt(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
'
'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  Then
  Simpson = 0#
  MsgBox "Sorry # of intervals has to be > 0 and even"
  Exit Function
End If
n = 2 * n
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
 
SimpsonInt = sum * h / 3
 
End Function

Function y(x As Double) As Double
 
' a_, b_, and Vmax are named cells in spreadsheet
y = x ^ (a_ - 1) * (Vmax - x) ^ (b_ - 1)
 
End Function

How can I pass the function y(x) to the SimpsonInt function. I have several different equations that I want to be able to take the integral of. I am trying to find a way of passing that equation that defines y(x) to SimpsonInt so I dont have to define each equation as a function in VBA.

Thanks.

 

[pgc01]

Marcelo, I'm glad it's working now. Cheers.

 

[pgc01]

Ssheikh, you're welcome, thanks for the feedback.

 

[Marcelo Branco]

Hi M.

I recall reading about VBA not working with regional settings of Excel. Have a look at the two functions in http://books.google.com/books?id=wlLQlMuSwAcC&pg=PT481&source=gbs_toc_r&cad=4#v=onepage&q&f=false under the Excel 2007's International options.

-Shahid

Shahid,

Thank you for the link. Really appreciate.

M.

 

[Marcelo Branco]

Marcelo, I'm glad it's working now. Cheers.

Cheers

M.