
May 26th, 2007, 02:43 PM
#1
Thread Starter
New Member
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

May 26th, 2007, 03:12 PM
#2
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 rearrange 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.

May 26th, 2007, 03:28 PM
#3
Thread Starter
New Member
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

May 26th, 2007, 03:44 PM
#4
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 midpoint (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.

May 26th, 2007, 04:31 PM
#5
Thread Starter
New Member
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

May 26th, 2007, 05:35 PM
#6
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 arrayread 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

Forum Rules

Click Here to Expand Forum to Full Width
Survey posted by VBForums.
