Part I: VBA Fundamentals
1. Introduction to VBA Development in Excel
1.1. Introduction
1.2. VBA in Excel 2007 and Later Versions
1.3. Example Applications
1.4. Decision Support Systems
1.5. Required Background
1.6. Visual Basic Versus VBA
1.7. Some Basic Terminology
1.8. Summary
2. The Excel Object Model
2.1. Introduction
2.2. Objects, Properties, Methods, and Events
2.3. Collections as Objects
2.4. The Hierarchy of Objects
2.5. Object Models in General
2.6. Summary
3. The Visual Basic Editor
3.1. Introduction
3.2. Important Features of the VBE
3.3. The Object Browser
3.4. The Immediate and Watch Windows
3.5. A First Program
3.6. Intellisense
3.7. Color Coding and Case
3.8. Finding Subs in the VBE
3.9. Summary
4. Recording Macros
4.1. Introduction
4.2. How to Record a Macro
4.3. Changes from Excel 2007 to Later Versions
4.4. Recorded Macro Examples
4.5. Summary
5. Getting Started with VBA
5.1. Introduction
5.2. Subroutines
5.3. Declaring Variables and Constants
5.4. Built-In Constants
5.5. Input Boxes and Message Boxes
5.6. Message Boxes with Yes and No Buttons
5.7. Using Excel Functions in VBA
5.8. Comments
5.9. Indenting
5.10. Strings
5.11. Specifying Objects, Properties, and Methods
5.12. With Construction
5.13. Other Useful VBA Tips
5.14. Good Programming Practices
5.15. Debugging
5.16. Summary
6. Working with Ranges
6.1. Introduction
6.2. Exercise
6.3. Important Properties and Methods of Ranges
6.4. Referencing Ranges with VBA
6.5. Examples of Ranges with VBA
6.6. Range Names and Their Scope
6.7. Summary
7. Control Logic and Loops
7.1. Introduction
7.2. Exercise
7.3. If Constructions
7.4. Case Constructions
7.5. For Loops
7.6. For Each Loops
7.7. Do Loops
7.8. Summary
8. Working with Other Excel Objects
8.1. Introduction
8.2. Exercise
8.3. Collections and Members of Collections
8.4. Examples of Workbooks in VBA
8.5. Examples of Worksheets in VBA
8.6. Examples of Charts in VBA
8.7. Summary
9. Arrays
9.1. Introduction
9.2. Exercise
9.3. The Need for Arrays
9.4. Rules for Working with Arrays
9.5. Examples of Arrays in VBA
9.6. Array Functions
9.7. Summary
10. More on Variables and Subroutines
10.1. Introduction
10.2. Exercise
10.3. Scope of Variables and Subroutines
10.4. Modularizing Programs
10.5. Passing Arguments
10.6. Function Subroutines
10.7. The Workbook_Open Event Handler
10.8. Summary
11. User Forms
11.1. Introduction
11.2. Exercise
11.3. Designing User Forms
11.4. Setting Properties of Controls
11.5. Creating a User Form Template
11.6. Writing Event Handlers
11.7. Displaying a User Form
11.8. Looping Through the Controls on a User Form
11.9. Working with List Boxes
11.10. Modal and Modeless Forms
11.11. Working with Excel Controls
11.12. Summary
12. Error Handling
12.1. Introduction
12.2. Error Handling with On Error Statement
12.3. Handling Inappropriate User Inputs
12.4. Summary
13. Working with Files and Folders
13.1. Introduction
13.2. Exercise
13.3. Dialog Boxes for File Operations
13.4. The FileSystemObject Object
13.5. A File Renaming Example
13.6. Working with Text Files
13.7. Summary
14. Importing Data into Excel from a Database
14.1. Introduction
14.2. Exercise
14.3. A Brief Introduction to Relational Databases
14.4. A Brief Introduction to SQL
14.5. ActiveX Data Objects (ADO)
14.6. Discussion of the Sales Orders Exercise
14.7. Summary
15. Working with Pivot Tables and Tables
15.1. Introduction
15.2. Working with Pivot Tables Manually
15.3. Working with Pivot Tables Using VBA
15.4. An Example
15.5. PowerPivot and the Data Model
15.6. Working with Excel Tables Manually
15.7. Working with Excel Tables with VBA
15.8. Summary
16. Working with Ribbons, Menus, and Toolbars
16.1. Introduction
16.2. Customizing Ribbons
16.3. Using RibbonX and XML to Customize Ribbons
16.4. Using RibbonX to Customize the QAT
16.5. CommandBar and Related Office Objects
16.6. A Grading Program Example
16.7. Summary
17. Automating Solver and Other Applications
17.1. Introduction
17.2. Exercise
17.3. Automating Solver with VBA
17.4. Possible Solver Problems
17.5. Programming with Risk Solver Platform
17.6. Automating @RISK with VBA
17.7. Automating Other Office Applications with VBA
17.8. Summary
18. User-Defined Types, Enumerations, Collections, and Classes
18.1. Introduction
18.2. User-Defined Types
18.3. Enumerations
18.4. Collections
18.5. Classes
18.6. Summary
19. Basic Ideas for Application Development with VBA
19.1. Introduction
19.2. Guidelines for Application Development
19.3. A Car Loan Application
19.4. Summary
20. A Blending Application
20.1. Introduction
20.2. Functionality of the Application
20.3. Running the Application
20.4. Setting Up the Excel Sheets
20.5. Getting Started with the VBA
20.6. The User Forms
20.7. The Module
20.8. Summary
21. A Product Mix Application
21.1. Introduction
21.2. Functionality of the Application
21.3. Running the Application
21.4. Setting Up the Excel Sheets
21.5. Getting Started with the VBA
21.6. The User Form
21.7. The Module
21.8. Summary
22. A Worker Scheduling Application
22.1. Introduction
22.2. Functionality of the Application
22.3. Running the Application
22.4. Setting Up the Excel Sheets
22.5. Getting Started with the VBA
22.6. The User Form
22.7. The Module
22.8. Summary
23. A Production Planning Application
23.1. Introduction
23.2. Functionality of the Application
23.3. Running the Application
23.4. Setting Up the Excel Sheets
23.5. Getting Started with the VBA
23.6. The User Forms
23.7. The Module
23.8. Summary
24. A Transportation Application
24.1. Introduction
24.2. Functionality of the Application
24.3. Running the Application
24.4. Setting Up the Access Database
24.5. Setting Up the Excel Sheets
24.6. Getting Started with the VBA
24.7. The User Form
24.8. The Module
24.9. Summary
25. A Stock Trading Simulation Application
25.1. Introduction
25.2. Functionality of the Application
25.3. Running the Application
25.4. Setting Up the Excel Sheets
25.5. Getting Started with the VBA
25.6. The Module
25.7. Summary
26. A Capital Budgeting Application
26.1. Introduction
26.2. Functionality of the Application
26.3. Running the Application
26.4. Setting Up the Excel Sheets
26.5. Getting Started with the VBA
26.6. The User Form
26.7. The Module
26.8. Summary
27. A Regression Application
27.1. Introduction
27.2. Functionality of the Application
27.3. Running the Application
27.4. Setting Up the Excel Sheets
27.5. Getting Started with the VBA
27.6. The User Form
27.7. The Module
27.8. Summary
28. An Exponential Utility Application
28.1. Introduction
28.2. Functionality of the Application
28.3. Running the Application
28.4. Setting Up the Excel Sheets
28.5. Getting Started with the VBA
28.6. The User Form
28.7. The Module
28.8. Summary
29. A Queueing Simulation Application
29.1. Introduction
29.2. Functionality of the Application
29.3. Running the Application
29.4. Setting Up the Excel Sheets
29.5. Getting Started with the VBA
29.6. Structure of a Queueing Simulation
29.7. The Module
29.8. Summary
30. An Option Pricing Application
30.1. Introduction
30.2. Functionality of the Application
30.3. Running the Application
30.4. Setting Up the Excel Sheets
30.5. Getting Started with the VBA
30.6. The User Form
30.7. The Module
30.8. Summary
31. An Application for Finding Betas of Stocks
31.1. Introduction
31.2. Functionality of the Application
31.3. Running the Application
31.4. Setting Up the Excel Sheets
31.5. Getting Started with the VBA
31.6. The User Forms
31.7. The Module
31.8. Summary
32. A Portfolio Optimization Application
32.1. Introduction
32.2. Functionality of the Application
32.3. Running the Application
32.4. Web Queries in Excel
32.5. Setting Up the Excel Sheets
32.6. Getting Started with the VBA
32.7. The User Forms
32.8. The Module
32.9. Summary
33. A Data Envelopment Analysis Application
33.1. Introduction
33.2. Functionality of the Application
33.3. Running the Application
33.4. Setting Up the Excel Sheets and the Text File
33.5. Getting Started with the VBA
33.6. Getting Data from a Text File
33.7. The Module
33.8. Summary
34. An AHP Application for Choosing a Job
34.1. Introduction
34.2. Functionality of the Application
34.3. Running the Application
34.4. Setting Up the Excel Sheets
34.5. Getting Started with the VBA
34.6. The User Forms
34.7. The Module
34.8. Summary
35. A Poker Simulation Application
35.1. Introduction
35.2. Functionality of the Application
35.3. Running the Application
35.4. Setting Up the Excel Sheets
35.5. Getting Started with the VBA
35.6. The Module
35.7. Summary