Running a Macro at every Solver Iteration

  • Thread starter Thread starter sk
  • Start date Start date
S

sk

I am trying to run a macro at every solver iteration through showref.
However, the solver goes through only the first iteration and does not
optimize the target cell updated by the macro called through showref.

I found an old thread from a couple of years ago on the same issue.
Just wondering if folks were able to resolve this.

Thanks
 
Hi. It works fine in Excel 2003.
However, the documentation is a bit flakey.
If you post your code, we may be able to help.
 
Here is the macro that I wrote to test the solver. As I mentioned, I
can get to the macro from the solver in the first iteration but not
back for further iterations. Do I need to force a return?

Thanks
Sharad

Sub Macro1()
'

SolverOk SetCell:="$B$5", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$2"
SolverAdd CellRef:="$B$1", Relation:=3, FormulaText:="25"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=False, StepThru:=True, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=False

ans = SolverSolve(False, "Example()")

End Sub

Function Example()
Sheets("sheet1").Cells(1, "B") = -10 * Sheets("sheet1").Cells(2, "B") +
1000
End Function
-------------------
 
Hi. In general, here is how you do it.
I've included some personal techniques.

SolverOk SetCell:="B5", MaxMinVal:=1, ByChange:="B2"
SolverOptions StepThru:=True
ans = SolverSolve(True, "Example")

- - -
You can drop =0 in a Max problem as it's ignored anyway.
I like to set StepThru on a separate line for these problems.
Note the correct call format
- - -

Here's one way for your Example function to work.

Function Example(WhyCalled As Integer)
'// WhyCalled is 1,2, or 3
'// Not necessary here...

With Sheets("Sheet1")
.Cells(1, 2) = -10 * .Cells(2, 2) + 1000
End With
Example = False ' <- Excel 2003
End Function

Good luck...
 
Thanks, this is great
----------

Hi. In general, here is how you do it.
I've included some personal techniques.

SolverOk SetCell:="B5", MaxMinVal:=1, ByChange:="B2"
SolverOptions StepThru:=True
ans = SolverSolve(True, "Example")

- - -
You can drop =0 in a Max problem as it's ignored anyway.
I like to set StepThru on a separate line for these problems.
Note the correct call format
- - -

Here's one way for your Example function to work.

Function Example(WhyCalled As Integer)
'// WhyCalled is 1,2, or 3
'// Not necessary here...

With Sheets("Sheet1")
.Cells(1, 2) = -10 * .Cells(2, 2) + 1000
End With
Example = False ' <- Excel 2003
End Function

Good luck...
Click to expand...
 
Back
Top