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