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.

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

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"Andrew Harris" <Andrew wrote in message
...
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.

Hello Andrew. Really hoping you found an answer to your question and help me since I'm having the exact same issue. I have a macro written in VBA (called “BatchRun”) that needs to be run at every iteration that the Excel Solver goes through. I’ve tried using the ShowRef function in my VBA code, but can’t seem to get this work. Any advice would be HUGELY appreciated. The code I’m using to run the Solver via VBA is as follows.

Sub OptimalTriggerPercentageSolver()

' Clear any previous Solver settings
SolverReset

'Set solver options to enable stepthru so that macro can run at each iteration
SolverOptions StepThru:=True

' Set up the parameters for the model.
' Set the target cell L8 to maximum value by changing cell F3 and running the BatchRun Macro.

SolverOK SetCell:=Range("L8"), MaxMinVal:=1, _
ByChange:=Range("F3:F4")

' Solve the model but do not display the Solver Results dialog box.
SolverSolve UserFinish:=True, Showref:="BatchRun"

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub


Thank you!!!!!

Alex