VBA Fundamentals | p. 1 |
Introduction to VBA Development in Excel | p. 3 |
Introduction | p. 3 |
Example Applications | p. 4 |
Required Background | p. 4 |
Visual Basic Versus VBA | p. 5 |
Summary | p. 6 |
The Excel Object Model | p. 7 |
Introduction | p. 7 |
Objects, Properties, and Methods | p. 7 |
Collections as Objects | p. 8 |
The Hierarchy of Objects | p. 9 |
Object Models in General | p. 12 |
Summary | p. 12 |
The Visual Basic Editor | p. 13 |
Introduction | p. 13 |
Important Features of the VBE | p. 13 |
The Object Browser | p. 17 |
The Immediate and Watch Windows | p. 18 |
A First Program | p. 18 |
Summary | p. 23 |
Getting Started with VBA | p. 27 |
Introduction | p. 27 |
Subroutines | p. 27 |
Understanding and Declaring Variables | p. 28 |
Input Boxes and Message Boxes | p. 31 |
Comments | p. 35 |
Strings | p. 35 |
Specifying Objects, Properties, and Methods | p. 39 |
The With Construction | p. 41 |
Other Useful VBA Tips | p. 43 |
Summary | p. 44 |
Recording Macros | p. 49 |
Introduction | p. 49 |
How to Record a Macro | p. 49 |
Recorded Macro Examples | p. 51 |
Summary | p. 61 |
Working with Ranges | p. 63 |
Introduction | p. 63 |
Exercise | p. 63 |
Important Properties and Methods of Ranges | p. 64 |
Specifying Ranges with VBA | p. 68 |
Examples of Ranges with VBA | p. 70 |
Summary | p. 78 |
Control Logic and Loops | p. 81 |
Introduction | p. 81 |
Exercise | p. 81 |
If Constructions | p. 83 |
Case Constructions | p. 89 |
For Loops | p. 91 |
For Each Loops | p. 96 |
Do Loops | p. 98 |
Summary | p. 102 |
Working with Other Excel Objects | p. 107 |
Introduction | p. 107 |
Exercise | p. 107 |
Collections and Specific Members of Collections | p. 109 |
Examples of Workbooks in VBA | p. 110 |
Examples of Worksheets in VBA | p. 113 |
Examples of Charts in VBA | p. 118 |
Summary | p. 122 |
Arrays | p. 125 |
Introduction | p. 125 |
Exercise | p. 125 |
The Need for Arrays | p. 126 |
Rules for Working with Arrays | p. 128 |
Examples of Arrays in VBA | p. 130 |
Array Functions | p. 144 |
Summary | p. 144 |
More on Variables and Subroutines | p. 147 |
Introduction | p. 147 |
Exercise | p. 147 |
Scope of Variables and Subroutines | p. 150 |
Modularizing Programs | p. 152 |
Passing Arguments | p. 155 |
Function Subroutines | p. 160 |
The Workbook_Open Subroutine | p. 165 |
Summary | p. 166 |
Working with the Solver Add-In in VBA | p. 171 |
Introduction | p. 171 |
Exercise | p. 171 |
Invoking the Solver in VBA | p. 173 |
A Solver VBA Bug | p. 182 |
Summary | p. 183 |
User Forms | p. 187 |
Introduction | p. 187 |
Exercise | p. 187 |
Designing User Forms | p. 190 |
Setting Properties of Controls | p. 194 |
Creating a User Form Template | p. 197 |
Writing Event Code for User Forms | p. 198 |
Displaying a User Form | p. 206 |
Looping Through the Controls on a User Form | p. 207 |
Working with List Boxes | p. 208 |
Summary | p. 209 |
Debugging and Error Handling | p. 213 |
Introduction | p. 213 |
Exercise | p. 213 |
Debugging | p. 214 |
Error Handling | p. 220 |
Summary | p. 223 |
VBA Applications for Management Science | p. 227 |
Basic Ideas for Application Development with VBA | p. 229 |
Introduction | p. 229 |
Guidelines for Application Development | p. 229 |
A Car Loan Application | p. 234 |
Summary | p. 250 |
A Blending Application | p. 253 |
Introduction | p. 253 |
Functionality of the Application | p. 253 |
Running the Application | p. 254 |
Setting Up the Excel Sheets | p. 260 |
Getting Started with the VBA | p. 260 |
Designing the User Forms and Writing Their Event Code | p. 262 |
The VBA Code in the Module | p. 266 |
Summary | p. 269 |
A Product Mix Application | p. 271 |
Introduction | p. 271 |
Functionality of the Application | p. 272 |
Running the Application | p. 272 |
Setting Up the Excel Sheets | p. 274 |
Getting Started with the VBA | p. 275 |
Designing the User Form and Writing Its Event Code | p. 276 |
The VBA Code in the Module | p. 278 |
Summary | p. 287 |
An Employee Scheduling Application | p. 291 |
Introduction | p. 291 |
Functionality of the Application | p. 291 |
Running the Application | p. 292 |
Setting Up the Excel Sheets | p. 295 |
Getting Started with the VBA | p. 297 |
Designing the User Forms and Writing Their Event Code | p. 298 |
The VBA Code in the Module | p. 301 |
Summary | p. 304 |
A Production Scheduling Application | p. 307 |
Introduction | p. 307 |
Functionality of the Application | p. 307 |
Running the Application | p. 308 |
Setting Up the Excel Sheets | p. 315 |
Getting Started with the VBA | p. 317 |
Designing the User Forms and Writing Their Event Code | p. 318 |
The VBA Code in the Module | p. 322 |
Summary | p. 330 |
A Minimum-Cost Network Flow Application | p. 333 |
Introduction | p. 333 |
Functionality of the Application | p. 334 |
Running the Application | p. 334 |
Setting Up the Access Database | p. 335 |
Setting Up the Excel Worksheets | p. 338 |
Getting Started with the VBA | p. 338 |
A Quick Introduction to ADO | p. 340 |
The VBA Code in the Module | p. 342 |
Summary | p. 350 |
A Stock Trading Simulation | p. 353 |
Introduction | p. 353 |
Functionality of the Application | p. 354 |
Running the Application | p. 354 |
Setting Up the Excel Sheets | p. 357 |
Getting Started with the VBA | p. 359 |
The VBA Code in the Module | p. 360 |
Summary | p. 364 |
A Capital Budgeting Application | p. 367 |
Introduction | p. 367 |
Functionality of the Application | p. 368 |
Running the Application | p. 368 |
Setting Up the Excel Sheets | p. 370 |
Getting Started with the VBA | p. 371 |
Designing the User Form and Writing Its Event Code | p. 372 |
The VBA Code in the Module | p. 373 |
Summary | p. 378 |
An Application for Estimating the Relationship Between Two Variables | p. 381 |
Introduction | p. 381 |
Functionality of the Application | p. 381 |
Running the Application | p. 382 |
Setting Up the Excel Sheets | p. 384 |
Getting Started with the VBA | p. 385 |
Designing the User Form and Writing Its Event Code | p. 386 |
The VBA Code in the Module | p. 388 |
Summary | p. 392 |
An Exponential Utility Application | p. 395 |
Introduction | p. 395 |
Functionality of the Application | p. 395 |
Running the Application | p. 396 |
Setting Up the Excel Sheets | p. 399 |
Getting Started with the VBA | p. 400 |
Designing the User Form and Writing Its Event Code | p. 401 |
The VBA Code in the Module | p. 403 |
Summary | p. 406 |
Simulation of a Multiserver Queue | p. 409 |
Introduction | p. 409 |
Functionality of the Application | p. 410 |
Running the Application | p. 410 |
Setting Up the Excel Sheets | p. 412 |
Getting Started with the VBA | p. 412 |
The Structure of a Queueing Simulation | p. 413 |
The VBA Code in the Module | p. 415 |
Summary | p. 424 |
An Application for Pricing European and American Options | p. 427 |
Introduction | p. 427 |
Functionality of the Application | p. 428 |
Running the Application | p. 428 |
Setting Up the Excel Sheets | p. 430 |
Getting Started with the VBA | p. 431 |
Designing the User Form and Writing Its Event Code | p. 433 |
The VBA Code in the Module | p. 436 |
Summary | p. 445 |
An Application for Finding Betas of Stocks | p. 447 |
Introduction | p. 447 |
Functionality of the Application | p. 447 |
Running the Application | p. 448 |
Setting Up the Excel Sheets | p. 449 |
Getting Started with the VBA | p. 452 |
Designing the User Forms and Writing Their Event Code | p. 453 |
The VBA Code in the Module | p. 456 |
Summary | p. 462 |
A Portfolio Optimization Application | p. 465 |
Introduction | p. 465 |
Functionality of the Application | p. 465 |
Running the Application | p. 466 |
Web Queries in Excel | p. 470 |
Setting Up the Excel Sheets | p. 473 |
Getting Started with the VBA | p. 473 |
Designing the User Forms and Writing Their Event Code | p. 474 |
The VBA Code in the Module | p. 477 |
Summary | p. 486 |
A Data Envelopment Analysis Application | p. 489 |
Introduction | p. 489 |
Functionality of the Application | p. 489 |
Running the Application | p. 490 |
Setting Up the Excel Sheets and the Text File | p. 492 |
Getting Started with the VBA | p. 493 |
Designing the User Form and Writing Its Event Code | p. 494 |
Getting Data from a Text File | p. 495 |
The VBA Code in the Module | p. 496 |
Summary | p. 506 |
An AHP Application for Choosing a Job | p. 509 |
Introduction | p. 509 |
Functionality of the Application | p. 509 |
Running the Application | p. 510 |
Setting Up the Excel Sheets | p. 513 |
Getting Started with the VBA | p. 515 |
Designing the User Forms and Writing Their Event Code | p. 518 |
The VBA Code in the Module | p. 521 |
Summary | p. 531 |
A Poker Simulation | p. 533 |
Introduction | p. 533 |
Functionality of the Application | p. 534 |
Running the Application | p. 534 |
Setting Up the Excel Sheets | p. 536 |
Getting Started with the VBA | p. 536 |
The VBA Code in the Module | p. 536 |
Summary | p. 542 |
Index | p. 545 |
Table of Contents provided by Syndetics. All Rights Reserved. |