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.

 

[ssheikh]

Just realized that I forgot to eclose the named references in [ ]. Function y(x) should have been:

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

 

[pgc01]

Hi
Welcome to the board

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.

You can use Evaluate to evaluate an expression that is passed as a string, as long as it respects the formula syntax.

This is an example that you can adapt:

Sub Test()
Dim sEq As String
Dim xx As Double
 
xx = 5
sEq = "xx+3"
MsgBox y(sEq, xx)
 
sEq = "xx^3-4*xx^2-3*xx+1"
MsgBox y(sEq, xx)
 
xx = Application.Pi() / 6
sEq = "Sin(xx)"
MsgBox y(sEq, xx)
 
End Sub
 
Function y(sEq As String, xx As Double) As Double
y = Evaluate(Replace(sEq, "xx", xx))
End Function

 

[Marcelo Branco]

Hi PGC,

I tried your code but i'm getting an error 13 - Type Mismatch in the last example (the first two worked perfectly)

xx = Application.Pi() / 6
sEq = "Sin(xx)"
MsgBox y(sEq, xx)

probably because my regional setting for the decimal separator is , (comma).

To check what is going on i tried
MsgBox Replace(sEq, "xx", xx)
that results in
Sin(0,523598...)

Cannot find a workaround...

Could you help me?

M.

 

[pgc01]

Hi Marcelo

I cannot test now, I'll try later.

Are you using the Portuguese version of Excel? If that's the case please try a formula with Cos() instead of Sin(), as it is the same function name in both languages.

 

[Marcelo Branco]

PGC,

The problem is not in the function name. If i set
xx=1
the Sin works perfectly resulting in
0.84147...

I'm almost sure the problem is in the Regional Setting.

Weird...

M.

 

[Marcelo Branco]

I've tried Cos - same problem.

M.

 

[Marcelo Branco]

Gotta a workaround !!!

Sub Test()
Dim sEq As String
Dim xx As [COLOR=blue]Variant
[/COLOR] 
xx = 5
sEq = "xx+3"
MsgBox y(sEq, xx)
 
sEq = "xx^3-4*xx^2-3*xx+1"
MsgBox y(sEq, xx)
 
xx = Application.Pi() / 6
[COLOR=blue]xx = Replace(xx, ",", ".")
[/COLOR][COLOR=blue]sEq = "Sin(" & xx & ")"
[/COLOR]MsgBox y(sEq, xx)
End Sub
 
Function y(sEq As String, [COLOR=blue]ByVal[/COLOR] xx As Double) As Variant
y = Evaluate(Replace(sEq, "xx", xx))
End Function

M.

 

[ssheikh]

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

 

[ssheikh]

Hi PGC,

That is Awesome! Thanks for the quick reply. It worked like a charm.

Here is how my simpson rule numeric integration function looks like:

Function SimpsonInt(dLower As Double, dUpper As Double, n As Integer, sEq As String) As Double
'This function calculates the area under the curve y(x) defined by sEq from
'x=dLower to x=dUpper using Simpson's rule with n intervals
'Note: the independent variable in sEq must be "_x_"
'
'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"
    Exit Function
End If
n = n * 2
h = (dUpper - dLower) / n
x = dLower
sum = 0#
For i = 1 To n Step 2
    term = y(sEq, x) + 4 * y(sEq, x + h) + y(sEq, x + 2 * h)
    sum = sum + term
    x = x + 2 * h
Next i
SimpsonInt = sum * h / 3
End Function

Function y(sEq As String, xx As Double) As Double
y = Evaluate(Replace(sEq, "_x_", xx))
End Function