Goal Seek in VB-VBForums
Results 1 to 6 of 6

Thread: Goal Seek in VB

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2007
    Posts
    3

    Goal Seek in VB

    I had a calculation performed on a spreadsheet which I transfered to Visual Basic, the spreadsheet performed calculation using a value derived using the goal seek function.

    The code is now something as below:


    TargetValue = 1500
    arbval = -100000 ' this is intial value assumed as the goal seek value


    Do While TotFlo <= TargetValue
    Totflo = 0
    For i = 1 To 365
    SixFlow(i) = calculation performed using arbval and other variables
    Totflo = Totflo + SixFlow(i)
    Next i
    arbval = arbval + 0.001

    Loop

    as you can see i increase arbval by 0.001 on each loop to put into the caculation trying to copy goal seek.

    Can anybody suggest some code that would allow to decrease the processing time, arbval will be between -100,000 and 100,000

    thanks in advance

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

    Re: Goal Seek in VB

    Welcome to VBForums

    I can't find any existing Goal Seek functions (here, or elsewhere on the web). The best I could find is the suggestion here to re-arrange the formula (which then makes finding the answer instantaneous).

    If you can show us the whole routine (including the declarations of the variables, and the calculation itself), we can tell you ways of making it faster, perhaps drastically so.

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2007
    Posts
    3

    Re: Goal Seek in VB

    Si

    thanks for your reply, I don't think the calculation can be simplified. I could show the original spreadsheet which shows the calculation, if things can be attached on here.

    I was thinking of somethink like this to get around the solver problem, starts with a value of -100,000 does the calculation compares it to the target value it is to low so changes the solver value to -50,000 this time it is to high, changes it to -25000, now to low by doing this it narrows the range and then I can start from a much more realistic value eg -15,000 which would cut the calculation required by 10's of thousands, but i don't kno whow to code the above

    regards

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

    Re: Goal Seek in VB

    My thought was not to simplify the calculation - merely to improve the speed of your code, by optimising what it does, and how it does it. You might think that this couldn't make much difference, but in the past I have often found ways of making code like this hundreds of times faster.


    Your idea of "skipping values" is a good one (assuming the calculation can be treated that way), and is close to one of the potential solutions I was thinking of - a binary search.

    This works along the same lines as you are thinking of, but starts at the mid-point (in this case, 0), and sees what the outcome is - if too high "knock out" all higher values (and vice versa), and repeat the process for the remaining values (so next check at -50000).

    This method means that in the worst case you make half the checks you would have done, and in most cases saves much more.

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2007
    Posts
    3

    Re: Goal Seek in VB

    Si

    TargetValue = 1500
    arbval = -100000 ' this is intial value assumed as the goal seek value


    Do While TotFlo <= TargetValue
    Totflo = 0
    For i = 1 To 365
    SixFlow(i) = calculation performed using arbval and other variables
    Totflo = Totflo + SixFlow(i)
    Next i
    arbval = arbval + 0.001

    Loop

    The calculation that Sixflow(i) performs is

    minValue(TotalExp, maxValue(0, (TotalExp / (t10 - arbval) * (t10 - i))))

    t10 is calculated shortly before, as is TotalExp. MinValue and MaxValue are two functions that you supply two values to and it brings back the max or min.

    The binary search would work, but i cannot code it

    Regards

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,192

    Re: Goal Seek in VB

    You missed out the variable definitions, which are also important (eg: an Integer is noticeably faster than a Variant, and a Long is faster still). I have made guesses as to what the data type of each variable should be, but they may not be ideal.

    It would have helped to see the code for the functions, but your explanation is probably enough. Note that calling functions is very slow - so simply putting the code into the loop will make a significant speed improvement.

    As I suspected, you are doing work inside the For loop that does not need to be done each time (eg: TotalExp / (t10 - arbval)), as it could be done outside that loop, and stored in a variable. This means that this calculation is done much less often - only once for every 365 times it was done before.

    Similar can be done with (t10 - i) , by calculating all 365 values before the Do, and storing these values to an array. As an array-read needs to be done each time the gain each time is lower, but due to the number of times this calculation could be done (365 * (1/0.001) * (200000) = 73,000,000,000 !) the improvement it brings will almost certainly be noticeable!

    Here's the code with those changes, see what kind of speed improvement you get:
    Code:
    Dim TargetValue As Long, i As Long
    Dim arbval As Double, Totflo As Double
    Dim t10 As Double, TotalExp As Double
    Dim t10_arbval As Double, t10_i(365) As Double
    
    'this does not need to be an array, as it is only used temporarily
    'Dim SixFlow(365) As Double
    Dim SixFlow As Double
    
      TargetValue = 1500
      arbval = -100000 ' this is intial value assumed as the goal seek value
      t10 = ????
      TotalExp = ????
    
    
      'calculate all values of  (t10 - i)
      For i = 1 To 365
        t10_i(i) = (t10 - i)
      Next i
    
      Do
        'calculate value of  (t10 - arbval), since it will not change inside the For loop
        t10_arbval = (t10 - arbval)
        Totflo = 0
        For i = 1 To 365
          'find value of (TotalExp / (t10 - arbval) * (t10 - i))
          SixFlow = TotalExp / (t10_arbval * t10_i(i))
          
          'get maxValue( )
          If 0 > SixFlow Then SixFlow = 0
    
          'get minValue( ), and add it to our total (combining it like this is quicker)
          If TotalExp < SixFlow Then
            Totflo = Totflo + TotalExp
          Else
            Totflo = Totflo + SixFlow
          End If
        Next i
        arbval = arbval + 0.001
      Loop While Totflo <= TargetValue
    I'm not sure if the binary search is appropriate for your calculation.. to work it out (or determine better options) it would be best for us to know what kind of values you are expecting for t10 and TotalExp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.