Vba for Modelers : Developing Decision Support Systems Using Microsoft Excel (2 PAP/CDR)

Vba for Modelers : Developing Decision Support Systems Using Microsoft Excel (2 PAP/CDR)

  • ただいまウェブストアではご注文を受け付けておりません。 ⇒古書を探す
  • 製本 Paperback:紙装版/ペーパーバック版/ページ数 681 p.
  • 言語 ENG,ENG
  • 商品コード 9780495106838
  • DDC分類 658

Table of Contents

  PART I VBA Fundamentals                          1  (347)
Introduction to VBA Development in Excel 3 (5)
Introduction 3 (1)
Example Applications 4 (1)
Decision Support Systems 5 (1)
Required Background 5 (1)
Visual Basic Versus VBA 6 (1)
Summary 7 (1)
The Excel Object Model 8 (8)
Introduction 8 (1)
Objects, Properties, Methods, and Events 8 (2)
Collections as Objects 10 (1)
The Hierarchy of Objects 10 (1)
Object Models in General 11 (4)
Summary 15 (1)
The Visual Basic Editor 16 (15)
Introduction 16 (1)
Important Features of the VBE 16 (4)
The Object Browser 20 (1)
The Immediate and Watch Windows 21 (1)
A First Program 22 (5)
Intellisense 27 (1)
Color Coding and Case 28 (1)
Summary 29 (2)
Recording Macros 31 (15)
Introduction 31 (1)
How to Record a Macro 31 (2)
Recorded Macro Examples 33 (10)
Summary 43 (3)
Getting Started with VBA 46 (35)
Introduction 46 (1)
Subroutines 46 (1)
Declaring Variables and Constants 47 (5)
Built-in Constants 52 (1)
Input Boxes and Message Boxes 53 (1)
Message Boxes with Yes and No Buttons 54 (3)
Using Excel Functions in VBA 57 (1)
Comments 57 (1)
Strings 58 (4)
Specifying Objects, Properties, and 62 (2)
Methods
With Construction 64 (2)
Other Useful VBA Tips 66 (2)
Good Programming Practices 68 (1)
Debugging 69 (7)
Summary 76 (5)
Working with Ranges 81 (20)
Introduction 81 (1)
Exercise 81 (2)
Important Properties and Methods of Ranges 83 (3)
Specifying Ranges with VBA 86 (2)
Examples of Ranges with VBA 88 (10)
Summary 98 (3)
Control Logic and Loops 101(29)
Introduction 101(1)
Exercise 101(2)
If Constructions 103(6)
Case Constructions 109(3)
For Loops 112(6)
For Each Loops 118(2)
Do Loops 120(5)
Summary 125(5)
Working with Other Excel Objects 130(20)
Introduction 130(1)
Exercise 130(2)
Collections and Members of Collections 132(2)
Examples of Workbooks in VBA 134(3)
Examples of Worksheets in VBA 137(5)
Examples of Charts in VBA 142(5)
Summary 147(3)
Arrays 150(26)
Introduction 150(1)
Excercise 150(1)
The Need for Arrays 151(2)
Rules for Working with Arrays 153(3)
Examples of Arrays in VBA 156(15)
Array Functions 171(1)
Summary 171(5)
More on Variables and Subroutines 176(26)
Introduction 176(1)
Exercise 176(3)
Scope of Variables and Subroutines 179(2)
Modularizing Programs 181(3)
Passing Arguments 184(7)
Function Subroutines 191(5)
The Workbook_Open Event Handler 196(1)
Summary 197(5)
User Forms 202(34)
Introduction 202(1)
Exercise 202(3)
Designing User Forms 205(4)
Setting Properties of Controls 209(4)
Creating a User Form Template 213(1)
Writing Event Handlers 213(10)
Displaying a User Form 223(1)
Looping Through the Controls on a User 223(1)
Form
Working with List Boxes 224(1)
Working with the Forms and Control 225(4)
Toolbox Toolbars
Summary 229(7)
Error Handling 236(7)
Introduction 236(1)
Error Handling with On Error Statement 236(2)
Handling Inappropriate User Inputs 238(2)
Summary 240(3)
Working with Files and Folders 243(21)
Introduction 243(1)
Exercise 243(2)
Dialog Boxes for File Operations 245(6)
The FileSearch Object 251(3)
A File Renumbering Example 254(4)
Working with Text Files 258(4)
Summary 262(2)
Importing Data into Excel from a Database 264(23)
Introduction 264(1)
Exercise 264(2)
A Brief Introduction to Relational 266(5)
Databases
A Brief Introduction to SQL 271(4)
The Technology: DAO and ADO 275(5)
Discussion of the SalesOrders Exercise 280(4)
Summary 284(3)
Working with Pivot Tables 287(19)
Introduction 287(1)
Working with Pivot Tables Manually 287(8)
Working with Pivot Tables Using VBA 295(3)
An Example 298(6)
Summary 304(2)
Working with Menus and Toolbars 306(20)
Introduction 306(1)
Using Excel's GUI 306(3)
Relevant Objects for Menus and Toolbars 309(7)
Modifying Menu Bars and Toolbars 316(7)
Summary 323(3)
Automating Solver and Other Add-Ins 326(21)
Introduction 326(1)
Exercise 326(2)
Invoking Solver in VBA 328(9)
Possible Solver Problems 337(2)
Programming with Palisade Add-Ins 339(3)
Summary 342(5)
PART II VBA Management Science Applications 347(320)
Basic Ideas for Application Development 349(25)
with VBA
Introduction 349(1)
Guidelines for Application Development 349(6)
A Car Loan Application 355(17)
Summary 372(2)
A Blending Application 374(17)
Introduction 374(1)
Functionality of the Application 374(1)
Running the Application 375(1)
Setting Up the Excel Sheets 375(3)
Getting Started with the VBA 378(5)
The User Forms and Their Event Handlers 383(3)
The VBA Code in the Module 386(3)
Summary 389(2)
A Product Mix Application 391(20)
Introduction 391(1)
Functionality of the Application 392(1)
Running the Appliction 392(3)
Setting Up the Excel Sheets 395(1)
Getting Started with the VBA 395(2)
The User Forms and Its Event Handlers 397(1)
The VBA Code in the Module 398(10)
Summary 408(3)
An Employee-Scheduling Application 411(14)
Introduction 411(1)
Functionality of the Application 411(1)
Running the Appliction 412(1)
Setting Up the Excel Sheets 413(3)
Getting Started with the VBA 416(1)
The User Forms and Its Event Handlers 417(2)
The VBA Code in the Module 419(3)
Summary 422(3)
A Production-Planning Application 425(26)
Introduction 425(1)
Functionality of the Application 425(1)
Running the Appliction 426(8)
Setting Up the Excel Sheets 434(1)
Getting Started with the VBA 435(1)
The User Forms and Their Event Handlers 436(5)
The VBA Code in the Module 441(8)
Summary 449(2)
A Logistics Application 451(20)
Introduction 451(1)
Functionality of the Application 452(1)
Running the Appliction 452(2)
Setting Up the Access Database 454(2)
Setting Up the Excel Worksheets 456(1)
Getting Started with the VBA 457(1)
The User Forms and Its Event Handlers 458(2)
The VBA Code in the Module 460(8)
Summary 468(3)
A Stock-Trading Simulation Application 471(13)
Introduction 471(1)
Functionality of the Application 472(1)
Running the Application 472(2)
Setting Up the Excel Sheets 474(3)
Getting Started with the VBA 477(1)
The VBA Code in the Module 478(4)
Summary 482(2)
A Capital-Budgeting Application 484(14)
Introduction 484(1)
Functionality of the Application 485(1)
Running the Application 485(2)
Setting Up the Excel Sheets 487(2)
Getting Started with the VBA 489(1)
The User Form and Its Event Handlers 489(1)
The VBA Code in the Module 490(6)
Summary 496(2)
A Regression Application 498(14)
Introduction 498(1)
Functionality of the Application 498(1)
Running the Application 499(2)
Setting Up the Excel Sheets 501(1)
Getting Started with the VBA 501(2)
The User Form and Its Event Handlers 503(2)
The VBA Code in the Module 505(5)
Summary 510(2)
An Exponential Utility Application 512(13)
Introduction 512(1)
Functionality of the Application 513(1)
Running the Application 513(1)
Setting Up the Excel Sheets 514(3)
Getting Started with the VBA 517(1)
The User Form and Its Event Handlers 518(2)
The VBA Code in the Module 520(4)
Summary 524(1)
A Queueing Simulation Application 525(19)
Introduction 525(1)
Functionality of the Application 526(1)
Running the Application 526(2)
Setting Up the Excel Sheets 528(1)
Getting Started with the VBA 529(1)
The Structure of a Queueing Simulation 530(2)
The VBA Code in the Module 532(10)
Summary 542(2)
An Option-Pricing Application 544(23)
Introduction 544(1)
Functionality of the Application 545(1)
Running the Application 545(2)
Setting Up the Excel Sheets 547(3)
Getting Started with the VBA 550(1)
The User Form and Its Event Handlers 551(3)
The VBA Code in the Module 554(11)
Summary 565(2)
An Application for Finding Betas of Stocks 567(19)
Introduction 567(1)
Functionality of the Application 567(1)
Running the Application 568(1)
Setting Up the Excel Sheets 569(3)
Getting Started with the VBA 572(1)
The User Form and Its Event Handlers 573(4)
The VBA Code in the Module 577(7)
Summary 584(2)
A Portfolio Optimization Application 586(25)
Introduction 586(1)
Functionality of the Application 586(1)
Running the Application 587(4)
Web Queries in Excel 591(3)
Setting Up the Excel Sheets 594(1)
Getting Started with the VBA 594(1)
The User Forms and Their Event Handlers 595(3)
The VBA Code in the Module 598(12)
Summary 610(1)
A Data Envelopment Analysis Application 611(20)
Introduction 611(1)
Functionality of the Application 611(1)
Running the Application 612(2)
Setting Up the Excel Sheets and the Text 614(1)
File
Getting Started with the VBA 615(1)
Getting Data from a Text File 616(1)
The VBA Code in the Module 617(11)
Summary 628(3)
An AHP Application for Choosing a Job 631(26)
Introduction 631(1)
Functionality of the Application 631(1)
Running the Application 632(5)
Setting Up the Excel Sheets 637(1)
Getting Started with the VBA 637(2)
The User Forms and Their Event Handlers 639(5)
The VBA Code in the Module 644(10)
Summary 654(3)
A Poker Simulation Application 657(10)
Introduction 657(1)
Functionality of the Application 658(1)
Running the Application 658(1)
Setting Up the Excel Sheets 658(1)
Getting Started with the VBA 659(1)
The VBA Code in the Module 660(7)
Summary 667