Excel 2010 Power Programming with VBA

Part I: Some Essential Background - Page 35
Part I: Some Essential Background - Page 36
Chapter 1: Excel 2010: Where It Came From - Page 37
Chapter 1: Excel 2010: Where It Came From - Page 38
Chapter 1: Excel 2010: Where It Came From - Page 39
Chapter 1: Excel 2010: Where It Came From - Page 40
Chapter 1: Excel 2010: Where It Came From - Page 41
Chapter 1: Excel 2010: Where It Came From - Page 42
Chapter 1: Excel 2010: Where It Came From - Page 43
Chapter 1: Excel 2010: Where It Came From - Page 44
Chapter 1: Excel 2010: Where It Came From - Page 45
Why Excel Is Great for Developers - Page 46
Why Excel Is Great for Developers - Page 47
Excel’s Role in Microsoft’s Strategy - Page 48
Chapter 2: Excel in a Nutshell - Page 49
Workbooks - Page 50
Workbooks - Page 51
Workbooks - Page 52
Workbooks - Page 53
Excel’s User Interface - Page 54
Excel’s User Interface - Page 55
Excel’s User Interface - Page 56
Excel’s User Interface - Page 57
Excel’s User Interface - Page 58
Excel’s User Interface - Page 59
Excel’s User Interface - Page 60
Excel’s User Interface - Page 61
Excel’s User Interface - Page 62
Customizing the Display - Page 63
Data Entry - Page 64
Data Entry - Page 65
Selecting Objects - Page 66
Selecting Objects - Page 67
Protection Options - Page 68
Protection Options - Page 69
Charts - Page 70
Shapes and SmartArt - Page 71
Database Access - Page 72
Internet Features - Page 73
Analysis Tools - Page 74
Analysis Tools - Page 75
Add-Ins - Page 76
Excel’s Help System - Page 77
Excel’s Help System - Page 78
Chapter 3: Formula Tricks and Techniques - Page 79
Calculating Formulas - Page 80
Cell and Range References - Page 81
Cell and Range References - Page 82
Cell and Range References - Page 83
Using Names - Page 84
Using Names - Page 85
Using Names - Page 86
Using Names - Page 87
Using Names - Page 88
Using Names - Page 89
Using Names - Page 90
Formula Errors - Page 91
Array Formulas - Page 92
Array Formulas - Page 93
Array Formulas - Page 94
Counting and Summing Techniques - Page 95
Counting and Summing Techniques - Page 96
Working with Dates and Times - Page 97
Working with Dates and Times - Page 98
Working with Dates and Times - Page 99
Creating Megaformulas - Page 100
Creating Megaformulas - Page 101
Creating Megaformulas - Page 102
Chapter 4: Understanding Excel Files - Page 103
Chapter 4: Understanding Excel Files - Page 104
Chapter 4: Understanding Excel Files - Page 105
File Types - Page 106
File Types - Page 107
File Types - Page 108
Working with Template Files - Page 109
Working with Template Files - Page 110
Working with Template Files - Page 111
Working with Template Files - Page 112
Inside an Excel File - Page 113
Inside an Excel File - Page 114
Inside an Excel File - Page 115
Inside an Excel File - Page 116
The OfficeUI File - Page 117
The XLB File - Page 118
Add-In Files - Page 119
Excel Settings in the Registry - Page 120
Excel Settings in the Registry - Page 121
Excel Settings in the Registry - Page 122
Excel Settings in the Registry - Page 123
Excel Settings in the Registry - Page 124
Part II: Excel Application Development - Page 125
Part II: Excel Application Development - Page 126
Chapter 5: What Is a Spreadsheet Application? - Page 127
The Developer and the End User - Page 128
The Developer and the End User - Page 129
The Developer and the End User - Page 130
Solving Problems with Excel - Page 131
Basic Spreadsheet Types - Page 132
Basic Spreadsheet Types - Page 133
Basic Spreadsheet Types - Page 134
Basic Spreadsheet Types - Page 135
Basic Spreadsheet Types - Page 136
Chapter 6: Essentials of Spreadsheet Application Development - Page 137
Determining User Needs - Page 138
Planning an Application That Meets User Needs - Page 139
Planning an Application That Meets User Needs - Page 140
Determining the Most Appropriate User Interface - Page 141
Determining the Most Appropriate User Interface - Page 142
Determining the Most Appropriate User Interface - Page 143
Determining the Most Appropriate User Interface - Page 144
Determining the Most Appropriate User Interface - Page 145
Determining the Most Appropriate User Interface - Page 146
Determining the Most Appropriate User Interface - Page 147
Concerning Yourself with the End User - Page 148
Concerning Yourself with the End User - Page 149
Concerning Yourself with the End User - Page 150
Concerning Yourself with the End User - Page 151
Concerning Yourself with the End User - Page 152
Concerning Yourself with the End User - Page 153
Concerning Yourself with the End User - Page 154
Other Development Issues - Page 155
Other Development Issues - Page 156
Other Development Issues - Page 157
Other Development Issues - Page 158
Part III: Understanding Visual Basic for Applications - Page 159
Part III: Understanding Visual Basic for Applications - Page 160
Chapter 7: Introducing Visual Basic for Applications - Page 161
Delving in to VBA - Page 162
Covering the Basics of VBA - Page 163
Covering the Basics of VBA - Page 164
Covering the Basics of VBA - Page 165
Introducing the Visual Basic Editor - Page 166
Introducing the Visual Basic Editor - Page 167
Introducing the Visual Basic Editor - Page 168
Working with the Project Explorer - Page 169
Working with the Project Explorer - Page 170
Working with Code Windows - Page 171
Working with Code Windows - Page 172
Working with Code Windows - Page 173
Working with Code Windows - Page 174
Working with Code Windows - Page 175
Working with Code Windows - Page 176
Working with Code Windows - Page 177
Working with Code Windows - Page 178
Customizing the VBE Environment - Page 179
Customizing the VBE Environment - Page 180
Customizing the VBE Environment - Page 181
Customizing the VBE Environment - Page 182
Customizing the VBE Environment - Page 183
Customizing the VBE Environment - Page 184
The Macro Recorder - Page 185
The Macro Recorder - Page 186
The Macro Recorder - Page 187
The Macro Recorder - Page 188
The Macro Recorder - Page 189
The Macro Recorder - Page 190
The Macro Recorder - Page 191
The Macro Recorder - Page 192
About Objects and Collections - Page 193
About Objects and Collections - Page 194
Properties and Methods - Page 195
Properties and Methods - Page 196
Properties and Methods - Page 197
The Comment Object: A Case Study - Page 198
The Comment Object: A Case Study - Page 199
The Comment Object: A Case Study - Page 200
The Comment Object: A Case Study - Page 201
The Comment Object: A Case Study - Page 202
The Comment Object: A Case Study - Page 203
The Comment Object: A Case Study - Page 204
The Comment Object: A Case Study - Page 205
Some Useful Application Properties - Page 206
Some Useful Application Properties - Page 207
Working with Range Objects - Page 208
Working with Range Objects - Page 209
Working with Range Objects - Page 210
Working with Range Objects - Page 211
Working with Range Objects - Page 212
Working with Range Objects - Page 213
Things to Know about Objects - Page 214
Things to Know about Objects - Page 215
Things to Know about Objects - Page 216
Things to Know about Objects - Page 217
Things to Know about Objects - Page 218
Chapter 8: VBA Programming Fundamentals - Page 219
Chapter 8: VBA Programming Fundamentals - Page 220
Comments - Page 221
Comments - Page 222
Variables, Data Types, and Constants - Page 223
Variables, Data Types, and Constants - Page 224
Variables, Data Types, and Constants - Page 225
Variables, Data Types, and Constants - Page 226
Variables, Data Types, and Constants - Page 227
Variables, Data Types, and Constants - Page 228
Variables, Data Types, and Constants - Page 229
Variables, Data Types, and Constants - Page 230
Variables, Data Types, and Constants - Page 231
Variables, Data Types, and Constants - Page 232
Variables, Data Types, and Constants - Page 233
Variables, Data Types, and Constants - Page 234
Variables, Data Types, and Constants - Page 235
Assignment Statements - Page 236
Assignment Statements - Page 237
Assignment Statements - Page 238
Arrays - Page 239
Arrays - Page 240
Object Variables - Page 241
User-Defined Data Types - Page 242
Built-in Functions - Page 243
Built-in Functions - Page 244
Built-in Functions - Page 245
Manipulating Objects and Collections - Page 246
Manipulating Objects and Collections - Page 247
Manipulating Objects and Collections - Page 248
Controlling Code Execution - Page 249
Controlling Code Execution - Page 250
Controlling Code Execution - Page 251
Controlling Code Execution - Page 252
Controlling Code Execution - Page 253
Controlling Code Execution - Page 254
Controlling Code Execution - Page 255
Controlling Code Execution - Page 256
Controlling Code Execution - Page 257
Controlling Code Execution - Page 258
Controlling Code Execution - Page 259
Controlling Code Execution - Page 260
Controlling Code Execution - Page 261
Controlling Code Execution - Page 262
Controlling Code Execution - Page 263
Controlling Code Execution - Page 264
Controlling Code Execution - Page 265
Controlling Code Execution - Page 266
Chapter 9: Working with VBA Sub Procedures - Page 267
Chapter 9: Working with VBA Sub Procedures - Page 268
Chapter 9: Working with VBA Sub Procedures - Page 269
Executing Sub Procedures - Page 270
Executing Sub Procedures - Page 271
Executing Sub Procedures - Page 272
Executing Sub Procedures - Page 273
Executing Sub Procedures - Page 274
Executing Sub Procedures - Page 275
Executing Sub Procedures - Page 276
Executing Sub Procedures - Page 277
Executing Sub Procedures - Page 278
Executing Sub Procedures - Page 279
Executing Sub Procedures - Page 280
Passing Arguments to Procedures - Page 281
Passing Arguments to Procedures - Page 282
Passing Arguments to Procedures - Page 283
Passing Arguments to Procedures - Page 284
Error-Handling Techniques - Page 285
Error-Handling Techniques - Page 286
Error-Handling Techniques - Page 287
Error-Handling Techniques - Page 288
Error-Handling Techniques - Page 289
A Realistic Example That Uses Sub Procedures - Page 290
A Realistic Example That Uses Sub Procedures - Page 291
A Realistic Example That Uses Sub Procedures - Page 292
A Realistic Example That Uses Sub Procedures - Page 293
A Realistic Example That Uses Sub Procedures - Page 294
A Realistic Example That Uses Sub Procedures - Page 295
A Realistic Example That Uses Sub Procedures - Page 296
A Realistic Example That Uses Sub Procedures - Page 297
A Realistic Example That Uses Sub Procedures - Page 298
A Realistic Example That Uses Sub Procedures - Page 299
A Realistic Example That Uses Sub Procedures - Page 300
A Realistic Example That Uses Sub Procedures - Page 301
A Realistic Example That Uses Sub Procedures - Page 302
A Realistic Example That Uses Sub Procedures - Page 303
A Realistic Example That Uses Sub Procedures - Page 304
A Realistic Example That Uses Sub Procedures - Page 305
A Realistic Example That Uses Sub Procedures - Page 306
Chapter 10: Creating Function Procedures - Page 307
Why Create Custom Functions? - Page 308
Why Create Custom Functions? - Page 309
Why Create Custom Functions? - Page 310
Why Create Custom Functions? - Page 311
Why Create Custom Functions? - Page 312
Function Procedures - Page 313
Function Procedures - Page 314
Function Procedures - Page 315
Function Procedures - Page 316
Function Procedures - Page 317
Function Arguments - Page 318
Function Examples - Page 319
Function Examples - Page 320
Function Examples - Page 321
Function Examples - Page 322
Function Examples - Page 323
Function Examples - Page 324
Function Examples - Page 325
Function Examples - Page 326
Function Examples - Page 327
Function Examples - Page 328
Function Examples - Page 329
Function Examples - Page 330
Function Examples - Page 331
Function Examples - Page 332
Function Examples - Page 333
Emulating Excel’s SUM function - Page 334
Emulating Excel’s SUM function - Page 335
Emulating Excel’s SUM function - Page 336
Extended Date Functions - Page 337
Extended Date Functions - Page 338
Debugging Functions - Page 339
Dealing with the Insert Function Dialog Box - Page 340
Dealing with the Insert Function Dialog Box - Page 341
Dealing with the Insert Function Dialog Box - Page 342
Dealing with the Insert Function Dialog Box - Page 343
Dealing with the Insert Function Dialog Box - Page 344
Using Add-ins to Store Custom Functions - Page 345
Using the Windows API - Page 346
Using the Windows API - Page 347
Using the Windows API - Page 348
Using the Windows API - Page 349
Using the Windows API - Page 350
Chapter 11: VBA Programming Examples and Techniques - Page 351
Working with Ranges - Page 352
Working with Ranges - Page 353
Working with Ranges - Page 354
Working with Ranges - Page 355
Working with Ranges - Page 356
Working with Ranges - Page 357
Working with Ranges - Page 358
Working with Ranges - Page 359
Working with Ranges - Page 360
Working with Ranges - Page 361
Working with Ranges - Page 362
Working with Ranges - Page 363
Working with Ranges - Page 364
Working with Ranges - Page 365
Working with Ranges - Page 366
Working with Ranges - Page 367
Working with Ranges - Page 368
Working with Ranges - Page 369
Working with Ranges - Page 370
Working with Ranges - Page 371
Working with Ranges - Page 372
Working with Ranges - Page 373
Working with Ranges - Page 374
Working with Ranges - Page 375
Working with Ranges - Page 376
Working with Ranges - Page 377
Working with Ranges - Page 378
Working with Workbooks and Sheets - Page 379
Working with Workbooks and Sheets - Page 380
Working with Workbooks and Sheets - Page 381
Working with Workbooks and Sheets - Page 382
VBA Techniques - Page 383
VBA Techniques - Page 384
VBA Techniques - Page 385
VBA Techniques - Page 386
VBA Techniques - Page 387
VBA Techniques - Page 388
VBA Techniques - Page 389
VBA Techniques - Page 390
Some Useful Functions for Use in Your Code - Page 391
Some Useful Functions for Use in Your Code - Page 392
Some Useful Functions for Use in Your Code - Page 393
Some Useful Functions for Use in Your Code - Page 394
Some Useful Functions for Use in Your Code - Page 395
Some Useful Worksheet Functions - Page 396
Some Useful Worksheet Functions - Page 397
Some Useful Worksheet Functions - Page 398
Some Useful Worksheet Functions - Page 399
Some Useful Worksheet Functions - Page 400
Some Useful Worksheet Functions - Page 401
Some Useful Worksheet Functions - Page 402
Some Useful Worksheet Functions - Page 403
Some Useful Worksheet Functions - Page 404
Some Useful Worksheet Functions - Page 405
Some Useful Worksheet Functions - Page 406
Some Useful Worksheet Functions - Page 407
Some Useful Worksheet Functions - Page 408
Some Useful Worksheet Functions - Page 409
Some Useful Worksheet Functions - Page 410
Some Useful Worksheet Functions - Page 411
Windows API Calls - Page 412
Windows API Calls - Page 413
Windows API Calls - Page 414
Windows API Calls - Page 415
Windows API Calls - Page 416
Windows API Calls - Page 417
Windows API Calls - Page 418
Windows API Calls - Page 419
Windows API Calls - Page 420
Windows API Calls - Page 421
Windows API Calls - Page 422
Part IV: Working with UserForms - Page 423
Part IV: Working with UserForms - Page 424
Chapter 12: Custom Dialog Box Alternatives - Page 425
Chapter 12: Custom Dialog Box Alternatives - Page 426
Chapter 12: Custom Dialog Box Alternatives - Page 427
Chapter 12: Custom Dialog Box Alternatives - Page 428
Chapter 12: Custom Dialog Box Alternatives - Page 429
The VBA MsgBox Function - Page 430
The VBA MsgBox Function - Page 431
The VBA MsgBox Function - Page 432
The VBA MsgBox Function - Page 433
The VBA MsgBox Function - Page 434
The Excel GetOpenFilename Method - Page 435
The Excel GetOpenFilename Method - Page 436
The Excel GetOpenFilename Method - Page 437
The Excel GetSaveAsFilename Method - Page 438
Prompting for a Directory - Page 439
Prompting for a Directory - Page 440
Prompting for a Directory - Page 441
Displaying a Data Form - Page 442
Displaying a Data Form - Page 443
Displaying a Data Form - Page 444
Chapter 13: Introducing UserForms - Page 445
Inserting a New UserForm - Page 446
Adding Controls to a UserForm - Page 447
Toolbox Controls - Page 448
Toolbox Controls - Page 449
Toolbox Controls - Page 450
Toolbox Controls - Page 451
Adjusting UserForm Controls - Page 452
Adjusting UserForm Controls - Page 453
Adjusting UserForm Controls - Page 454
Adjusting UserForm Controls - Page 455
Adjusting UserForm Controls - Page 456
Adjusting UserForm Controls - Page 457
Displaying a UserForm - Page 458
Displaying a UserForm - Page 459
Closing a UserForm - Page 460
Creating a UserForm: An Example - Page 461
Creating a UserForm: An Example - Page 462
Creating a UserForm: An Example - Page 463
Creating a UserForm: An Example - Page 464
Creating a UserForm: An Example - Page 465
Creating a UserForm: An Example - Page 466
Creating a UserForm: An Example - Page 467
Understanding UserForm Events - Page 468
Understanding UserForm Events - Page 469
Understanding UserForm Events - Page 470
Understanding UserForm Events - Page 471
Understanding UserForm Events - Page 472
Understanding UserForm Events - Page 473
Referencing UserForm Controls - Page 474
Referencing UserForm Controls - Page 475
Customizing the Toolbox - Page 476
Customizing the Toolbox - Page 477
Creating UserForm Templates - Page 478
A UserForm Checklist - Page 479
A UserForm Checklist - Page 480
Chapter 14: UserForm Examples - Page 481
Chapter 14: UserForm Examples - Page 482
Selecting Ranges from a UserForm - Page 483
Selecting Ranges from a UserForm - Page 484
Creating a Splash Screen - Page 485
Creating a Splash Screen - Page 486
Disabling a UserForm’s Close Button - Page 487
Changing a UserForm’s Size - Page 488
Changing a UserForm’s Size - Page 489
Zooming and Scrolling a Sheet from a UserForm - Page 490
Zooming and Scrolling a Sheet from a UserForm - Page 491
ListBox Techniques - Page 492
ListBox Techniques - Page 493
ListBox Techniques - Page 494
ListBox Techniques - Page 495
ListBox Techniques - Page 496
ListBox Techniques - Page 497
ListBox Techniques - Page 498
ListBox Techniques - Page 499
ListBox Techniques - Page 500
ListBox Techniques - Page 501
ListBox Techniques - Page 502
ListBox Techniques - Page 503
ListBox Techniques - Page 504
ListBox Techniques - Page 505
ListBox Techniques - Page 506
ListBox Techniques - Page 507
ListBox Techniques - Page 508
ListBox Techniques - Page 509
ListBox Techniques - Page 510
Using the MultiPage Control in a UserForm - Page 511
Using an External Control - Page 512
Using an External Control - Page 513
Using an External Control - Page 514
Animating a Label - Page 515
Animating a Label - Page 516
Animating a Label - Page 517
Animating a Label - Page 518
Chapter 15: Advanced UserForm Techniques - Page 519
Chapter 15: Advanced UserForm Techniques - Page 520
Chapter 15: Advanced UserForm Techniques - Page 521
Chapter 15: Advanced UserForm Techniques - Page 522
Displaying a Progress Indicator - Page 523
Displaying a Progress Indicator - Page 524
Displaying a Progress Indicator - Page 525
Displaying a Progress Indicator - Page 526
Displaying a Progress Indicator - Page 527
Displaying a Progress Indicator - Page 528
Displaying a Progress Indicator - Page 529
Displaying a Progress Indicator - Page 530
Displaying a Progress Indicator - Page 531
Displaying a Progress Indicator - Page 532
Creating Wizards - Page 533
Creating Wizards - Page 534
Creating Wizards - Page 535
Creating Wizards - Page 536
Creating Wizards - Page 537
Creating Wizards - Page 538
Emulating the MsgBox Function - Page 539
Emulating the MsgBox Function - Page 540
Emulating the MsgBox Function - Page 541
Emulating the MsgBox Function - Page 542
A UserForm with Movable Controls - Page 543
A UserForm with No Title Bar - Page 544
Simulating a Toolbar with a UserForm - Page 545
Simulating a Toolbar with a UserForm - Page 546
A Resizable UserForm - Page 547
A Resizable UserForm - Page 548
A Resizable UserForm - Page 549
A Resizable UserForm - Page 550
A Resizable UserForm - Page 551
Handling Multiple UserForm Controls with One Event Handler - Page 552
Handling Multiple UserForm Controls with One Event Handler - Page 553
Handling Multiple UserForm Controls with One Event Handler - Page 554
Selecting a Color in a UserForm - Page 555
Selecting a Color in a UserForm - Page 556
Displaying a Chart in a UserForm - Page 557
Making a UserForm Semitransparent - Page 558
Making a UserForm Semitransparent - Page 559
An Enhanced Data Form - Page 560
An Enhanced Data Form - Page 561
An Enhanced Data Form - Page 562
A Puzzle on a UserForm - Page 563
Video Poker on a UserForm - Page 564
Video Poker on a UserForm - Page 565
Video Poker on a UserForm - Page 566
Part V: Advanced Programming Techniques - Page 567
Part V: Advanced Programming Techniques - Page 568
Chapter 16: Developing Excel Utilities with VBA - Page 569
Using VBA to Develop Utilities - Page 570
What Makes a Good Utility? - Page 571
What Makes a Good Utility? - Page 572
What Makes a Good Utility? - Page 573
What Makes a Good Utility? - Page 574
What Makes a Good Utility? - Page 575
What Makes a Good Utility? - Page 576
What Makes a Good Utility? - Page 577
What Makes a Good Utility? - Page 578
What Makes a Good Utility? - Page 579
What Makes a Good Utility? - Page 580
What Makes a Good Utility? - Page 581
What Makes a Good Utility? - Page 582
What Makes a Good Utility? - Page 583
What Makes a Good Utility? - Page 584
What Makes a Good Utility? - Page 585
What Makes a Good Utility? - Page 586
What Makes a Good Utility? - Page 587
What Makes a Good Utility? - Page 588
More about Excel Utilities - Page 589
More about Excel Utilities - Page 590
Chapter 17: Working with Pivot Tables - Page 591
Chapter 17: Working with Pivot Tables - Page 592
Chapter 17: Working with Pivot Tables - Page 593
Chapter 17: Working with Pivot Tables - Page 594
Chapter 17: Working with Pivot Tables - Page 595
Chapter 17: Working with Pivot Tables - Page 596
Creating a More Complex Pivot Table - Page 597
Creating a More Complex Pivot Table - Page 598
Creating a More Complex Pivot Table - Page 599
Creating a More Complex Pivot Table - Page 600
Creating a More Complex Pivot Table - Page 601
Creating Multiple Pivot Tables - Page 602
Creating Multiple Pivot Tables - Page 603
Creating Multiple Pivot Tables - Page 604
Creating a Reverse Pivot Table - Page 605
Creating a Reverse Pivot Table - Page 606
Creating a Reverse Pivot Table - Page 607
Creating a Reverse Pivot Table - Page 608
Chapter 18: Working with Charts - Page 609
Chapter 18: Working with Charts - Page 610
Chapter 18: Working with Charts - Page 611
Creating an Embedded Chart - Page 612
Creating an Embedded Chart - Page 613
Creating a Chart on a Chart Sheet - Page 614
Using VBA to Activate a Chart - Page 615
Moving a Chart - Page 616
Using VBA to Deactivate a Chart - Page 617
Determining Whether a Chart Is Activated - Page 618
Deleting from the ChartObjects or Charts Collection - Page 619
Looping through All Charts - Page 620
Looping through All Charts - Page 621
Sizing and Aligning ChartObjects - Page 622
Sizing and Aligning ChartObjects - Page 623
Exporting a Chart - Page 624
Exporting a Chart - Page 625
Changing the Data Used in a Chart - Page 626
Changing the Data Used in a Chart - Page 627
Changing the Data Used in a Chart - Page 628
Changing the Data Used in a Chart - Page 629
Changing the Data Used in a Chart - Page 630
Changing the Data Used in a Chart - Page 631
Using VBA to Display Arbitrary Data Labels on a Chart - Page 632
Using VBA to Display Arbitrary Data Labels on a Chart - Page 633
Using VBA to Display Arbitrary Data Labels on a Chart - Page 634
Displaying a Chart in a UserForm - Page 635
Displaying a Chart in a UserForm - Page 636
Understanding Chart Events - Page 637
Understanding Chart Events - Page 638
Understanding Chart Events - Page 639
Understanding Chart Events - Page 640
Understanding Chart Events - Page 641
Understanding Chart Events - Page 642
Understanding Chart Events - Page 643
Discovering VBA Charting Tricks - Page 644
Discovering VBA Charting Tricks - Page 645
Discovering VBA Charting Tricks - Page 646
Discovering VBA Charting Tricks - Page 647
Discovering VBA Charting Tricks - Page 648
Discovering VBA Charting Tricks - Page 649
Discovering VBA Charting Tricks - Page 650
Animating Charts - Page 651
Animating Charts - Page 652
Animating Charts - Page 653
Animating Charts - Page 654
Animating Charts - Page 655
Animating Charts - Page 656
Creating an Interactive Chart without VBA - Page 657
Creating an Interactive Chart without VBA - Page 658
Creating an Interactive Chart without VBA - Page 659
Creating an Interactive Chart without VBA - Page 660
Working with Sparkline Charts - Page 661
Working with Sparkline Charts - Page 662
Working with Sparkline Charts - Page 663
Working with Sparkline Charts - Page 664
Chapter 19: Understanding Excel’s Events - Page 665
Chapter 19: Understanding Excel’s Events - Page 666
Chapter 19: Understanding Excel’s Events - Page 667
Chapter 19: Understanding Excel’s Events - Page 668
Chapter 19: Understanding Excel’s Events - Page 669
Chapter 19: Understanding Excel’s Events - Page 670
Chapter 19: Understanding Excel’s Events - Page 671
Getting Acquainted with Workbook-Level Events - Page 672
Getting Acquainted with Workbook-Level Events - Page 673
Getting Acquainted with Workbook-Level Events - Page 674
Getting Acquainted with Workbook-Level Events - Page 675
Getting Acquainted with Workbook-Level Events - Page 676
Getting Acquainted with Workbook-Level Events - Page 677
Getting Acquainted with Workbook-Level Events - Page 678
Getting Acquainted with Workbook-Level Events - Page 679
Examining Worksheet Events - Page 680
Examining Worksheet Events - Page 681
Examining Worksheet Events - Page 682
Examining Worksheet Events - Page 683
Examining Worksheet Events - Page 684
Examining Worksheet Events - Page 685
Examining Worksheet Events - Page 686
Examining Worksheet Events - Page 687
Checking Out Chart Events - Page 688
Checking Out Chart Events - Page 689
Monitoring with Application Events - Page 690
Monitoring with Application Events - Page 691
Monitoring with Application Events - Page 692
Monitoring with Application Events - Page 693
Monitoring with Application Events - Page 694
Using UserForm Events - Page 695
Accessing Events Not Associated with an Object - Page 696
Accessing Events Not Associated with an Object - Page 697
Accessing Events Not Associated with an Object - Page 698
Accessing Events Not Associated with an Object - Page 699
Accessing Events Not Associated with an Object - Page 700
Accessing Events Not Associated with an Object - Page 701
Accessing Events Not Associated with an Object - Page 702
Chapter 20: Interacting with Other Applications - Page 703
Chapter 20: Interacting with Other Applications - Page 704
Chapter 20: Interacting with Other Applications - Page 705
Chapter 20: Interacting with Other Applications - Page 706
Activating an Application with Excel - Page 707
Activating an Application with Excel - Page 708
Running Control Panel Dialog Boxes - Page 709
Using Automation in Excel - Page 710
Using Automation in Excel - Page 711
Using Automation in Excel - Page 712
Using Automation in Excel - Page 713
Using Automation in Excel - Page 714
Using Automation in Excel - Page 715
Using Automation in Excel - Page 716
Using Automation in Excel - Page 717
Using Automation in Excel - Page 718
Using Automation in Excel - Page 719
Using Automation in Excel - Page 720
Sending Personalized E-Mail via Outlook - Page 721
Sending Personalized E-Mail via Outlook - Page 722
Sending Personalized E-Mail via Outlook - Page 723
Sending E-Mail Attachments from Excel - Page 724
Sending E-Mail Attachments from Excel - Page 725
Sending E-Mail Attachments from Excel - Page 726
Using SendKeys - Page 727
Using SendKeys - Page 728
Chapter 21: Creating and Using Add-Ins - Page 729
Chapter 21: Creating and Using Add-Ins - Page 730
Chapter 21: Creating and Using Add-Ins - Page 731
Understanding Excel’s Add-In Manager - Page 732
Creating an Add-in - Page 733
An Add-In Example - Page 734
An Add-In Example - Page 735
An Add-In Example - Page 736
An Add-In Example - Page 737
An Add-In Example - Page 738
An Add-In Example - Page 739
Comparing XLAM and XLSM Files - Page 740
Comparing XLAM and XLSM Files - Page 741
Comparing XLAM and XLSM Files - Page 742
Comparing XLAM and XLSM Files - Page 743
Comparing XLAM and XLSM Files - Page 744
Comparing XLAM and XLSM Files - Page 745
Comparing XLAM and XLSM Files - Page 746
Manipulating Add-Ins with VBA - Page 747
Manipulating Add-Ins with VBA - Page 748
Manipulating Add-Ins with VBA - Page 749
Manipulating Add-Ins with VBA - Page 750
Manipulating Add-Ins with VBA - Page 751
Optimizing the Performance of Add-ins - Page 752
Special Problems with Add-Ins - Page 753
Special Problems with Add-Ins - Page 754
Special Problems with Add-Ins - Page 755
Special Problems with Add-Ins - Page 756
Part VI: Developing Applications - Page 757
Part VI: Developing Applications - Page 758
Chapter 22: Working with the Ribbon - Page 759
Chapter 22: Working with the Ribbon - Page 760
Chapter 22: Working with the Ribbon - Page 761
Chapter 22: Working with the Ribbon - Page 762
Using VBA with the Ribbon - Page 763
Using VBA with the Ribbon - Page 764
Using VBA with the Ribbon - Page 765
Using VBA with the Ribbon - Page 766
Using VBA with the Ribbon - Page 767
Using VBA with the Ribbon - Page 768
Customizing the Ribbon - Page 769
Customizing the Ribbon - Page 770
Customizing the Ribbon - Page 771
Customizing the Ribbon - Page 772
Customizing the Ribbon - Page 773
Customizing the Ribbon - Page 774
Customizing the Ribbon - Page 775
Customizing the Ribbon - Page 776
Customizing the Ribbon - Page 777
Customizing the Ribbon - Page 778
Customizing the Ribbon - Page 779
Customizing the Ribbon - Page 780
Customizing the Ribbon - Page 781
Customizing the Ribbon - Page 782
Customizing the Ribbon - Page 783
Customizing the Ribbon - Page 784
Customizing the Ribbon - Page 785
Customizing the Ribbon - Page 786
Customizing the Ribbon - Page 787
Customizing the Ribbon - Page 788
Customizing the Ribbon - Page 789
Creating an Old-Style Toolbar - Page 790
Creating an Old-Style Toolbar - Page 791
Creating an Old-Style Toolbar - Page 792
Creating an Old-Style Toolbar - Page 793
Creating an Old-Style Toolbar - Page 794
Chapter 23: Working with Shortcut Menus - Page 795
Chapter 23: Working with Shortcut Menus - Page 796
Chapter 23: Working with Shortcut Menus - Page 797
Chapter 23: Working with Shortcut Menus - Page 798
Chapter 23: Working with Shortcut Menus - Page 799
Chapter 23: Working with Shortcut Menus - Page 800
Chapter 23: Working with Shortcut Menus - Page 801
Chapter 23: Working with Shortcut Menus - Page 802
Using VBA to Customize Shortcut Menus - Page 803
Using VBA to Customize Shortcut Menus - Page 804
Using VBA to Customize Shortcut Menus - Page 805
Using VBA to Customize Shortcut Menus - Page 806
Using VBA to Customize Shortcut Menus - Page 807
Using VBA to Customize Shortcut Menus - Page 808
Shortcut Menus and Events - Page 809
Shortcut Menus and Events - Page 810
Shortcut Menus and Events - Page 811
Shortcut Menus and Events - Page 812
Shortcut Menus and Events - Page 813
Shortcut Menus and Events - Page 814
Chapter 24: Providing Help for Your Applications - Page 815
Help Systems That Use Excel Components - Page 816
Help Systems That Use Excel Components - Page 817
Help Systems That Use Excel Components - Page 818
Help Systems That Use Excel Components - Page 819
Help Systems That Use Excel Components - Page 820
Help Systems That Use Excel Components - Page 821
Help Systems That Use Excel Components - Page 822
Help Systems That Use Excel Components - Page 823
Help Systems That Use Excel Components - Page 824
Displaying Help in a Web Browser - Page 825
Displaying Help in a Web Browser - Page 826
Using the HTML Help System - Page 827
Using the HTML Help System - Page 828
Using the HTML Help System - Page 829
Using the HTML Help System - Page 830
Associating a Help File with Your Application - Page 831
Associating a Help File with Your Application - Page 832
Associating a Help File with Your Application - Page 833
Associating a Help File with Your Application - Page 834
Chapter 25: Developing User-Oriented Applications - Page 835
Chapter 25: Developing User-Oriented Applications - Page 836
Chapter 25: Developing User-Oriented Applications - Page 837
Chapter 25: Developing User-Oriented Applications - Page 838
Chapter 25: Developing User-Oriented Applications - Page 839
Chapter 25: Developing User-Oriented Applications - Page 840
Chapter 25: Developing User-Oriented Applications - Page 841
Chapter 25: Developing User-Oriented Applications - Page 842
Chapter 25: Developing User-Oriented Applications - Page 843
Chapter 25: Developing User-Oriented Applications - Page 844
Chapter 25: Developing User-Oriented Applications - Page 845
Application Development Concepts - Page 846
Application Development Concepts - Page 847
Application Development Concepts - Page 848
Part VII: Other Topics - Page 849
Part VII: Other Topics - Page 850
Chapter 26: Compatibility Issues - Page 851
Types of Compatibility Problems - Page 852
Avoid Using New Features - Page 853
But Will It Work on a Mac? - Page 854
But Will It Work on a Mac? - Page 855
Dealing with 64-bit Excel - Page 856
Creating an International Application - Page 857
Creating an International Application - Page 858
Creating an International Application - Page 859
Creating an International Application - Page 860
Creating an International Application - Page 861
Creating an International Application - Page 862
Creating an International Application - Page 863
Creating an International Application - Page 864
Chapter 27: Manipulating Files with VBA - Page 865
Chapter 27: Manipulating Files with VBA - Page 866
Chapter 27: Manipulating Files with VBA - Page 867
Chapter 27: Manipulating Files with VBA - Page 868
Chapter 27: Manipulating Files with VBA - Page 869
Chapter 27: Manipulating Files with VBA - Page 870
Chapter 27: Manipulating Files with VBA - Page 871
Chapter 27: Manipulating Files with VBA - Page 872
Chapter 27: Manipulating Files with VBA - Page 873
Displaying Extended File Information - Page 874
Displaying Extended File Information - Page 875
Working with Text Files - Page 876
Working with Text Files - Page 877
Working with Text Files - Page 878
Working with Text Files - Page 879
Text File Manipulation Examples - Page 880
Text File Manipulation Examples - Page 881
Text File Manipulation Examples - Page 882
Text File Manipulation Examples - Page 883
Text File Manipulation Examples - Page 884
Text File Manipulation Examples - Page 885
Text File Manipulation Examples - Page 886
Text File Manipulation Examples - Page 887
Text File Manipulation Examples - Page 888
Text File Manipulation Examples - Page 889
Text File Manipulation Examples - Page 890
Zipping and Unzipping Files - Page 891
Zipping and Unzipping Files - Page 892
Zipping and Unzipping Files - Page 893
Working with ADO - Page 894
Working with ADO - Page 895
Working with ADO - Page 896
Chapter 28: Manipulating Visual Basic Components - Page 897
Chapter 28: Manipulating Visual Basic Components - Page 898
The IDE Object Model - Page 899
The IDE Object Model - Page 900
The IDE Object Model - Page 901
Displaying All Components in a VBA Project - Page 902
Listing All VBA Procedures in a Workbook - Page 903
Listing All VBA Procedures in a Workbook - Page 904
Replacing a Module with an Updated Version - Page 905
Replacing a Module with an Updated Version - Page 906
Using VBA to Write VBA Code - Page 907
Using VBA to Write VBA Code - Page 908
Adding Controls to a UserForm at Design Time - Page 909
Adding Controls to a UserForm at Design Time - Page 910
Adding Controls to a UserForm at Design Time - Page 911
Creating UserForms Programmatically - Page 912
Creating UserForms Programmatically - Page 913
Creating UserForms Programmatically - Page 914
Creating UserForms Programmatically - Page 915
Creating UserForms Programmatically - Page 916
Creating UserForms Programmatically - Page 917
Creating UserForms Programmatically - Page 918
Creating UserForms Programmatically - Page 919
Creating UserForms Programmatically - Page 920
Chapter 29: Understanding Class Modules - Page 921
Example: Creating a NumLock Class - Page 922
Example: Creating a NumLock Class - Page 923
Example: Creating a NumLock Class - Page 924
Example: Creating a NumLock Class - Page 925
Example: Creating a NumLock Class - Page 926
More about Class Modules - Page 927
More about Class Modules - Page 928
More about Class Modules - Page 929
Example: A CSV File Class - Page 930
Example: A CSV File Class - Page 931
Example: A CSV File Class - Page 932
Example: A CSV File Class - Page 933
Example: A CSV File Class - Page 934
Example: A CSV File Class - Page 935
Example: A CSV File Class - Page 936
Chapter 30: Working with Colors - Page 937
Chapter 30: Working with Colors - Page 938
Chapter 30: Working with Colors - Page 939
Chapter 30: Working with Colors - Page 940
Chapter 30: Working with Colors - Page 941
Understanding Grayscale - Page 942
Understanding Grayscale - Page 943
Understanding Grayscale - Page 944
Experimenting with Colors - Page 945
Experimenting with Colors - Page 946
Understanding Document Themes - Page 947
Understanding Document Themes - Page 948
Understanding Document Themes - Page 949
Understanding Document Themes - Page 950
Understanding Document Themes - Page 951
Understanding Document Themes - Page 952
Working with Shape Objects - Page 953
Working with Shape Objects - Page 954
Working with Shape Objects - Page 955
Working with Shape Objects - Page 956
Working with Shape Objects - Page 957
Working with Shape Objects - Page 958
Modifying Chart Colors - Page 959
Modifying Chart Colors - Page 960
Modifying Chart Colors - Page 961
Modifying Chart Colors - Page 962
Chapter 31: Frequently Asked Questions about Excel Programming - Page 963
General Excel Questions - Page 964
General Excel Questions - Page 965
General Excel Questions - Page 966
General Excel Questions - Page 967
The Visual Basic Editor - Page 968
The Visual Basic Editor - Page 969
Procedures - Page 970
Procedures - Page 971
Procedures - Page 972
Procedures - Page 973
Functions - Page 974
Functions - Page 975
Functions - Page 976
Objects, Properties, Methods, and Events - Page 977
Objects, Properties, Methods, and Events - Page 978
Objects, Properties, Methods, and Events - Page 979
Objects, Properties, Methods, and Events - Page 980
Objects, Properties, Methods, and Events - Page 981
Objects, Properties, Methods, and Events - Page 982
Objects, Properties, Methods, and Events - Page 983
Objects, Properties, Methods, and Events - Page 984
UserForms - Page 985
UserForms - Page 986
UserForms - Page 987
Add-Ins - Page 988
Add-Ins - Page 989
User Interface - Page 990
User Interface - Page 991
User Interface - Page 992
Part VIII: Appendixes - Page 993
Part VIII: Appendixes - Page 994
Appendix A: Excel Resources Online - Page 995
Appendix A: Excel Resources Online - Page 996
Internet Newsgroups - Page 997
Internet Newsgroups - Page 998
Internet Web sites - Page 999
Internet Web sites - Page 1000
Internet Web sites - Page 1001
Internet Web sites - Page 1002
Appendix B: VBA Statements and Functions Reference - Page 1003
Appendix B: VBA Statements and Functions Reference - Page 1004
Appendix B: VBA Statements and Functions Reference - Page 1005
Invoking Excel functions in VBA instructions - Page 1006
Invoking Excel functions in VBA instructions - Page 1007
Invoking Excel functions in VBA instructions - Page 1008
Invoking Excel functions in VBA instructions - Page 1009
Invoking Excel functions in VBA instructions - Page 1010
Appendix C: VBA Error Codes - Page 1011
Appendix C: VBA Error Codes - Page 1012
Appendix C: VBA Error Codes - Page 1013
Appendix C: VBA Error Codes - Page 1014
Appendix D: What’s on the CD-ROM - Page 1015
Files and Software on the CD - Page 1016
Files and Software on the CD - Page 1017
Files and Software on the CD - Page 1018
Files and Software on the CD - Page 1019
Files and Software on the CD - Page 1020
Files and Software on the CD - Page 1021
Files and Software on the CD - Page 1022
Files and Software on the CD - Page 1023
Files and Software on the CD - Page 1024
Files and Software on the CD - Page 1025
Files and Software on the CD - Page 1026
Files and Software on the CD - Page 1027
Files and Software on the CD - Page 1028
Files and Software on the CD - Page 1029
Files and Software on the CD - Page 1030
Troubleshooting - Page 1031