Numerical integration to a maximum value using VBA
Numerical integration to a maximum value using VBA
(OP)
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





RE: Numerical integration to a maximum value using VBA
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!
RE: Numerical integration to a maximum value using VBA
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Numerical integration to a maximum value using VBA
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Numerical integration to a maximum value using VBA
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
RE: Numerical integration to a maximum value using VBA
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?
RE: Numerical integration to a maximum value using VBA
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.
RE: Numerical integration to a maximum value using VBA
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.
RE: Numerical integration to a maximum value using VBA
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
RE: Numerical integration to a maximum value using VBA
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