Integration/area under a curve VBA code?

WAYNEL · 01-01-2006, 05:50 PM

Hi

I have built a spreadsheet that can calculate the area under a curve of
a
set of data but I would like to have this in VBA for Excel, in say
Integral(C1,C2) format or a button on the toolbar.

Can anyone point me in the right direction for acquiring the code?

Cheers

WayneL

Shaz · 01-02-2006, 09:55 AM

try this link, maybe you will find what you are looking for.=E7

http://www.vbnumericalmethods.com/vb...thods/math.asp

cheers

WAYNEL wrote:
> Hi
>
>
>
> I have built a spreadsheet that can calculate the area under a curve of
> a
> set of data but I would like to have this in VBA for Excel, in say
> Integral(C1,C2) format or a button on the toolbar.
>
> Can anyone point me in the right direction for acquiring the code?
>=20
>=20
>=20
> Cheers
>=20
>=20
>=20
> WayneL

Bernard Liengme · 01-02-2006, 10:35 AM

see www.stfx.ca/people/bliengme/ExcelTips

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"WAYNEL" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
>
>
> I have built a spreadsheet that can calculate the area under a curve of
> a
> set of data but I would like to have this in VBA for Excel, in say
> Integral(C1,C2) format or a button on the toolbar.
>
> Can anyone point me in the right direction for acquiring the code?
>
>
>
> Cheers
>
>
>
> WayneL
>

WayneL · 01-02-2006, 01:20 PM

Hi

I have a set of data that needs integrating but it does not fit an easy
function therefore I need some technique (fuction) like that seen in Flex
Pro. In this package you select X and Y and press a button titled "Area
under Curve". This software is expensive and I am sure this could be done
in Excel.

Cheers

WayneL

P.S

Some example data I am trying to find the Area Under the Curve.

Seconds Voltage
0.095 4.55E-04
2.526 4.14E-04
4.659 3.74E-04
6.791 3.45E-04
8.923 3.25E-04
11.055 3.11E-04
13.188 3.09E-04
15.321 3.05E-04
17.454 2.93E-04
19.587 2.86E-04
21.72 2.80E-04
23.855 2.76E-04
25.987 2.73E-04
28.119 2.70E-04
30.301 2.68E-04
32.433 2.66E-04
34.566 2.64E-04
36.698 2.62E-04
38.83 2.61E-04
41.172 2.60E-04
43.305 2.58E-04
45.436 2.57E-04
47.569 2.57E-04
49.702 2.56E-04
51.834 2.56E-04
53.966 2.56E-04
56.1 2.56E-04
58.233 2.56E-04
60.366 2.56E-04
62.499 2.55E-04
64.632 2.55E-04
66.772 2.55E-04
68.954 2.55E-04
71.086 2.55E-04
73.219 2.53E-04
75.357 2.54E-04
77.489 2.56E-04
79.621 2.55E-04
81.753 2.55E-04
83.884 2.55E-04
86.016 2.55E-04
88.149 2.55E-04
90.288 2.55E-04
92.42 2.55E-04
94.552 2.55E-04
96.684 2.55E-04
98.816 2.55E-04
100.949 2.56E-04
103.082 2.56E-04
105.214 2.56E-04
107.347 2.56E-04
109.549 2.55E-04
111.681 2.55E-04
113.813 2.54E-04
115.945 2.53E-04
118.077 2.53E-04
120.208 2.53E-04
122.341 2.53E-04
124.473 2.53E-04
126.605 2.53E-04
128.737 2.53E-04
130.87 2.53E-04
133.003 2.54E-04
135.136 2.54E-04
137.269 2.55E-04
139.401 2.53E-04
141.534 2.55E-04
143.666 2.57E-04
145.798 2.58E-04
147.93 2.59E-04
150.061 2.58E-04
152.193 2.59E-04
154.325 2.56E-04
156.457 2.57E-04
158.589 2.58E-04
160.723 2.57E-04
162.854 2.58E-04
164.986 2.60E-04
167.119 2.60E-04
169.251 2.61E-04
171.384 2.62E-04
173.517 2.63E-04
175.65 2.64E-04
177.785 2.67E-04
179.923 2.59E-04
182.055 2.59E-04
184.188 2.60E-04
186.327 2.60E-04
188.51 2.61E-04
190.649 2.60E-04
192.787 2.59E-04
194.918 2.60E-04
197.059 2.60E-04
199.2 2.60E-04
201.338 2.60E-04
203.476 2.61E-04
205.611 2.61E-04
207.744 2.62E-04
209.882 2.62E-04
212.02 2.63E-04
214.159 2.67E-04
216.292 2.65E-04
218.425 2.65E-04
220.557 2.65E-04
222.689 2.66E-04
224.821 2.66E-04
226.954 2.66E-04
229.094 2.67E-04
231.226 2.67E-04
233.359 2.66E-04
235.49 2.67E-04
237.622 2.67E-04
239.805 2.68E-04
241.938 2.69E-04
244.072 2.69E-04
246.204 2.70E-04
248.345 2.71E-04
250.478 2.71E-04
252.611 2.72E-04
254.744 2.73E-04
256.876 2.74E-04
259.009 2.75E-04
261.142 2.76E-04
263.274 2.75E-04
265.407 2.76E-04
267.539 2.76E-04
269.67 2.77E-04
271.802 2.77E-04
273.935 2.78E-04
276.355 2.79E-04
278.677 2.79E-04
280.937 2.81E-04
283.072 2.82E-04
285.205 2.83E-04
287.337 2.84E-04
289.47 2.84E-04
291.602 2.85E-04
293.743 2.86E-04
296.031 2.86E-04
298.163 2.87E-04
300.295 2.88E-04

***********************************************
"Bernard Liengme" <[email protected]> wrote in message
news:[email protected]...
> see www.stfx.ca/people/bliengme/ExcelTips
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "WAYNEL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi
>>
>>
>>
>> I have built a spreadsheet that can calculate the area under a curve of
>> a
>> set of data but I would like to have this in VBA for Excel, in say
>> Integral(C1,C2) format or a button on the toolbar.
>>
>> Can anyone point me in the right direction for acquiring the code?
>>
>>
>>
>> Cheers
>>
>>
>>
>> WayneL
>>
>
>

WayneL · 01-06-2006, 02:30 AM

Thanks Gregory Vainberg (http://www.vbnumericalmethods.com) for this perfect
solution.

I have tested it with FlexPro and the value correlates.

Cheers

WayneL

Hey Wayne,

I have a number of different methods that you can use, but the easiest
technique is trapezoidal integration. On the website I have a version that
takes the function name as a parameter, but it can be easily adapted to use
2 vectors as parameters as follows:

Public Function TRAPnumint(x, y) As Double

n = Application.Count(x)

TRAPnumint = 0

For t = 2 To n

TRAPnumint = TRAPnumint + 0.5 * (x(t) - x(t - 1)) * (y(t - 1) +
y(t))

Next

End Function

Where x is the column of x values and y is the column of f(x) values.

Hope this helps,

Gregory Vainberg

http://www.vbnumericalmethods.com

"WayneL" <[email protected]> wrote in message
news:[email protected]...

> Hi
>
>
>
> I have built a spreadsheet that can calculate the area under a curve of a
> set of data but I would like to have this in VBA for Excel, in say
> Integral(C1,C2) format or a button on the toolbar.
>
> Can anyone point me in the right direction for acquiring the code?
>
>
>
> Cheers
>
>
>
> WayneL
>
>
"WAYNEL" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
>
>
> I have built a spreadsheet that can calculate the area under a curve of
> a
> set of data but I would like to have this in VBA for Excel, in say
> Integral(C1,C2) format or a button on the toolbar.
>
> Can anyone point me in the right direction for acquiring the code?
>
>
>
> Cheers
>
>
>
> WayneL
>

**integreat** · 05-19-2006, 10:47 PM

I've got the VBA code you want, cause I'm writing it. If you get to see this message then just wait a few days and I'll post it.

WAYNEL · 05-20-2006, 03:40 PM

Thanks integreat

WAYNEL · 05-20-2006, 03:40 PM

Thanks integreat

**integreat** · 05-20-2006, 06:48 PM

I did not get a chance to work on it today but it is VBA code that calculates a definite integral (area uder the curve)

It is a simple matter to use this VBA code. I named the function "integrateTrapz" because it integrates by using the trapezoidal method. You use it just like a normal math function in excel such as SUM or ABS

It will take the values of whatever is in the cell range that you want to use as input.

There is a bug in my code and I'm trying to presently get it fixed. It has been some 5 years since I have done any programming. I was an expert with Quick Basic but it seems visual basic has many changes from quick basic.

Trivia
The last version of quick basic came out in 1988 (v4.5)

------
10 minutes later

I found the bug . The variable that I was having trouble with should have been a SINGLE not a LONG!!. Thanks for the help from this site!