Simpson's Rule in VBA

[Warlie]

Hello,

I'm writing a userform in excel as part of a greater project. A part of the userform needs to calculate integration, and I'm doing so numerically using Simpson's rule. The user sets the parameters (upper limit, lower limit, number of intervals, and the function itself) and then it's calculated.

The results I'm getting are off. For some functions, they're correct. For some functions, the result is the same regardless of much I change the parameters. For some functions, the result just increases more and more as I add more intervals (which obviously it shouldn't). Is there a problem in the mathematics? Could anyone suggest why the result is seemingly close most of the time, sometimes way off, and sometimes spot on?

16504
16505

I'm a beginner, so I've probably used a few unconventional/inefficient ways of doing stuff.

Thanks,
Charlie.

[Warlie]

Here's 3 examples of me plugging in random numbers and slowly increasing the number of intervals. One increases, one decreases, and one goes back and forth.
165091651016511

[p45cal]

Pictures are all well and good, but hard work for us to determine what's happening.
An Excel file please.

[Warlie]

Whoops, Excel file attached.

[p45cal]

Whoops, no it's not.

[Warlie]

oh, not sure what's going on here. Now?

[p45cal]

I expect this will solve some of your problems:
If you enter a formula such as
x^2-2x
the 2x part means 2 * x. Not to Excel.
It's obvious from the code that you're expected to type in:
x^2-2*x

Another example:
x^4+2x^2-3x
becomes
x^4+2*x^2-3*x
or
x^4+(2*x)^2-3*x
depending on whether you want to allow operator precedence.

[Kenneth Hobs]

Interesting animated pic.

Maybe the routine in the link below would help? The book has other routines as well.

https://books.google.com/books?id=X3wdjNVMrwkC&pg=PA134&lpg=PA134&dq=Function+IntegrateS(expression,+variable,+from_lower,+to_upper)&source=bl&ots=HgeMqh0FDh&sig=GngZlhttp://people.stfx.ca/bliengme/exceltips/areaundercurve.htmGu5_zNQRahdp63McAMTEtw&hl=en&sa=X&ved=0ahUKEwjJsoivotDNAhUGwmMKHZMQAQ0Q6AEIHjAA#v=onepage&q=Function%20IntegrateS(expression%2C%20variable%2C%20from_lower%2C%20to_up per)&f=false

This one has some good examples and routines for area under the curve. http://people.stfx.ca/bliengme/exceltips/areaundercurve.htm

This one is more accurate probably but is fairly complex. http://www.hpmuseum.org/cgi-sys/cgiwrap/hpmuseum/archv021.cgi?read=247311

[Warlie]

Well, that's solved that. When I make sure I put the * in for multiplications, it works. Out of interest, what does Excel interpret 2x as then if not 2*x.


I expect this will solve some of your problems:
If you enter a formula such as
x^2-2x
the 2x part means 2 * x. Not to Excel.
It's obvious from the code that you're expected to type in:
x^2-2*x

Another example:
x^4+2x^2-3x
becomes
x^4+2*x^2-3*x
or
x^4+(2*x)^2-3*x
depending on whether you want to allow operator precedence.

[p45cal]

what does Excel interpret 2x as then if not 2*x.
If x was 10 then 2x would be 210

[Warlie]

If x was 10 then 2x would be 210
oh right, thanks mate

[Paul_Hossler]

If x was 10 then 2x would be 210

I'm having a hard time understanding that. This gives me a syntax error. Did I misunderstand?



Option Explicit

Sub test()
Dim x As Long

x = 10

msgbox 2x

End Sub

[Kenneth Hobs]

Paul, it is due to string replacement. I hard coded the 2x for illustration. That would be the integral function in the example file's userform.
e.g.

Sub test()
MsgBox userfunction(10)
End Sub


Private Function userfunction(ByVal currentx As Double) As Double 'runs f(x) with value sent
userfunction = Evaluate(Replace("2x", "x", currentx)) 'replaces "x" in f(x) with the variable currentx
End Function

[Paul_Hossler]

OK, I got it.

I was looking at P45cal's posts, and got confused since somehow I was looking for 20, but that would be userfunction1 below, correct?

Interesting technique, but why wouldn't you just use something simpler like userfunction2?



O
ption Explicit
Sub test()
MsgBox userfunction1(10)
MsgBox userfunction2(10)
End Sub


Private Function userfunction1(ByVal currentx As Double) As Double 'runs f(x) with value sent
userfunction1 = Evaluate(Replace("2*x", "x", currentx)) 'replaces "x" in f(x) with the variable currentx
End Function
Private Function userfunction2(ByVal currentx As Double) As Double 'runs f(x) with value sent
userfunction2 = 2 * currentx
End Function

[Kenneth Hobs]

The string replacement method has been used by some like the first link that I referenced. I have used those routines since I have the book but I don't share that code in forums due to copy rights.

The x or X is replaced by *. One should probably set the input string as lowercase to make Replace() easier. This lets the user enter a text version of the equation that is sent to Evaluate(). 2x was just an example. It could be 2x^4+3.2x^3-Sin(50)x-2.34. As with any routine, one should verify and understand that a routine returns the correct result and especially so for more complicated equations.