×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Numerical integration to a maximum value using VBA
2

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

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!

RE: Numerical integration to a maximum value using VBA

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.

RE: Numerical integration to a maximum value using VBA

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.

RE: Numerical integration to a maximum value using VBA

(OP)
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

RE: Numerical integration to a maximum value using VBA

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?

RE: Numerical integration to a maximum value using VBA

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.

RE: Numerical integration to a maximum value using VBA

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.

RE: Numerical integration to a maximum value using VBA

(OP)
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

RE: Numerical integration to a maximum value using VBA

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources