Using Excel and VBA for Excel

Document Sample
Using Excel and VBA for Excel Powered By Docstoc
					Developing Spreadsheet-Based
Decision Support Systems
  Using Excel and VBA for Excel



                 ¸
   Michelle M.H. Seref
   Decision and Information Sciences
   Warrington College of Business
   University of Florida, Gainesville



   Ravindra K. Ahuja
   Industrial and Systems Engineering
   University of Florida, Gainesville
   and
   Innovative Scheduling, Inc., Gainesville



   Wayne L. Winston
   Operations and Decision Technologies
   Kelly School of Business
   Indiana University, Bloomington




                                   Dynamic Ideas
                               Belmont, Massachusetts
  Contents



             PREFACE                                    xiii

   PART I:   EXCEL ESSENTIALS
CHAPTER 1    Introduction                                1
       1.1   Introduction to Decision Support Systems     2
       1.2   Defining a Decision Support System           3
       1.3   Decision Support Systems Applications        5
       1.4   Textbook Overview                            6
       1.5   Summary                                      8
       1.6   Exercises                                    8

CHAPTER 2    Excel Basics and Formatting                 9
       2.1   Introduction                               10
       2.2   Defining the Set of Excel Objects          10
       2.3   Entering Data into Cells                   10
       2.4   Understanding Excel Menus and Toolbars     13
       2.5   Formatting                                 20
       2.6   Summary                                    27
       2.7   Exercises                                  28

CHAPTER 3    Referencing and Names                      31
       3.1   Introduction                               32
       3.2   Referencing Cells                          32
       3.3   Names for Cells, Ranges, and Worksheets    40
       3.4   Summary                                    48
       3.5   Exercises                                  48

CHAPTER 4    Functions and Formulas                     53
       4.1   Introduction                               54
       4.2   Formulas and Function Categories           54
                                                          v
vi     Contents


             4.3   Logical and Information Functions                        58
             4.4   Text and Lookup & Reference Functions                    66
             4.5   Date & Time Functions                                    77
             4.6   Mathematical and Trigonometry Functions                  84
             4.7   Statistical and Financial Functions                      88
             4.8   Conditional Formatting Formulas                          95
             4.9   Auditing                                                 97
            4.10   Summary                                                  99
            4.11   Exercises                                                99

     CHAPTER 5     Charts                                                  105
             5.1   Introduction                                            106
             5.2   Creating Charts with Chart Wizard                       106
             5.3   Working with Chart Options                              111
             5.4   Creating a Dynamic Chart                                116
             5.5   Summary                                                 119
             5.6   Exercises                                               120

     CHAPTER 6     Pivot Tables                                            123
             6.1   Introduction                                            124
             6.2   Pivot Tables                                            124
             6.3   Further Modifications                                   130
             6.4   Pivot Charts                                            142
             6.5   Summary                                                 144
             6.6   Exercises                                               144

     CHAPTER 7     Statistical Analysis with Excel                         147
             7.1   Introduction                                            148
             7.2   Understanding Data                                      148
             7.3   Relationships in Data                                   161
             7.4   Distributions                                           174
             7.5   Summary                                                 181
             7.6   Exercises                                               182

     CHAPTER 8     Using the Excel Solver to Solve Mathematical Programs   187
             8.1   Introduction                                            188
             8.2   Formulating Mathematical Programs                       188
             8.3   The Excel Solver                                        191
             8.4   Applications of the Solver                              202
             8.5   Summary                                                 216
             8.6   Exercises                                               216

     CHAPTER 9     Simulation                                              221
             9.1   Introduction                                            222
             9.2   Defining Simulation                                     222
             9.3   Applications                                            233
                                                             Contents    vii


        9.4   Summary                                                   240
        9.5   Exercises                                                 240

CHAPTER 10    Working with Large Data                                   245
       10.1   Introduction                                              246
       10.2   Importing Data                                            246
       10.3   Exporting Data                                            255
       10.4   Creating Pivot Tables from External Data                  256
       10.5   Using Excel as a Database                                 258
       10.6   Summary                                                   278
       10.7   Exercises                                                 279

   PART II:   VBA FOR EXCEL
CHAPTER 11    Introduction to the Visual Basic Environment              283
       11.1   Introduction                                              284
       11.2   The Visual Basic Editor                                   284
       11.3   The Object Browser                                        287
       11.4   Summary                                                   289
       11.5   Exercises                                                 290

CHAPTER 12    Recording Macros                                          291
       12.1   Introduction                                              292
       12.2   Macros                                                    292
       12.3   Customizing Toolbars and Menu Options                     306
       12.4   Summary                                                   310
       12.5   Exercises                                                 311

CHAPTER 13    More on Objects                                           315
       13.1   Introduction                                              316
       13.2   More on Properties and Methods                            316
       13.3   The With Construct                                        344
       13.4   Referencing and Names in VBA                              347
       13.5   Formulas in VBA                                           355
       13.6   Summary                                                   361
       13.7   Exercises                                                 361

CHAPTER 14    Variables                                                 367
       14.1   Introduction                                              368
       14.2   Variable Declarations and Data Types                      368
       14.3   Variable Scope                                            372
       14.4   Variables in User Interface                               373
       14.5   VBA Math Functions                                        376
       14.6   Applications                                              381
       14.7   Summary                                                   392
       14.8   Exercises                                                 393
viii      Contents


       CHAPTER 15     Sub Procedures and Function Procedures   399
               15.1   Introduction                             400
               15.2   Sub Procedures                           400
               15.3   Function Procedures                      402
               15.4   Public and Private Procedures            405
               15.5   Applications                             406
               15.6   Summary                                  409
               15.7   Exercises                                410

       CHAPTER 16     Programming Structures                   413
               16.1   Introduction                             414
               16.2   If, Then Statements                      414
               16.3   Select Case                              419
               16.4   Loops                                    420
               16.5   Exit Statements and End                  423
               16.6   Applications                             427
               16.7   Summary                                  434
               16.8   Exercises                                434

       CHAPTER 17     Arrays                                   439
               17.1   Introduction                             440
               17.2   When and Why to Use Arrays               440
               17.3   Defining Arrays                          440
               17.4   Dynamic Arrays                           442
               17.5   Sorting Arrays                           444
               17.6   Applications                             445
               17.7   Summary                                  450
               17.8   Exercises                                451

       CHAPTER 18     User Interface                           455
               18.1   Introduction                             456
               18.2   User Form Controls                       456
               18.3   User Form Options                        472
               18.4   Event Procedures                         473
               18.5   Variable Scope                           476
               18.6   Error Checking                           477
               18.7   Importing and Exporting Forms            479
               18.8   Navigating                               479
               18.9   Professional Appearance                  480
              18.10   Applications                             481
              18.11   Summary                                  492
              18.12   Exercises                                493
                                                            Contents    ix


CHAPTER 19     The Solver Revisited                                    499
        19.1   Introduction                                            500
        19.2   Review of Chapter 8                                     500
        19.3   Solver Commands in VBA                                  503
        19.4   Applications                                            508
        19.5   Summary                                                 514
        19.6   Exercises                                               515

CHAPTER 20     Simulation Revisited                                    519
        20.1   Introduction                                            520
        20.2   Review of Chapter 9                                     520
        20.3   Simulation with VBA                                     521
        20.4   Applications                                            526
        20.5   Summary                                                 531
        20.6   Exercises                                               532

CHAPTER 21     Working with Large Data Using VBA                       537
        21.1   Introduction                                            538
        21.2   Creating Pivot Tables with VBA                          538
        21.3   Using External Data                                     544
        21.4   Exporting Data                                          557
        21.5   Applications                                            558
        21.6   Summary                                                 565
        21.7   Exercises                                               566

   PART III:   CASE STUDIES
CHAPTER 22     The DSS Development Process                             571
        22.1   Defining the DSS Development Process                    572
        22.2   Application Overview and Model Development              572
        22.3   Worksheets                                              574
        22.4   User Interface                                          580
        22.5   Procedures                                              587
        22.6   Re-solve Options                                        589
        22.7   Testing and Final Packaging                             593
        22.8   Summary                                                 594
        22.9   Exercises                                               594

CHAPTER 23     GUI Design                                              595
        23.1   GUI Design                                              596
        23.2   The Theory Behind Effective GUI Design                  596
        23.3   Effective and Ineffective GUI Design                    602
        23.4   Summary                                                 607
        23.5   Exercises                                               607
x        Contents


      CHAPTER 24     Programming Principles                               609
              24.1   Programming Practices                                610
              24.2   Clarity                                              610
              24.3   Efficiency                                           611
              24.4   Summary                                              613
              24.5   Excercises                                           613

     CASE STUDY 1    Birthday Simulation                                  615

     CASE STUDY 2    Eight Queens                                         629

     CASE STUDY 3    Inventory Management                                 649

     CASE STUDY 4    Warehouse Layout                                     669

     CASE STUDY 5    Forecasting Methods                                  699

     CASE STUDY 6    Facility Layout                                      721

     CASE STUDY 7    Portfolio Management and Optimization                741

     CASE STUDY 8    Reliability Analysis                                 765

     CASE STUDY 9    Retirement Planning                                  791

    CASE STUDY 10    Queuing SImulation: Single Server and Multi Server   815

      APPENDIX A     Excel Add-Ins                                        843
               A.1   Introduction                                         844
               A.2   Including Add-Ins and References using VBA           844
               A.3   Data Analysis Toolpack                               847
               A.4   The Solver                                           847
               A.5   @RISK                                                853
               A.6   Crystal Ball                                         854
               A.7   StatTools                                            854
               A.8   Summary                                              854

      APPENDIX B     Debugging and Error Checking                         855
               B.1   Introduction                                         856
               B.2   Types of Errors                                      856
               B.3   The Debug Toolbar                                    857
               B.4   The Debug Windows                                    859
                                                            Contents    xi


       B.5   Debugging Tips                                            861
       B.6   Error Checking                                            862
       B.7   Summary                                                   866

APPENDIX C   Advanced Programming Topics                               867
       C.1   Introduction                                              868
       C.2   Object-Oriented Programming in VBA for Excel              868
       C.3   Opening Other Applications From VBA                       875
       C.4   Summary                                                   880

             REFERENCES                                                881

             ABOUT THE AUTHORS                                         883

             INDEX                                                     885