Running a Macro at every Solver Iteration
 

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

Running a Macro at every Solver Iteration
 

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.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"sk" wrote in message
ups.com...

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

Running a Macro at every Solver Iteration
 

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
-------------------
On Jan 25, 12:55 pm, "Dana DeLouis" wrote:
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.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003

"sk" wrote in oglegroups.com...





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- Hide quoted text -- Show quoted text -

Running a Macro at every Solver Iteration
 

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...
--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"sk" wrote in message
ups.com...
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
-------------------
On Jan 25, 12:55 pm, "Dana DeLouis" wrote:
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.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003

"sk" wrote in
oglegroups.com...





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- Hide quoted text -- Show quoted text -

Running a Macro at every Solver Iteration
 

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

On Jan 25, 3:08 pm, "Dana DeLouis" wrote:
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...
--
HTH :)
Dana DeLouis
Windows XP & Office 2003

"sk" wrote in oglegroups.com...



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
-------------------
On Jan 25, 12:55 pm, "Dana DeLouis" wrote:
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.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003

"sk" wrote in
oglegroups.com...

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- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -