Running a Macro at every Solver Iteration

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sk - 25 Jan 2007 16:58 GMT

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

Reply to this Message

Dana DeLouis - 25 Jan 2007 20:55 GMT

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.

Signature

HTH :>)
Dana DeLouis
Windows XP & Office 2003

> 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
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

sk - 25 Jan 2007 22:45 GMT

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. It works fine in Excel 2003.
> However, the documentation is a bit flakey.
[quoted text clipped - 13 lines]
>
> > Thanks- Hide quoted text -- Show quoted text -

Reply to this Message

Dana DeLouis - 25 Jan 2007 23:08 GMT

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...

Signature

HTH :>)
Dana DeLouis
Windows XP & Office 2003

> 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
[quoted text clipped - 42 lines]
>>
>> > Thanks- Hide quoted text -- Show quoted text -

Reply to this Message

sk - 26 Jan 2007 00:46 GMT

Thanks, this is great
----------

> Hi. In general, here is how you do it.
> I've included some personal techniques.
[quoted text clipped - 73 lines]
>
> >> > Thanks- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

Reply to this Message