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.
 

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
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.
 

caleb3296

New Member
Joined
Jan 20, 2012
Messages
4
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
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

New Member
Joined
Jan 20, 2012
Messages
4
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

Active Member
Joined
Nov 1, 2007
Messages
403
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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
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

New Member
Joined
Jan 20, 2012
Messages
4
That's really cool, I'll definitely be able to use it. Thanks a lot for your help
 

Janna Lee

New Member
Joined
Jul 15, 2013
Messages
1
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
That's pretty obscure ...
 

Forum statistics

Threads
1,081,556
Messages
5,359,546
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top