Marching Towards Excel Based DSS Application Development

1
Marching Towards Excel Based DSS Application
Development

• Goal develop spreadsheet based decision support
  applications
• Example Scheduling-Annotated.xls
• Premise VBA often very useful for this
• automation
• user interface
• some stuff just really hard to do with formulas
• VBA Session 1 Programming Fundamentals
• VBA Session 2 Excel Object Model
• VBA Session 3 User Forms and Application Design
• this is kind of fun and concepts transfer well to
  Access, other apps
• several ways to create useful user interfaces to
  spreadsheet models

2
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

3
Creating User Forms (Ch 11)Download and Open
PracticeForm-Code.xls (avail. from web)
This is the final product of what well create
today. When we push the Push Me button we get a
nice User Form to fill out. When were done, we
push OK and get this.
After trying this out, PLEASE CLOSE this XLS file
for now.
4
Creating a new User FormOpen PracticeForm-blank.x
ls (avail. from web)
(1) InsertUser Form
Blank form
Get to know all the controls
5
Each control has a slew of properties. The form
itself also has a bunch of properties. Select the
form to see them.
6
Get to this stage, see p208
This is a list box
7
Leszynski Naming Convention
tagBaseName

• Makes object names informative
• Standardized vocabulary for teams
• Improved ability to work with objects
• Sorting, self-documenting, find-replace

8
Names for Common ControlsfrmCustomer
9
Setting a few control properties
To set any control property, select the control
and then make your changes in the Properties
Window.

• Name property
• using the naming conventions from the last slide,
  lets set all the control Name properties
• naming them will make it easier to reference them
  from our VBA code later
• Caption property
• the Caption property will affect what is shown on
  or next to the control on the form
• Example set the Caption property to OK for the
  cmdOK command button control

10
Now, get it to here by setting Name and Caption
properties, see p210.
Lets put a RefEdit control here
Note how list box based on Customers range and
that its a single select type list box.
11
When OK button is pushed, we want...
Message box showing summary of what we chose on
the Form
Same summary values but placed into the
spreadsheet with VBA.
Lets look at the code...
12
So, now its time for Event Code

• Windows programs respond to Events, such as...?
• We get to write code to make our controls respond
  to user events and do whatever we want to have
  happen
• Please save and close your file and reopen
  PracticeForm-Code.xls
• Lets examine the code
• in class handout with annotated code Code Behind
  Forms Example.doc

13
Three subroutines behind the form

• UserForm_Initialize()
• this code executes whenever the form is opened
• Note that this sub is always called this
  regardless of the form name (go figure)
• cmdOK_Click()
• this code executes when user presses OK
• cmdCancel_Click()
• this code executes when user presses Cancel

14
Viewing code behind forms

• Select the form (frmInputs) in the Project
  Explorer window in the VBE.
• In the View menu, select Code
• To work with the object again (the form), go back
  to the View menu, select Object
• you can toggle back and forth

15
UserForm_Initialize()
All we are doing is setting the initial values
for the controls on the form that the user will
see when he/she opens the form.
If you ever want to dynamically fill a list box
based on the values in a Range on a worksheet,
heres how you do it.
16
cmdCancel_Click
Me is shorthand notation for the calling form.
In this case, MefrmInputs. By using Me instead
of the form name its faster, easier to reference
objects on the form and also makes it easier to
share code across multiple forms.
17
cmdOK_Click (handout has details)

• Capture the value of ProductBox, but make sure it
  is from 1 to 1000.
• from the text box control into an integer
  variable
• Capture region and shipping method
• from the option button controls into string
  variables
• Capture perishable and fragile status
• from check box controls into Boolean variables
• Capture customer
• from list box control into string variable
• Capture selected range
• from RefEdit control into a string variable
• Put all of the captured values into the worksheet
  in the appropriate ranges
• Close the form frmInputs

18
Main calling sub and some Public variables
Whats the role of these Public variables? See
Sec 10.3 in VBA text.
(1)
(2)
Notice that our Main() subroutine only does two
things (1) it shows the form named frmInputs (2)
it displays a message box with the results of the
users choices from filling out the
form Question How does the Main() subroutine
get called?
19
Yes, we did it!

• We created a user form containing a variety of
  commonly used controls
• We captured the values of the controls chosen or
  entered by the user
• We displayed the results in a message box and
  also figured out how to put them into ranges on
  spreadsheets
• FYI We can also use the Forms toolbar to add
  simple (non-VBA) controls to spreadsheets
• nice way to enhance interface to model without
  programming
• see next slide, ControlExamples.xls and
  IRSSupercomputers-WithControls.xls
• Now we are ready to revisit the
  Scheduling-Annotated.xls application after we
  learn about optimization

20
(No Transcript)
21
Data Validation

• Maximize data integrity
• Simple user interface development
• Build validation rules at cell level
• Rules can be based on values in SAME sheet
• Stop, Warning, Information
• EXAMPLE OBLog_prototype.xls