Numerical integration to a maximum value using VBA 2

[Latexman]

I have a plain old spreadsheet that I manually manipulate to numerically integrate a function until it reaches a maximum value. I am trying to add some VBA automation to it to speed things up and increase accuracy. One thing I would like it to do is automatically halt the iterating as soon as the function is summed to it's maximum value. Can someone offer some advice on how to do that? Thanks!

Good luck,
Latexman

 

[onlyadrafter]

Hello,

You may want to do something like

Do until range("A1").value=100
'your code here
loop

this is a bit of a guess, as I'm not really sure what you are doing

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[electricpete]

I interpreted that you wanted to find a local maximum of the result of the integration. That would occur when the numbers you're integrating change from positive to negative. Did I misunderstand the question?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

You can use Excel's solver add-in to maximize a cell's value by changing other cells, and even add some constraints if you want.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Latexman]

My initial VBA program used the Do While Loop for control. I still have not mastered it yet though.

I'm numerically integrating a complex function to it's maximum value. It looks something like this going down a column:

330.406
330.649
330.747
330.699
330.507

I want execution to stop after I know 330.747 is > 330.699.

After I modify what I have and try it again, I'll post my code.



Good luck,
Latexman

 

[cowski]

A few questions:
1) Your first post says numerically integrating, but the description in the second post sounds more like finding the local min/max of a function (more of a job for differentiating), is your goal to find the local maximum?

2) joerd made a good suggestion about the goal seek/solver, have you tried that or is there a good reason it cannot be used?

3) by 'complex function' do you mean 'complicated' or a function that involves actual complex numbers?

 

[electricpete]

As I see it you know what you are going to do in vba and it sounds pretty straight forward to carry a lastvalue and thisvalue in your loop and check when lastvalue>thisvalue

Just to expand my previous post, the same thing could be accomplished even without integrating by examining the input data (which is the slope of the output data of the integration). When the input data changes from positive to negative, the integration output will start decreasing.

Either way will work. If there is noise in the data you might want to look for two consecutve decreases in the output (two consecutive inputs below 0).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[prost]

This worked for me:
There might be a better method in Numerical Recipes. You could translate subroutines and functions outlined in Numerical Recipes in Fortran program into a VBA language. In Numerical Recipes are routines that calculate the value of the subintervals at midpoints, and keep adding number of subintervals until the value of the integral converges numerically. For instance, 'qromb' and 'qromo' are set up to numerically integrate while checking convergence. There are several subroutines and functions associated with these two subroutines. These two subroutines use the Romberg integration method, which normally is used when there are singularities in the argument at the upper or lower bounds (if you try to use Simpsons method when the argument is singular at one of the bounds, VB stops with errors for obvious reasons). The qromo is great because it automatically assumes the argument you are integrating is singular at the upper or lower bound.

 

[Latexman]

electricpete, your advice to use a lastvalue and firstvalue broke the ice! I did it all in memory and got away from using worksheet values to test for convergence. That's what did it! As promised, here's the code:

Sub DataFiller()
Dim Row As Long
Dim Gfirst As Double
Dim Glast As Double
Row = 1
Gfirst = 0
Glast = Range("E18")
Worksheets("Sheet1").Activate
Range("A18").Activate
Do While Gfirst < Glast
Gfirst = Glast
Range("A18:F18").Copy ActiveCell.Offset(Row, 0)
Glast = ActiveCell.Offset(Row, 4).Value
Row = Row + 1
Loop
Row = Row - 1
Range("A18:F18").Offset(Row, 0).Select
Selection.Copy
Range("B7:B12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub


Good luck,
Latexman

 

[Latexman]

cowski,

1 - I'm actually doing both simultaneously. As the program steps down columns A thru F, it is evaluating several complicated functions and combining and summing them in column E. That's the numerical integration. Column G rises monotonically as it progresses, so at the first step that G decreases, I cease the integration.

2 - I don't need a high powered routine to find the maximum, just VBA code.

3 - Complicated.

Good luck,
Latexman