BIS 494 Topics in Information Systems Introduction to

BIS 494: Topics in Information Systems Introduction to VBA Development in Excel (2) Chapter 6 (Supplement) Business Process Modeling, Simulation and Design 1

Overview • Generating Random Numbers – DUnifrom – Exponential • Application Development • The Workbook_Open Event Handler – Private Sub – Explanation worksheet 2

Generating Random Numbers • =Rand(): Excel formula, which generates random numbers • @RISK: simulation add-ins, has its own collection of random number functions • Rnd: random number function built-in VBA 3

DUniform • Discrete Uniform distribution (Functions. xls) Function DUniform (lower. Limit As integer, _ upper. Limit As integer) As Integer Application. Volatile Randomize DUniform = lower. Limit + Int((upper. Limit _ – lower. Limit +1) * Rnd) End Function 4

DUniform (e. g. ) 5

Exponential Distribution • In a queue simulation, interarrival and service times are all exponentially distributed. • If an exponential distribution has mean m, then we can generate a random number form it with VBA expression • - m * Log( Rnd ) • e. g. Exponential. xls 6

Exponential Distribution (e. g. ) 7

Basic Ideas for Application Development • Decide clearly what you want the application to accomplish • Communicate to users what the application does and how it works • Provide plenty of comments • Use meaningful names for variables, subs, and other programming elements • Use modular approach with multiple short subs instead of one long sub 8

Basic Ideas for Application Development • Borrow from other programs that you or others have developed • Decide how to obtain the required input data • Decide what can be done at design time rather than at run time • Decide how to report the results • Add appropriate finishing touches 9

The Workbook_Open Event Handler • An event handler: a sub that runs whenever a certain event fires • A Private sub by default (no one else has access to it – other than the event) • Workbook_Open() –built-in name • Placed in This. Workbook code window (not in modules) • Code the application here if you want anything to occur when this Excel workbook is opened • Could be used to show title screen/explanation 10

The Workbook_Open Event Handler (e. g. ) 11

Exercise • Try develop an Excel application with the following functionality: – When the workbook is opened, “explanation” worksheet will be activated. The sheet contains explanation of the application and a button to activate another sheet, “exponential. ” – In “exponential” worksheet, a series of exponential random numbers are generated based on a pre-defined mean of the distribution, put them in a column with column head that reads “Random number” – When an button is clicked, the average of the series will be calculated and reported. 12