Lab 8 Solver In VBA

1
Lab 8 Solver In VBA

• ? Review Solver Add-in In Excel
• ? Solver Add-in In VBA

2
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

3
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

4
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 www.frontsys.com/mlvbaref.htm
• for detailed help on all Solver functions

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

6
SolverReset Function

• To reset the Solver, use this line
  SolverReset
• It clears all previous settings.

7
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
• If it is a maximization problem MaxMinVal 1
  minimization problem MaxMinVal 2
• Example
• SolverOK SetCell Range(E12), MaxMinVal1, _
  ByChangeRange(C9D9)

8
SolverAdd Function (1)

• This function adds a new constraint each time it
  is called.
• Syntax
• SolverAdd CellRef, Relation, FormulaText
• CellRef is a reference to a cell or a range of
  cells on the active worksheet that forms the left
  hand side of the constraint.
• Relation specifies the arithmetic relationship
  between the left and right sides of the
  constraint. More specifically it has the
  following values
• Relation Relationship
• 1 lt
• 2
• 3 gt
• 4 the left hand side has integer
  value(s)
• 5 the left hand side has binary
  value(s)
• (If the Relation equals to 4 or 5, the
  constraint has no right hand side.)
• FormulaText is the right hand side of the
  constraint, and it can be a number or a reference
  to a range of cells.

9
SolverAdd Function (2)

• Example
• SolverAdd CellRefRange(D14), Relation1, _
  FormulaTextF14
• SolverAdd CellRefRange(D15), Relation1, _
  FormulaTextF15
• SolverAdd CellRefRange(D16), Relation3, _
  FormulaTextF16
• SolverAdd CellRefRange(D17), Relation3, _
  FormulaTextF17
• Or you can add several constraints together if
  they are of the same relationsSolverAdd
  CellRefRange(D14D15), Relation1, _
  FormulaTextF14F15

10
SolverOptions Function

• It allows you to set any of the options and is
  equivalent to click the Options button in the
  Solver dialog box in Excel.
• Syntax
• SolverOptions (list of options separated by
  commas)The two most frequently used options are
  the Assume Linear Model and Assume Non-Negative
  options.
• Example
• SolverOptions AssumeLinearTrue,
  AssumeNonNegTrue

11
SolverSolve Function

• It is equivalent to click the Solve button in the
  usual Solver dialog box to run the solver.
• Syntax SolverSolve UserFinish
• UserFinish If it is True, the solver results
  dialog box will not appear if it is False or
  omitted, Solver displays the standard Solver
  results dialog box, allowing the user to keep or
  discard the final solution values, and optionally
  produce reports.
• The SolverSolve function returns an integer value
  that indicates Solvers success. If this integer
  is 0 (or 1, 2), it means successful if it is 4,
  the solver did not converge if it is 5, there is
  not a feasible solution.
• Example
• SolverSolve UserFinishTrue
• Example of using the returned value
• If SolverSolve(UserFinishTrue) 5 Then _
• MsgBox There are no feasible solutions.

12
Summary Solving the Product Mix Example

• Putting the SolverReset, SolverOK, SolverAdd,
  SolverOptions, and SolverSolve together, we can
  write a sub to solve the product mix example
• Sub SaferlySolver()
• SolverReset
• SolverOK SetCellRange("E12"), MaxMinVal1,
  _
• ByChangeRange("C9D9")
• SolverAdd CellRefRange("E15E16"),
  Relation1, _
• FormulaText"G15G16"
• SolverOptions AssumeLinearTrue, _
• AssumeNonNegTrue
• If SolverSolve(UserFinishTrue) 5 Then
  _
• MsgBox "There are no feasible solutions. "
• End Sub

13
Add Buttons To Run VBA Code

• You can actually add form buttons on the Excel
  worksheet so that users can simply click the
  Buttons to run the VBA code
• Anywhere at the Excel toolbar, right click to
  bring up a list of toolbars available and select
  Forms tools
• Click on the fourth control Button
• On the Worksheet place and draw a button
• Assign the Solver Sub to this button
• Change the button label to Run Solver
• Write another short Sub in VBE to clear the
  results in Range(C9D9) assign this macro to a
  new button with button label Clear Results
• Now click on the two buttons to clear the results
  and run the solver in Excel!

14
Assignment 2

• Solve the Krazy Krakers spreadsheet model with
  VBA code.
• 1) Consider the set up of the Krazy Kracker
  problem from lab 2
• 2) Write code with VBA Solver functions to
  solve the Linear Programming (LP) model.
• 3) Within your codes, check to see whether
  there are feasible solutions. If there are not,
  display an appropriate message.
• 4) Add two Form buttons on the Worksheet to run
  the solver and clear the results.
• (Notes Dont forget to add the Reference in VBE
  before running your Solver code)

15
Note

• You are required to have both assignments for
  this lab stay in one excel file.
• Data and model of the two assignment should be
  put in two separate worksheets
• Create buttons for each assignment.