Solver: 2nd function of SolverSolve

Hello. New guy here so please be gentle.

From searching the internet, I've learned that the 2nd function of SolverSolve can be called to count the number of iterations performed by Solver, ie:

SolverSolve (1st_function, 2nd_function).


Here's the code I found, however I can not make it to run. Where do I put the code in the VBA program? I mean what would be the complete SolverSolve command?

Please Login or Register  to view this content.

Thanks in advance!

Hi. Place your code in a regular vba module. Then go to Tool | References
| and select Solver.
Note that there are documentation errors with this method. I can't find my
notes right now.
I think this method was messed up with a previous version of Excel.
However, it seems fixed with Excel XP & 2003.
I believe the correct method to end your ShowTrial function is with:

ShowTrial = False

The following is a very general outline of a solver macro. Hopefully, this
will give you some ideas for your own code.

SolverReset ' Clear everything.
' Set it up
SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="130",
ByChange:="$C$4:$C$6"
' Add constraints...
SolverAdd CellRef:="$C$4:$C$6", Relation:=1, FormulaText:="$E$4:$E$6"
SolverAdd CellRef:="$C$4:$C$6", Relation:=3, FormulaText:="$D$4:$D$6"

' Previous documentation had errors here also...
' Set to True for ShowTrial
SolverOptions StepThru:=True

Results = SolverSolve(True, "ShowTrial")

' 0 Solver found a solution.
If Results = 0 Then SolverFinish True ' Or 1 or 2.

HTH. Good Luck. :>)
--
Dana DeLouis
Win XP & Office 2003


"zaina" <zaina.1qngec_1118808333.7351@excelforum-nospam.com> wrote in
message news:zaina.1qngec_1118808333.7351@excelforum-nospam.com...
>
> Hello. New guy here so please be gentle.
>
> From searching the internet, I've learned that the 2nd function of
> SolverSolve can be called to count the number of iterations performed
> by Solver, ie:
>
> SolverSolve (1st_function, 2nd_function).
>
>
> Here's the code I found, however I can not make it to run. Where do I
> put the code in the VBA program? I mean what would be the complete
> SolverSolve command?
>
>
> Code:
> --------------------
> Global count as integer
>
> Sub test()
> count = 0
> ret = solveroptions(stepthru:=True) '"checks" the Show Iteration checkbox
> in Tools/Solver/Options
> ret = solversolve(True, "showtrial")
> MsgBox count
> End Sub
>
> Function showtrial(reason As Integer)
> If reason = 1 Then 'reason = 1 means that the function will be called on
> each iteration.
> count = count + 1 'increments global variable
> End If
> showtrial = 1 ' continues Solver
> End Function
> --------------------
>
>
> Thanks in advance!
>
>
> --
> zaina
> ------------------------------------------------------------------------
> zaina's Profile:
> http://www.excelforum.com/member.php...o&userid=24316
> View this thread: http://www.excelforum.com/showthread...hreadid=379230
>

Hi. Your code is almost correct. Your SolverAdd & SolverOk lines have
"extra" stuff that are not part of the syntax. However, your Test()
subroutine never calls SolveProblem(), so that is why Solver never got
reset, and probably worked a little from previous attempts. It looks like
you want to generate a report also based on Report:=True.

This method did not work very well in previous versions of Excel. It just
didn't work, and had a few other bugs as well. In addition, the
documentation had a few errors as well. Most of the big issues are fixed in
the latest version of Excel, however, I see that most of the documentation
has disappeared. I want to point you to some articles, but they no longer
exists.

Anyway, here's the difference between the variables. Result is a variable
that holds the "result" of Solver. A return value of 0,1,or 2 means that
Solver found a solution. Count holds the number of times that Solver called
your macro.

> If reason = 1 Then 'reason = 1 means that the function will be
> called on each iteration.

Actually, the above statement is not totally correct. However, I can no
longer find any documentation on this feature.
Anyway, if your macro is running correctly, your function is going to be
called anyway. The variable that we are passing to our function (we are
using the variable 'Reason') holds a number that indicates "Why" we are
calling this function. A value of 1 means that Solver called this function
instead of showing the iteration message box. We would see this box
normally when we select the solver option "Show iteration results."
This function is not called on each iteration, but is called after an small,
unknown number of iterations. The other two reasons the function is called
is listed below in the code example.
I included a small example code below to give you some ideas. Run this code
("Demo") with a blank active worksheet. The code will set it up for you.
You should see about 5 iteration results on the sheet after it runs.
Write back if you have any questions. I included one test for an
undocumented bug. I've never been able to figure out why Solver's code
breaks the workbook name if there's a space in the name.

Option Explicit
Public Count As Long ' Counter

Sub Demo()
Dim Results
Count = 1
[A2:A4] = 1
[D:F].Clear

'// Spaces in the workbook name is a major undocumented bug with this
method:
If InStr(1, ThisWorkbook.Name, Space(1)) > 0 Or _
InStr(1, ActiveWorkbook.Name, Space(1)) > 0 Then
MsgBox "IMPORTANT: Remove all Spaces from workbook names",
vbCritical
End
End If

'// Use a random Polynomial equation:
[A6].Formula = "=A2+ 2 * (A3)+ 3 * (A4^ 2) + 10"

SolverReset
SolverOk SetCell:="A6", MaxMinVal:=3, ValueOf:="310", ByChange:="A2:A4"

SolverAdd CellRef:="A2:A4", Relation:=3, FormulaText:=4 'A2:A4 >= 4
SolverAdd CellRef:="A2:A4", Relation:=1, FormulaText:=10 'A2:A4 <= 10

SolverOptions StepThru:=True

Results = SolverSolve(True, "ShowTrial")

Select Case Results
Case 0, 1, 2
' Keep final values & generate answer report
Cells(Count, 4) = Count
Cells(Count, 6) = Range("A6")
SolverFinish KeepFinal:=1, ReportArray:=Array(1)
Case 4
'Target does not converge
'Perhaps stop, or give different starting values and do again
Case 5
'Solver could not find a feasible solution
'Your code here
Case Else
'Your code
End Select
End Sub

Function ShowTrial(Reason As Integer)

'// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Why was this function called while Solver was running?
'// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Const xContinue As Boolean = False 'Excel XP
Const xStopRunning As Boolean = True 'Excel XP
'
Select Case Reason
Case 1
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' ' The Show Iteration Results box in the Solver Options dialog is
checked, '
' ' OR called because the user pressed ESC to interrupt the Solver.
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Cells(Count, 4) = Count
Cells(Count, 5) = Reason
Cells(Count, 6) = Range("A6")
Count = Count + 1
ShowTrial = xContinue

Case 2
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' ' The Max Time option in the Solver Options dialog was exceeded.
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =

'// Is answer close enough, or do we want to keep going?
'// We'll quit for now...
ShowTrial = xStopRunning

Case 3
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' 'The Max Iterations option in the Solver Options dialog was exceeded
''// = = = = = = = = = = = = = = = = = = = = = = = = = = = =
ShowTrial = xStopRunning

End Select
End Function






--
Dana DeLouis
Win XP & Office 2003


"zaina" <zaina.1qogih_1118855156.606@excelforum-nospam.com> wrote in message
news:zaina.1qogih_1118855156.606@excelforum-nospam.com...
>
> Hi Dana: Thanks for your quick respond.
>
> Sorry, I still don't get it. Can you please elaborate a little more. I
> have Excel 2003 and added the codes in Module1.
>
> Below is the edited code. Can you tell if my codes is correct or not?
> And which variable that shows the number of iterations? Is it "count"
> or "Result"?
>
> I tried to run and it gave me error, plus the MsgBox = 0 although the
> variable in the spreadsheet changes so I think there are iterations
> take place. Thanks.
>
> Global count As Integer
> -------
> Sub test()
>
> count = 0
> SolverOptions StepThru:=True
> Results = SolverSolve(True, "ShowTrial")
> If Results = 0 Then SolverFinish True ' Or 1 or 2.
> MsgBox count
>
> End Sub
> --------
> Function ShowTrial(reason As Integer)
>
> If reason = 1 Then 'reason = 1 means that the function will be
> called on each iteration.
> count = count + 1 'increments global variable
> End If
> ShowTrial = False ' continues Solver
>
> End Function
> --------
> Sub SolveProblem()
>
> SolverReset
> SolverAdd cellRef:="$B$6", relation:=3, formulaText:="$B$4",
> Comment:="", _
> Report:=True
> SolverOk SetCell:="$B$4", MaxMinVal:=1, ValueOf:=0,
> ByChange:="$E$9:$E$13", _
> Engine:=1, EngineDesc:="Standard GRG Nonlinear"
> test 'is this right?
>
> End Sub
> --------
>
>
> Dana DeLouis Wrote:
>> Hi. Place your code in a regular vba module. Then go to Tool |
>> References
>> | and select Solver.
>> Note that there are documentation errors with this method. I can't
>> find my
>> notes right now.
>> I think this method was messed up with a previous version of Excel.
>> However, it seems fixed with Excel XP & 2003.
>> I believe the correct method to end your ShowTrial function is with:
>>
>> ShowTrial = False
>>
>> The following is a very general outline of a solver macro. Hopefully,
>> this
>> will give you some ideas for your own code.
>>
>> SolverReset ' Clear everything.
>> ' Set it up
>> SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="130",
>> ByChange:="$C$4:$C$6"
>> ' Add constraints...
>> SolverAdd CellRef:="$C$4:$C$6", Relation:=1, FormulaText:="$E$4:$E$6"
>> SolverAdd CellRef:="$C$4:$C$6", Relation:=3, FormulaText:="$D$4:$D$6"
>>
>> ' Previous documentation had errors here also...
>> ' Set to True for ShowTrial
>> SolverOptions StepThru:=True
>>
>> Results = SolverSolve(True, "ShowTrial")
>>
>> ' 0 Solver found a solution.
>> If Results = 0 Then SolverFinish True ' Or 1 or 2.
>>
>> HTH. Good Luck. :>)
>> --
>> Dana DeLouis
>> Win XP & Office 2003
>>
>>
>> >
>> > Hello. New guy here so please be gentle.
>> >
>> > From searching the internet, I've learned that the 2nd function of
>> > SolverSolve can be called to count the number of iterations
>> performed
>> > by Solver, ie:
>> >
>> > SolverSolve (1st_function, 2nd_function).
>> >
>> >
>> > Here's the code I found, however I can not make it to run. Where do
>> I
>> > put the code in the VBA program? I mean what would be the complete
>> > SolverSolve command?
>> >
>> >
>> > Code:
>> > --------------------
>> > Global count as integer
>> >
>> > Sub test()
>> > count = 0
>> > ret = solveroptions(stepthru:=True) '"checks" the Show Iteration
>> checkbox
>> > in Tools/Solver/Options
>> > ret = solversolve(True, "showtrial")
>> > MsgBox count
>> > End Sub
>> >
>> > Function showtrial(reason As Integer)
>> > If reason = 1 Then 'reason = 1 means that the function will be
>> called on
>> > each iteration.
>> > count = count + 1 'increments global variable
>> > End If
>> > showtrial = 1 ' continues Solver
>> > End Function
>> > --------------------
>> >
>> >
>> > Thanks in advance!
>> >
>>
>
>
> --
> zaina
> ------------------------------------------------------------------------
> zaina's Profile:
> http://www.excelforum.com/member.php...o&userid=24316
> View this thread: http://www.excelforum.com/showthread...hreadid=379230
>