# Using Excel and VBA for Excel by ryo17749

VIEWS: 143 PAGES: 8

• pg 1
```									Developing Spreadsheet-Based
Decision Support Systems
Using Excel and VBA for Excel

¸
Michelle M.H. Seref
Decision and Information Sciences
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
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

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