http://blackboard.umbc.edu lab 8 solver in vba ► review – solver add-in in excel ► solver...

http://blackboard.umbc.edu

Lab 8 Solver In VBALab 8 Solver In VBA

► ► Review – Solver Add-in In Excel Review – Solver Add-in In Excel

►► Solver Add-in In VBA Solver Add-in In VBA


Review: Using Solver in ExcelReview: 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 CodeUsing 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 VBASolver 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


Setting a ReferenceSetting 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 FunctionSolverReset Function

• To reset the Solver, use this line:

SolverReset

• It clears all previous settings.


SolverOK FunctionSolverOK 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”), MaxMinVal:=1, _ ByChange:=Range(“C9:D9”)


SolverAdd Function (1)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 <= 2 = 3 >= 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.


SolverAdd Function (2)SolverAdd Function (2)

• Example:

SolverAdd CellRef:=Range(“D14”), Relation:=1, _ FormulaText:=“F14”




• Or you can add several constraints together if they are of the same relations:

SolverAdd CellRef:=Range(“D14:D15”), Relation:=1, _ FormulaText:=“F14:F15”


SolverOptions FunctionSolverOptions 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 AssumeLinear:=True, AssumeNonNeg:=True


SolverSolve FunctionSolverSolve 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 Solver’s 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 UserFinish:=True

Example of using the returned value:If SolverSolve(UserFinish:=True) = 5 Then _

MsgBox “There are no feasible solutions.”


Summary: Solving the Product Mix ExampleSummary: 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 SetCell:=Range("E12"), MaxMinVal:=1, _

ByChange:=Range("C9:D9")

SolverAdd CellRef:=Range("E15:E16"), Relation:=1, _

FormulaText:="G15:G16"

SolverOptions AssumeLinear:=True, _

AssumeNonNeg:=True If SolverSolve(UserFinish:=True) = 5 Then _

MsgBox "There are no feasible solutions. "End Sub


Add Buttons To Run VBA CodeAdd 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(“C9:D9”); 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!


Assignment 2 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: Don’t forget to add the Reference in VBE before running your Solver code)


NoteNote

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

http://blackboard.umbc.edu lab 8 solver in vba ► review – solver add-in in excel ► solver...

Documents