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.

 

[Fazza]

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>

hi, Janna. What is the range of x? (I'm wondering what the function look like over that range?) regards

 

[shg]

Ah -- you end up with a negative number inside the radical for values of x from about 116.6 to 120.5 degrees.

 

[allizdor]

https://picload.org/thumbnail/wwcdadr/excel.jpg

Hello,

I m totaly new in VBA but I really want to solve an integral.

Nevertheless the following error occur. It seems Excel (2013) dislike the syntax. What have I forgotten?

 

[allizdor]

https://picload.org/view/wwcdadr/excel.jpg.html
better quality

 

[mahdiahmadi71]

Hi
I want to take this Integral:

F(alfa,beta)=|0 until 1 (erf(alfa*t^-.5) * erf(beta*t^-.5)) dt

plz help me to how do it
tnx

 

[propane]

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.

I have been trying to use this code or codes similar to this, they all have a problem for me that it returns the error #VALUE for me. after a bit of closer inspection problem seems to be with the decimals. as if there is any numbers with decimals I would get an error but e.g =INTEG("x",1,9,8) would return the right results.
I have tried changing the excel decimal points from comma to dot and back.
I have tried adding UseSystemSeparators = False , .DecimalSeparator = "," or DecimalSeparator = "."
I have tried changing the dMin and other variables from double to string.
I have tried changing dMin and other variables into strings with Cstr()
None of these seem to be able to work.

I would really appreciate any input, although this is a pretty old post this integration method directly into excel is very useful for my case.