Goal Seek in VB

[th081]

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

[si_the_geek]

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.

[th081]

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

[si_the_geek]

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.

[th081]

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

[si_the_geek]

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:

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.