Having Solver Call a macro for each iteration

[Andrew Harris]

Hello,

The help file for Excel Solver states it is possible to have solver execute
a macro for each intermediate solution. My objective function cell is not a
cell containing a formula, but is updated by macro with about 400 lines of
code.

The relevant code is:

SolverOptions precision:=0.001, maxTime:=100, StepThru:=True 'Set
Solver Options
SolverOk SetCell:=Range("Current_Balance"), MaxMinVal:=1, ByChange:=Selection
SolverSolve UserFinish:=False, ShowRef:="Main"

According to the help file, I have StepThru:=True in the SolverOptions call,
and ShowRef:="Main" in the SolverSolve call. This should call the sub
procedure "Main" for each intermediate solution, but when I execute this
code, solver runs, but does not execute "Main"


I also found this bug/fix file on support.microsoft.com, but I do not
understand how to implement the fix:
http://support.microsoft.com/kb/151267/en-us

Any Help is greatly appreciated.

 

[Dana DeLouis]

...My objective function cell is not a

cell containing a formula, but is updated by macro..

Hi. When I run Solver with a 'Target' Cell that does not have a Formula, I
get the error message:
"Set Target Cell contents must be a formula."
Hence, I don't believe (afaik) that you can use Solver in this way.

and ShowRef:="Main" in the SolverSolve call. This should call the sub
procedure "Main" for each intermediate solution...

The following is not documented. In general, it is not a good idea to name
any subroutines associated with Solver "Main."
This causes errors for Solver, as Solver has an internal routine called
"Main" also.
Try changing the name.
Speaking of names... your intermediate routine will not run if your workbook
name has any spaces, or special characters. Sounds silly, but it's actually
true.

You also need to tell your "Main" routine if you wish to continue.
Documentation is actually backwards.
False to Continue, True to Stop. (One would think the other way around)