Integrals in VBA

caleb3296

New Member
Joined
Jan 20, 2012
Messages
4
I thought it might be nice to write a function in VBA that would evaluate a definite integral, given a string that represents y as a function of x and the lower and upper limit. Here's what I came up with:


Function INTEG(exp As String, min As Double, max As Double)
Dim t As Double
Dim x As Integer
Dim range As Double
Dim exparray(5000) As Double
Dim dx As Double

range = max - min
dx = range / 5000
t = min
x = 0

Do Until x = 5000
exparray(x) = Evaluate(Replace(exp, "x", CStr(t))) * dx + 0.5 * dx * Abs(Evaluate(Replace(exp, "x", CStr(t + dx))) - Evaluate(Replace(exp, "x", CStr(t))))
t = t + dx
x = x + 1
Loop

INTEG = WorksheetFunction.Sum(exparray)
End Function



It works pretty well so far - it uses the trapezoid rule so it's pretty accurate.
And it works with most integrated excel functions.
For example: =INTEG("x^2+3*x+ln(x)",1,9) in a cell gives a value of 374.4416911.


I just thought I'd share that with anyone who might be able to use it.

Also, one problem I'm having with it is that any cell or function in the expression string that has the character "x" in it gives me an error. The reason this happens is that I used the replace function, and any time there is an x present it is replaced with the value that I wanted it to be evaluated at. The biggest issue here is that I can't use the EXP() function.

If anyone has any ideas about how to make this more eleagant, or to deal with the "x" issue, I'd really appreciate it. I'm pretty new to VBA so there easily could be a far more computationally efficient way to do this.
I'm using Excel 2010, by the way.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
caleb3296:

This Code gives the same result.
It is not a good idea to use certain Excel key words like 'Range' as variable names.
Various conventions or systems can be used in variable names.
Below d, l, s signify double, long, string.
Names that are not so simple as 'x' better allow search and replace to change a name.
This also allows adding the number of sections to divide the interval into, 5000 here.
=INTEG("x^2+3*x+LN(x)",1,9,5000)
Code:
Function INTEG(sExp As String, dMin As Double, dMax As Double, lBit As Long)
Dim dX As Double
Dim lX As Long
dX = (dMax - dMin) / lBit
For lX = 1 To lBit
    INTEG = INTEG + Evaluate(Replace(sExp, "x", dMin)) * dX + 0.5 * dX * Abs(Evaluate(Replace(sExp, "x", dMin + dX)) - Evaluate(Replace(sExp, "x", dMin)))
    dMin = dMin + dX
Next lX
End Function
A workaround to the 'x' in the CELL FORMULA is to replace as below:
Code:
Function INTEG(sExp As String, dMin As Double, dMax As Double, lBit As Long)
Dim AAA As String
sExp = Replace(sExp, "EXP", "AAA")
Dim dX As Double
Dim lX As Long
dX = (dMax - dMin) / lBit
For lX = 1 To lBit
    INTEG = INTEG + Evaluate(Replace(Replace(sExp, "x", dMin), "AAA", "EXP")) * dX + 0.5 * dX * _
        Abs(Evaluate(Replace(Replace(sExp, "x", dMin + dX), "AAA", "EXP")) _
        - Evaluate(Replace(Replace(sExp, "x", dMin), "AAA", "EXP")))
    dMin = dMin + dX
Next lX
End Function
This will work with EXP Function.

For:
=INTEG("EXP(x)+3*x+LN(x)",1,9,5000)
Result:
8232.14239

But actually both versions of the Function give the same result.
That is because I used Upper Case letters for the Excel Functions, and the 'x' in the string formula is lower case.
The REPLACE here must be case sensitive.
 
Upvote 0
JackBean,

Thanks for your advice. I tend to forget the conventions when I bounce back and forth between different programming languages. It seems like the corrections you made speed up the computing time a bit. Also I noticed 5000 intervals is sometimes more than necessary, so it's nice to be able to control that.

Thanks for helping with the EXP issue too - I never noticed that it worked when I put it in all caps.

- Caleb
 
Upvote 0
I have a similar routine to do integration (but using Simpson's method), and use a regular expression with pattern "\bx\b" (x standing alone between non-word boundaries) to replace x with its values.
 
Upvote 0
shg,

I was thinking about trying something like using non-word boundaries around x to determine which values to use but it seemed like sort of a daunting task for a mediocre programmer like me. I bet that'd be useful. Does Simpson's rule increase the accuracy to computation time ratio by a lot?
 
Upvote 0
If you look up Simpsons rule you will see that it
has no error in certain situations.
I would expect it to calculate slower but I have not tried it.

Below the previous Code has been modified for the Trapezoid rule as shg suggested.
This now gives the same result for the lower case "exp" as for the upper case "EXP" for:
=INTEG("exp(x)+3*x+ln(x)",1,9,500)
This Regular Expression version runs slower than the previous version.
Code:
Function INTEG(sExp As String, dMin As Double, dMax As Double, lBit As Long)
Dim dX As Double
Dim lX As Long
Dim oOb As Object
If oOb Is Nothing Then Set oOb = CreateObject("vbscript.regexp")
dX = (dMax - dMin) / lBit
With oOb
    .Global = True
    .Pattern = "\bx\b"
    For lX = 1 To lBit
        INTEG = INTEG + Evaluate(.Replace(sExp, dMin)) * dX _
            + 0.5 * dX * Abs(Evaluate(.Replace(sExp, dMin + dX)) _
            - Evaluate(.Replace(sExp, dMin)))
        dMin = dMin + dX
    Next lX
End With
End Function
 
Upvote 0
Below the previous Code has been modified for the Trapezoid rule as shg suggested.
Simpson's method treats each each three points as a parabola, not a trapezoid.

The code is at http://www.box.com/s/fb3orv4uu1r454n5rl3d

The routine allows you to specify the number of intervals, and allows you to specify the values of constants in the expression.

There's a routine at the end that I haven't finished. It's supposed to do an adaptive number of bisections (2, 4, 8, 16, ...) that stops when the output is 'stable', so you don't need to specify the number of iterations at all -- but I haven't figured out a way to define 'stable' while still allowing for functions large and small.
 
Upvote 0
Your code has been really helpful. However, I am confused that why it works to [TABLE="width: 847"]
<tbody>[TR]
[TD="class: xl64, width: 847"]" 70.71*sqrt(.98+.02*cos(x)^2+sin(x)^2*cos(x)^2/(4.56-cos(x)^2)+2*cos(x)*sin(x)*.14/sqrt(4.56-cos(x)^2)) "
but fails to calculate [TABLE="width: 847"]
<tbody>[TR]
[TD="class: xl66, width: 847"]" 104.31*sqrt(.34+.67*cos(x)^2+sin(x)^2*cos(x)^2/(1.34-cos(x)^2)+2*cos(x)*sin(x)*.82/sqrt(1.34-cos(x)^2)) ". The two expressions are quite similar.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

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