C K Anderson
SHA 6050 – Solver and VBA Lab In this lab you will be using macros to run Excel Solver. You will also use some basic tools in Visual Basic. Open 605_LP_Solver_Lab.xls. You will find a typical linear programming model. The usual template to input into Solver has already been set up. You will create a macro to record the actions that you perform to run Solver so that the next time that you want to run Solver, all you’ll have to do is click a button. CLASSIC RM Setup Open the ‘RM’ sheet—here’s what you’ll see.
Date 10/3 10/4 10/5 10/6 10/7 10/8 10/9 10/10 10/11 10/12 10/13 10/14 10/15 10/16
1
2
99
99
Date 99-1 10/3 10/4 10/5 10/6 10/7 10/8 10/9 10/10 10/11 10/12 10/13 10/14 10/15 10/16
99-2
SOLVER and VBA
Part A: Decision Variables 3 1 2 3 99
129
129
129
Hotel Capacity: 1
2
3
159
159
159
Part E : E xpected Pickup 99-3 129-1 129-2 129-3 159-1 159-2 159-3
Part B: R ooms Sold
200
Part C: R ooms Daily Available R evenue
Part D: Total R evenue
1
C K Anderson
Step 1: Enter the formulas: Part A: Decision Variables: No formulas, this is what you will solve for. Part B: Rooms Sold: The formula accounts for the number of arrivals AND the number of stayovers. Go to Cell K4 and enter the formula: =SUM(B4:J4) Go to Cell K5 and enter the formula: =SUM(B5:J5,C4:D4,F4:G4,I4:J4) Go to Cell K6 and enter the formula: =SUM(B6:J6,C5:D5,F5:G5,I5:J5,D4,G4,J4) Fill down. Part C: Rooms Available: To calculate the number of rooms available, we must subtract the number of reservations on hand from the number of rooms in the hotel. Assume our hotel has 200 rooms. On ‘RM’ sheet, Cell L4, enter the formula:
=MAX(0,$M$1-SUM(ROH!D4:L4)) Since we must account for stayovers, the formulas for the remaining days will be slightly different. Go to Cell L5 and enter the formula: =MAX(0,$M$1-SUM(ROH!D5:L5,ROH!E4:F4,ROH!H4:I4,ROH!K4:L4)) Finally, go to Cell L6 and enter the formula: =MAX(0,$M$1-SUM(ROH!D6:L6,ROH!E5:F5,ROH!H5:I5,ROH!K5:L5,ROH!F4,ROH!I4,ROH!L4))
Fill down.
SOLVER and VBA
2
C K Anderson Part D: Total Revenue: To calculate the Total Revenue, you must first calculate the Daily Revenue. Go to Cell M4 and enter the formula: =SUMPRODUCT($B$2:$J$2,$B$3:$J$3,B4:J4) Fill down. To calculate the Total Revenue, you must sum the Daily Revenues. Go to cell M18 and enter the formula: =SUM(M4:M17) Part E: Expected Pickup: These numbers are the same as the numbers that are in the ‘ROH” sheet. Go to Cell B21 of the ‘Ours’ sheet and enter the formula: =MAX(0,ROH!M4) Fill down and right
Step 2: Set up the Solver problem. Maximize: $M$18 By Changing Cells: $B$4:$J$17 Subject to: •
Demand constraints (can’t sell more than demanded) $B$4:$J$17<=$B$21:$J$34
•
Capacity constraints (can’t sell more rooms than available). $K$4:$K$17<=$L$4:$L$17
Step 3: Options: “Assume Linear Model” and “Assume Non-Negative” Step 4: Solve NOW Let’s use VBA to build and control our MODEL 1.
Record a new macro
SOLVER and VBA
3
C K Anderson
2. 3.
4. 5. 6. 7. 8.
• Developers-Macro-Record New Type in a name for the macro, "solver1” and hit the enter key. Set up the Solver as you normally would (Excel is recording every move you make so don't do anything extra). • Data-Solver • select “Reset All” in Solver before setting up the model. • Build the Solver model again as above Solve Generate Sensitivity Report Copy and Paste the Shadow Prices to the Restrictions tab Delete the Sensitivity Report sheet. • Right Click the Sheet Tab - Delete – Delete Stop recording the macro. • Click on the square box in the floating macro box OR • Go to Developer-Macro-Stop Recording Macro
Now every time you run the macro, it will automatically update your shadow prices. Then you can link rate and availability restrictions to these cells so that they can be updated automatically at the same time. Return to the Visual Basic Editor to see how your code has changed. There are some additional changes that you will have to make to the code for true automation of the task in the future. These are as follows: Every time a sensitivity report is created, the number assigned to the report is incremented by one. For example, the next time you run the macro, the sensitivity report will be called "Sensitivity Report 2" instead of "Sensitivity Report 1" and the macro will give you an error message since it cannot find "Sensitivity Report 1". To avoid this problem, make the following change to the code: Replace Sheets("Sensitivity Report 1").Select with Sheets(1).Select NOTE: This change needs to be made at 2 places in the macro. The number in the parentheses indicates the order of the sheets (i.e. 1 means the first sheet). Since the sensitivity report is always inserted immediately before the current worksheet, it will be always the first sheet. Therefore, Sheet(1) will always refer to the sensitivity report. The macro does not automatically record the selection of the sensitivity report. To select the sensitivity report, replace "SolverSolve" with the following: • SolverSolve userFinish:=True • SolverFinish KeepFinal:=1, ReportArray := Array(2) To avoid the alert dialog box when deleting the sensitivity report, add the following directly above “ActiveWindow.SelectedSheets.Delete”:
SOLVER and VBA
4
C K Anderson •
Application.DisplayAlerts = False
Create a Button to Run the Macro You can use a button to run the macro rather than going to the Tools menu and choosing Run Macro. In order to do this: 1. 2. 3. 4.
Click on the Button icon on the Forms toolbar and place a button anywhere on the worksheet. The Assign Macro dialog box will appear. Choose “solver1” and click OK. Rename the button as “solver1” then click somewhere outside of it to deselect it. Click the button at any time and Solver will run just as you recorded it in the macro.
NOTE: IF YOUR CODE WILL NOT RUN, IN THE VB EDITOR (ALT-F11) UNDER TOOLS, REFERENCES, make sure that SOLVER is selected from the list. Again VBA is fun, please come see me if you have troubles, don’t spend too much time banging your head against the wall. JUST IN CASE: THE CODE Sub SOLVER_RUN() ' ' SOLVER_RUN Macro ' THIS MACRO RUNS OUR SOLVER MODEL ' SolverReset SolverOk SetCell:="$M$18", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$4:$J$17" SolverAdd CellRef:="$B$4:$J$17", Relation:=1, FormulaText:="$B$21:$J$34" SolverAdd CellRef:="$K$4:$K$17", Relation:=1, FormulaText:="$L$4:$L$17" SolverOk SetCell:="$M$18", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$4:$J$17" SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _ :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True SolverOk SetCell:="$M$18", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$4:$J$17" SolverSolve userfinish:=True SolverFinish KeepFinal:=1, ReportArray:=Array(2)
SOLVER and VBA
5
C K Anderson Sheets("Sensitivity Report 1").Select Range("E139:E152").Select Selection.Copy Sheets("Restrictions").Select Range("B3").Select ActiveSheet.Paste Sheets("Sensitivity Report 1").Select Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True End Sub
SOLVER and VBA
6
