Marching Towards Excel Based DSS Application Development

1
Marching Towards Excel Based DSS Application
Development

• Goal develop spreadsheet based decision support
  applications
• 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 More VBA, 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
Types of Excel Applications(based on
Professional Excel Development by Bullen,
Bovey, Green)

• Codeless applications
• Simple (and complex) end user spreadsheets,
  models, data collection forms
• Self-automated workbooks
• VBA code within, often end user developed
• Complexity can vary widely
• Example ClinicWhatIfLookup-Sample.xls,
  CarLoan.xls (Ch 18), StockTrading.xls (Ch24),
  Regression.xls (Ch26), StockBeta.xls (Ch30),
  StockQuery.xls (Ch31), Poker.xls (Ch34)
• Function or General Purpose Add-In
• Developed for yourself or others
• Function libraries and/or useful, general,
  spreadsheet tools
• Items added to menus, toolbars, function lists
• Interaction through functions and user forms
  (add-in worksheets are hidden)
• Example Walkenbachs Power Utility Pack (PUP)
• Application specific add-in
• _at_Risk, Solver, SolverTable, StatPro, YASAI
• Dictator application
• Takes over Excel completely
• Tightly controls what user can do and how they
  can do it
• Lots o code required

3
Simplification through design time development

• Example Scheduling-Annotated.xls
• 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

4
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

5
More Excel Application Development Tips

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

6
Evolution of a simple VBA based Excel tool

• Working on a simulation model as part of a larger
  project for a hospital in New England
• Using Excel to document patient flow patterns
• Used to communicate with another analyst on the
  project
• Use many cell comments for questions, issues,
  notes, etc.
• Wouldnt it be nice if I could generate a list of
  cell comments and associated cell addresses?
• Demo of the file and the desired result

7
Cell Comment Lister Initial Thoughts

• Ill just try to write a sub that does it for a
  specific worksheet
• How exactly do I get the value of a cell
  comment?
• How exactly do I get the cell address of a cell
  having a comment?
• Can I record a macro to help me at all?
• How do I avoid checking all cells in the
  worksheet if they have comments?
• Also want to list a certain row header along with
  the address and comment.
• For now, Ill hardcode the destination for the
  output list of comments and locations.

8
VBA Coding PracticesMiscVBAExamples.xls

• Option Explicit
• Limit variants
• Dont reuse variables
• Variable naming conventions
• Commenting, whitespace, indenting
• Header comments for Modules and Procedures
• Use whitespace to group related lines of code
• Indent lines internal to loop and condition
  statements
• Include useful comments within procedures
• Use line continuation character to break long
  lines
• Name your VB Project

9
Variable Naming Conventions
ltscopegtltarraygtltdata typegtDescriptiveName
Some suggested prefixes

• Improve readability
• Helps with debugging
• Maintainability

10
FunWithMsgBox

• Alternative syntax for specifying function
  arguments
• Customizing message box style, buttons, and
  default button
• Displaying message box title
• Inserting line breaks
• Building long messages
• Storing button pushed info in variable
• MsgBox(arg1,arg2,) vs. MsgBox arg1,arg2,
• Using built in VB constants

11
For Each Loops
For Each object variable In collection bunch
of statements to be repeated Next object
variable

• Very useful for looping through a collection of
  objects
• Cells in a range
• Controls on a form
• Worksheets in a workbook

12
For Each Loops
Dim rngCell As Range For Each rngCell In
Range(A1A10) rngCell.Value 500 Next
rngCell
Dim wks As Worksheet For Each wks In
ActiveWorkbook.Worksheets MsgBox
wks.Name Next wks
13
Select Case
For Each cell In Range("Family") With
cell dValue .Value Select Case
dValue Case 0 .Font.ColorIndex 3
Case Is lt 35000 .Font.ColorIndex 3
Case 35001 To 50000
.Font.ColorIndex 4 Case
50001 To 65000
.Font.ColorIndex 5 Case Else '
above 65000 .Font.ColorIndex
7 End Select End With
Next
Notice the different forms that the individual
Case conditions can take. There are more. See the
Help topic for Select Case.
14
Variable Scope

• Module -Visible to all procedures within a module
• Use sparingly Dim curSalary1 As Currency
• Global or Public - Visible to all procedures in a
  VB Project
• Use sparinglyPublic curSalary2 As Currency
• Procedure or Local - Visible within procedure
  onlyPublic Sub Test1() Dim curSalary3 As
  CurrencyEnd Sub
• Subroutines also have scope
• Public by default
• Private keyword to restrict to Module visibility

15
Array Variables (Ch 9)

• Arrays are just collections of related variables
• They have a type assigned upon declaration
• Also have a size the of elements the array can
  store
• Arrays are really useful for dealing with lists
• Ranges vs. Arrays
• Why not just use worksheet ranges instead of VB
  arrays?
• Sometimes ranges are just fine
• Arrays allow you to avoid operating directly on a
  range and give you the chance to NOT commit a
  bunch of changes to a range until you are ready
• Arrays can have more than 2 dimensions can have
  n dimensions

16
Array Dimensions and Indexing

• Example Assume we have a variable called Costs
  that takes on a different value for each
  monthDim Costs(12) As Double ? Costs(0),
  Costs(1), , Costs(11)Dim Costs(1 to 12) As
  Double ? Costs(1), Costs(2), , Costs(12)
• Now we can reference individual costs by
  including the index desired
• Costs(6) 500
• Arrays can have multiple dimensions
• Example Assume we have costs for 10 different
  stores for each of the 12 months
• Dim Costs(1 to 10,1 to 12) As Double
• Costs(1,6) 500 Sets the cost for store 1 in
  month 6 to 500
• Think of first dimension as the row and the
  second as the column

0-based indexing
1-based indexing
17
Modularizing your Application

• Organize code into Modules by functionality or
  category of some type
• Functional decomposition
• Each procedure is responsible for a single task
• Procedure guidelines
• Encapsulate logic a procedure should stand on
  its own with respect to the job it performs
• Eliminate duplicate code
• Isolate complex operations
• Keep your procedures relatively small (lt 150-200
  lines of code)
• Limit number of procedure arguments

18
Scheduling-Annotated Main Program
1Inputs-Optimize
2Sensitivity
Use Show method
1
Subroutines that weve written
2
19
Passing Parameters (arguments) to Subs and
Functions

• Subroutines can communicate with other
  subroutines via passed parameters
• Use subroutine arguments instead of global
  variables
• Subroutine and function declaration specifies the
  number and type of parameters that a subroutine
  expects
• Functions are like subroutines except that they
  also return values

20
Simple Sub with 2 arguments
Public Sub PutRateInCell(dRate As Double, rngCell
as Range) rngCell.Value dRate End
Sub Heres 2 ways we can call this
subroutine Public Sub Test Using the Call
keyword Call PutRateInCell(0.1,
Range("A3)) Without Call notice no
parentheses in this version PutRateInCell 0.3,
Range("A4") End Sub
21
ByRef and ByVal argument passing

• ByRef by reference

Function CV4DigitNumericTime(iTime As Integer) As
Date
By default, iTime is passed by reference. Its
NOT the current value of iTime that is passed.
Instead, its the MEMORY ADDRESS of iTime that is
passed. ? If iTime has its value changed INSIDE
of the function CV4DigitNumericTime(), that
change will be recognized by whatever program
called the function.
Function CV4DigitNumericTime(ByRef iTime As
Integer) As Date
22
ByRef and ByVal argument passing

• ByVal by value

Function CV4DigitNumericTime(ByVal iTime As
Integer) As Date
The variable iTime is passed by value. Its
like a COPY of iTime is passed into the
function. ? If iTime has its value changed INSIDE
of the function CV4DigitNumericTime(), that
change will NOT be recognized by whatever program
called the function.
23
ByRef and ByVal argument passing

• Even though ByRef is the default, its SAFER to
  pass arguments ByVal to avoid accidentally
  changing their value
• Use ByRef if you need to be able to change the
  argument value
• Some things like arrays cannot be passed by
  value.
• Microsoft .NET changes the default to ByVal.