Integrals in VBA

[caleb3296]

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.

 

[JackBean]

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)

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:

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.

 

[caleb3296]

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

 

[shg]

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.

 

[caleb3296]

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?

 

[JackBean]

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.

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

 

[shg]

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.

 

[caleb3296]

That's really cool, I'll definitely be able to use it. Thanks a lot for your help

 

[Janna Lee]

Your code has been really helpful. However, I am confused that why it works to

" 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 " 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. <tbody> </tbody>

<tbody>
</tbody>

 

[shg]

That's pretty obscure ...