Passing a function defined in a cell to a VBA function

ssheikh

New Member
Joined
Apr 30, 2012
Messages
4
I have a numeric integration function using Simpson rule as follows:

Code:
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Just realized that I forgot to eclose the named references in [ ]. Function y(x) should have been:

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...:confused:

M.
 
Upvote 0
Gotta a workaround !!!

Code:
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top