Lab 8 Solver In VBA.ppt

Oct. 27-30, 2003 Lab 8 Solver In VBA ? Review – Solver Add-in In Excel ? Solver Add-in In VBA Review: Using Solver in Excel Describe the optimization model on an Excel worksheet Open the Solver dialog box in Excel (Tools/Solver) Specify the major items in the Solver dialog box Set Target Cell: the cell that contains the formula for the objective function Equal To: the type of the objective function By Changing Cells: the cells set to contain the values of the decision variables Subject to the Constraints: the adding of the constraints Options: Assume Linear Model Assume Non-Negative Reset All: clears all previous settings Run the Solver Using Solver with VBA Code Describe the model on an Excel worksheet Write code using Solver functions to specify the model. The major Solver functions include: SolverReset SolverOk SolverAdd SolverOptions SolverSolve Run the code Solver Functions in VBA Frontline Systems has written several VBA functions that allow developers to operate Solver “behind the scenes” with code These functions enable you to specify the model (target cell, changing cells, and constraints) and set options, etc. All the Solver functions begin with the word “Solver” Check the Web site /mlvbaref.htm for detailed help on all Solver functions Setting a Reference To use the Solver functions in an application, you need to set a Reference to the Solver Add-in in VBE. Set the reference with the Tools/References menu item in VBE. In a long list of possible libraries of code, check “Solver.xla” or “Solver” The reference will then appear in the Project Explorer window. SolverReset Function To reset the Solver, use this line: SolverReset It clears all previous settings. SolverOK Function It does three things: Identifies the target cell (Objective function) Specifies whether it is a maximization or minimization problem Identifies the changing cells Syntax (it takes three arguments): SolverOK SetCell:= , MaxMinVal:=, ByChange:=