Using Solver with VBA
 

I'm using this code to run Solver with VBA. When I run it, there is no solution (or Solver Results dialog box). When I check the Solver under "Tools" and see that the constraints and MaxMinVal are set OK. But there are no entries for "Set Target Cell or "By Changing Cells". What have I done wrong?

I would check two things.

1. Use the cell addresses for SetCell and for ByChange, not range objects.
2. Do SolverOK just before SolverSolve

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

EggShell wrote:

I'm using this code to run Solver with VBA. When I run it, there is no
solution (or Solver Results dialog box). When I check the Solver under
"Tools" and see that the constraints and MaxMinVal are set OK. But
there are no entries for "Set Target Cell or "By Changing Cells". What
have I done wrong?

Code:
--------------------
Sub RunSolver()
'Prompt for month number
currMonth = Application.InputBox(Prompt:="Enter month number:", Type:=2)

'Clear previous Solver settings
SolverReset

' Solver Options...
Call SolverOptions(MaxTime:=100, Iterations:=200, Precision:=0.0001, AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True)


' The Solver will run one month at a time. Using the first month as
' the starting point, use intOffset to determine which month (column)
' to solve for

intOffset = currMonth - 1

' Set the target cell to a minimum value by changing cells C11:C22 or
' an offset of this range
SolverOk SetCell:=Range("Total_Cost"), MaxMinVal:=2, _
ByChange:=Range("Ship").Offset(0, intOffset)

' Add the constraint that Final Inventory = Capacity
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset), Relation:=1, _
FormulaText:=Range("Capacity").Offset(0, intOffset)

' Add the constraint that Final Inventory = Safety Stock
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset), Relation:=3, _
FormulaText:=Range("Safety_Stock").Offset(0, intOffset)

' Add the constraint that shipments to customer = customer demand.
SolverAdd CellRef:=Range("Net_Flow_Cust").Offset(0, intOffset), Relation:=2, _
FormulaText:=Range("Demand_Cust").Offset(0, intOffset)

' Add the constraint that shipments from PM = PM Production.
SolverAdd CellRef:=Range("Net_Flow_PM").Offset(0, intOffset), Relation:=2, _
FormulaText:=Range("Supply_PM").Offset(0, intOffset)

' Add the constraint that shipments from WHSE = WHSE Demand.
SolverAdd CellRef:=Range("Net_Flow_WHSE").Offset(0, intOffset), Relation:=3, _
FormulaText:=Range("Demand_WHSE").Offset(0, intOffset)

' Solve the model and keep the final results.
SolverSolve UserFinish:=False
'SolverFinish KeepFinal:=1

End Sub
--------------------

Hi. Don't have an answer, but I would be curious for any feedback if you
deleted the following line for debugging ...
Call SolverOptions(MaxTime:=100...

' Add the constraint that Final Inventory = Capacity
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset),
Relation:=1, ....

Note that if you really want "Equal", I believe that Relation should be 2,
and not 1.
Also, make sure your sheet is using A1 reference, and not R1C1 reference for
debugging.
--
Dana DeLouis
Win XP & Office 2003


"EggShell" wrote in message
...

I'm using this code to run Solver with VBA. When I run it, there is no
solution (or Solver Results dialog box). When I check the Solver under
"Tools" and see that the constraints and MaxMinVal are set OK. But
there are no entries for "Set Target Cell or "By Changing Cells". What
have I done wrong?

Code:
--------------------
Sub RunSolver()
'Prompt for month number
currMonth = Application.InputBox(Prompt:="Enter month number:", Type:=2)

'Clear previous Solver settings
SolverReset

' Solver Options...
Call SolverOptions(MaxTime:=100, Iterations:=200, Precision:=0.0001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True)


' The Solver will run one month at a time. Using the first month as
' the starting point, use intOffset to determine which month (column)
' to solve for

intOffset = currMonth - 1

' Set the target cell to a minimum value by changing cells C11:C22 or
' an offset of this range
SolverOk SetCell:=Range("Total_Cost"), MaxMinVal:=2, _
ByChange:=Range("Ship").Offset(0, intOffset)

' Add the constraint that Final Inventory = Capacity
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset),
Relation:=1, _
FormulaText:=Range("Capacity").Offset(0, intOffset)

' Add the constraint that Final Inventory = Safety Stock
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset),
Relation:=3, _
FormulaText:=Range("Safety_Stock").Offset(0, intOffset)

' Add the constraint that shipments to customer = customer demand.
SolverAdd CellRef:=Range("Net_Flow_Cust").Offset(0, intOffset),
Relation:=2, _
FormulaText:=Range("Demand_Cust").Offset(0, intOffset)

' Add the constraint that shipments from PM = PM Production.
SolverAdd CellRef:=Range("Net_Flow_PM").Offset(0, intOffset),
Relation:=2, _
FormulaText:=Range("Supply_PM").Offset(0, intOffset)

' Add the constraint that shipments from WHSE = WHSE Demand.
SolverAdd CellRef:=Range("Net_Flow_WHSE").Offset(0, intOffset),
Relation:=3, _
FormulaText:=Range("Demand_WHSE").Offset(0, intOffset)

' Solve the model and keep the final results.
SolverSolve UserFinish:=False
'SolverFinish KeepFinal:=1

End Sub
--------------------


--
EggShell