Integration/area under a curve VBA code?

[WayneL]

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

[john]

Post by WayneL
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?

You can write your own VB macro with a bit of practice. Go into
Tools/Macro/VisualBasicEditor

select

Insert/module
Insert/procedure
select "function" button

Next write your integration routine in Basic. Parameter input variables
are determined by selecting cells before calling the routine.
To select a whole column of cells for the integration, refer to the MS
Visual Basic help files in the editor as I can't remember exactly how to
do this at this point in time.

Exit the editor by
File/CloseandReturn. or Alt+Q


john

[David A. Heiser]

Post by john

Post by WayneL
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?

You can write your own VB macro with a bit of practice. Go into
Tools/Macro/VisualBasicEditor
select
Insert/module
Insert/procedure
select "function" button
Next write your integration routine in Basic. Parameter input variables
are determined by selecting cells before calling the routine.
To select a whole column of cells for the integration, refer to the MS
Visual Basic help files in the editor as I can't remember exactly how to
do this at this point in time.
Exit the editor by
File/CloseandReturn. or Alt+Q
john

+++++++++++++++++++++++++++
A quick way to do this is to first build the workseheet starting data.

Then turn on the macro routine, say give it a name like macro5

Do all your worksheet cell manipulations, entries and setting in cell
equation, until you have a final form

Turn off the macro recording mode.

Go to vba (alt + F11)

Look up the macro in the structure knowing its name.

This gives you all the commands in vba form, you then have a good starting
base for modifying it to be a standalone subroutine. I use this method to
give me a good starting point for a specific vba subroutine or function.
This can have a lot of retrials and repeats, which can be edited out.

If your output is just one cell, then a function would be appropriate.

David Heiser

[john]

Post by David A. Heiser

Post by john

Post by WayneL
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?

You can write your own VB macro with a bit of practice. Go into
Tools/Macro/VisualBasicEditor
select
Insert/module
Insert/procedure
select "function" button
Next write your integration routine in Basic. Parameter input variables
are determined by selecting cells before calling the routine.
To select a whole column of cells for the integration, refer to the MS
Visual Basic help files in the editor as I can't remember exactly how to
do this at this point in time.
Exit the editor by
File/CloseandReturn. or Alt+Q
john

+++++++++++++++++++++++++++
A quick way to do this is to first build the workseheet starting data.
Then turn on the macro routine, say give it a name like macro5
Do all your worksheet cell manipulations, entries and setting in cell
equation, until you have a final form
Turn off the macro recording mode.
Go to vba (alt + F11)
Look up the macro in the structure knowing its name.
This gives you all the commands in vba form, you then have a good starting
base for modifying it to be a standalone subroutine. I use this method to
give me a good starting point for a specific vba subroutine or function.
This can have a lot of retrials and repeats, which can be edited out.
If your output is just one cell, then a function would be appropriate.
David Heiser

this method is much easier than trying to navigate the semi-dylexic MS
help files.


John

[WayneL]

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

Post by WayneL
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
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