Ch. 21 Scheduling Model

1
Ch. 21 - Scheduling Model
This application is a very basic Excel VBA based
DSS. The following slides help to explain the
structure of the application. Its important
that you thoroughly understand how this
application works. Most VBA projects will require
about this level of sophistication just to make
your DSS work and do something non-trivial.
First, lets figure out how the model works.
2
Simplification through design time development

• Model developed manually
• Report sheet designed manually
• Graph designed manually
• VBA used to modify values in sheets, run the
  model, and put the results in the report and
  graph ranges

3
General app dev guidelines (Ch18)

• Decide clear purpose for the application
• What will it do?
• Let the user know what it will do
• Lots of comments in your spreadsheet and your VBA
  code
• Meaningful variable names in your VBA and
  meaningful range name in Excel
• Think modular
• Many short subs and functions avoid the
  monolithic code module
• Look to borrow and reuse code
• Break application into manageable chunks that can
  be developed almost independently
• Think about how your application will obtain data
• User forms, spreadsheet cells manually filled
  out, external files, websites, spreadsheet
  controls
• Maximize design time development and minimize
  run time development
• Create as much of the model, charts, etc. as you
  can manually
• Use VBA as needed to automate and move data
• Think about how you will present the output
• Spreadsheet tables, charts, pivot tables/charts,
  reports
• Add finishing touches
• Navigation buttons, hide/unhide sheets, customize
  the Excel environment, enhanced error checking
  and handling, Help files

4
More Excel Application Development Tips

• Strive for data input integrity
• Data Validation
• Worksheet Controls
• Forms and VBA
• Worksheet protection
• Comments, Help
• Start simple, add complexity as needed
• Think about distribution method
• XLS, XLA, XLT

5
OptionsForm
InputsForm
input values
1.1 Get user options
1.2 Get daily requirements and other params from
user
input range
Choice
Show
model parameters
1.0 Inputs-Optimize
1.3 Solve optimization problem
Show
Solve
solution values
1.4 Create report
model
solution range
Please review these slides as well as Ch 21 in
VBA book. If you can understand how this app
works, youll be well on your way to creating
nice Excel based DSS applications.
solution and other report values
report values
6
Main Program
1Inputs-Optimize
2Sensitivity
Use Show method
1
Subroutines that weve written
2
7
1.1 Get User Options
OptionsForm
Code in OnClick event sets value of Choice
variable depending on which option button chosen
Click Cancel
Click OK
When form is loaded
8
1.2 Get User Inputs1.2.1 Event Form Load
1. Dim a control object variable
2. Loop over controls on form
3. Is it a textbox?
4. Use Name property to figure out which textbox
it is
5. Fill text boxes with appropriate values from
the worksheet
2. End loop
9
1.2 Get User Inputs1.2.2 Event Click OK
1. Dim a control object variable
2. Loop over controls on form
3. Is it a textbox?
4. If user enters invalid data, display message
and end this subroutine. WHAT WILL USER SEE THEN?
4. Do a bunch of validity checking of the values
entered by the user. MAKE SURE YOU UNDERSTAND THE
IF/ELSEIF STATEMENTS
Subroutine continued on next slide
10
1.2 Get User Inputs1.2.2 Event Click OK
(cont.)
5. What must be true if we make it to this part
of the sub
6. Depending on which textbox control it is,
store the value entered by the user into the
appropriate range in the worksheet.
2. End loop over form controls
7. All done looping over form controls so Unload
the UserInputs form. WHERE IS CONTROL RETURNED TO
IN OUR APPLICATION?
11
1.2 Get User Inputs1.2.3 Event Click Cancel
1. Unload the UserInputs form. WHERE IS CONTROL
RETURNED TO IN OUR APPLICATION?
Many uses of the End keyword in VB
12
Many ways for information to be passed between
objects and code

• Function()
• can return a single value to a variable
• Example from InputForm code for OnClick of OK

• Set value of a Public variable

13
Many ways for information to be passed between
objects and code

• Put/get values to/from ranges in worksheet
• like putting multiple values in a database
• Example from InputForm code for OnClick of OK

Ranges get values from the values of the related
controls
14
Subroutine RunSolverSolving a pre-built model
SolverSolve command pushes the Run button in
the Solver dialog box. The userfinish parameter
pushes the OK button on the dialog box that pop
up when Solver terminates.
Ch 17 covers more advanced use of Solver with VBA
15
Creating a simple report
Report design is pre-built. We are just moving
results from the Model sheet to the Report sheet.
16
2.1 Solve series of optimization problems for
different values of MaxPct (0, 0.1, 0.2, ..., 1.0
2.0 Sensitivity Analysis
Go
solution values
sensitivity value range
2.2 Display graph
solution values
17
Sensitivity() Subroutine
Use For..Next loop to set MaxPct 0.0, 0.1, 0.2,
...1.0 and solve model each time.
Update value of MaxPct
Clear out current solution and resolve the problem
Store the results in predefined ranges
18
Invoking Solver in VBAChapter 17

• Assumes
• basic familiarity with Solver
• Can set all Solver settings via VBA
• the controls in the Solver dialog boxes
• Can build or modify models using VBA
• Can run Solver and capture results

19
Setting Reference to solver.xla
To call Solver VBA functions, you must set a
Reference to the solver.xla add-in (p338)
Make sure this is checked. If solver.xla does not
appear in the list, press the Browse button and
go find it.
20
Model Building via VBA
SolverOk SetCellRange(TotalCost),
MaxMinVal2, ByChangeRange(Assignments)
(1) (2) (3)
(1) SolverAdd CellRefRange(Assignments),
Relation4
(2) SolverAdd CellRefRange(Available),
Relation3, FormulaTextRequired
(3) SolverAdd CellRefRange(Nonconsec),
Relation1, FormulaTextMaxNonconsec
21
Figuring out if Solver succeeded

• After Solver finishes, we want to know the status
• Result SolverSolve(UserFinishTrue)
• Ive downloaded the Premium Solver documentation
  from the Frontline Systems web site. It describes
  how to program the Solver using VBA.

supp_pspguide55.pdf