Excel 2010 Power Programming with VBA

Excel® 2010 Power Programming with VBA......Page 1
Contents at a Glance......Page 7
Table of Contents......Page 9
What You Need to Know......Page 27
Conventions in This Book......Page 28
What the Icons Mean......Page 30
How This Book Is Organized......Page 31
About the Companion CD-ROM......Page 32
Reach Out......Page 33
Part I: Some Essential Background......Page 35
A Brief History of Spreadsheets......Page 37
Why Excel Is Great for Developers......Page 46
Excel’s Role in Microsoft’s Strategy......Page 48
Thinking in Terms of Objects......Page 49
Workbooks......Page 50
Excel’s User Interface......Page 54
Customizing the Display......Page 63
Formulas, Functions, and Names......Page 64
Formatting......Page 66
Protection Options......Page 68
Charts......Page 70
Shapes and SmartArt......Page 71
Database Access......Page 72
Internet Features......Page 73
Analysis Tools......Page 74
File Format......Page 76
Excel’s Help System......Page 77
About Formulas......Page 79
Calculating Formulas......Page 80
Cell and Range References......Page 81
Using Names......Page 84
Formula Errors......Page 91
Array Formulas......Page 92
Counting and Summing Techniques......Page 95
Working with Dates and Times......Page 97
Creating Megaformulas......Page 100
Starting Excel......Page 103
File Types......Page 106
Working with Template Files......Page 109
Inside an Excel File......Page 113
The OfficeUI File......Page 117
The XLB File......Page 118
Add-In Files......Page 119
Excel Settings in the Registry......Page 120
Part II: Excel Application Development......Page 125
Spreadsheet Applications......Page 127
The Developer and the End User......Page 128
Solving Problems with Excel......Page 131
Basic Spreadsheet Types......Page 132
Steps for Application Development......Page 137
Determining User Needs......Page 138
Planning an Application That Meets User Needs......Page 139
Determining the Most Appropriate User Interface......Page 141
Concerning Yourself with the End User......Page 148
Other Development Issues......Page 155
Part III: Understanding Visual Basic for Applications......Page 159
Getting Some BASIC Background......Page 161
Delving in to VBA......Page 162
Covering the Basics of VBA......Page 163
Introducing the Visual Basic Editor......Page 166
Working with the Project Explorer......Page 169
Working with Code Windows......Page 171
Customizing the VBE Environment......Page 179
The Macro Recorder......Page 185
About Objects and Collections......Page 193
Properties and Methods......Page 195
The Comment Object: A Case Study......Page 198
Some Useful Application Properties......Page 206
Working with Range Objects......Page 208
Things to Know about Objects......Page 214
VBA Language Elements: An Overview......Page 219
Comments......Page 221
Variables, Data Types, and Constants......Page 223
Assignment Statements......Page 236
Arrays......Page 239
Object Variables......Page 241
User-Defined Data Types......Page 242
Built-in Functions......Page 243
Manipulating Objects and Collections......Page 246
Controlling Code Execution......Page 249
About Procedures......Page 267
Executing Sub Procedures......Page 270
Passing Arguments to Procedures......Page 281
Error-Handling Techniques......Page 285
A Realistic Example That Uses Sub Procedures......Page 290
Sub Procedures versus Function Procedures......Page 307
An Introductory Function Example......Page 308
Function Procedures......Page 313
Function Arguments......Page 318
Function Examples......Page 319
Emulating Excel’s SUM function......Page 334
Extended Date Functions......Page 337
Debugging Functions......Page 339
Dealing with the Insert Function Dialog Box......Page 340
Using Add-ins to Store Custom Functions......Page 345
Using the Windows API......Page 346
Learning by Example......Page 351
Working with Ranges......Page 352
Working with Workbooks and Sheets......Page 379
VBA Techniques......Page 383
Some Useful Functions for Use in Your Code......Page 391
Some Useful Worksheet Functions......Page 396
Windows API Calls......Page 412
Part IV: Working with UserForms......Page 423
Using an Input Box......Page 425
The VBA MsgBox Function......Page 430
The Excel GetOpenFilename Method......Page 435
The Excel GetSaveAsFilename Method......Page 438
Displaying Excel’s Built-In Dialog Boxes......Page 439
Displaying a Data Form......Page 442
How Excel Handles Custom Dialog Boxes......Page 445
Inserting a New UserForm......Page 446
Adding Controls to a UserForm......Page 447
Toolbox Controls......Page 448
Adjusting a Control’s Properties......Page 452
Displaying a UserForm......Page 458
Closing a UserForm......Page 460
Creating a UserForm: An Example......Page 461
Understanding UserForm Events......Page 468
Referencing UserForm Controls......Page 474
Customizing the Toolbox......Page 476
Creating UserForm Templates......Page 478
A UserForm Checklist......Page 479
Creating a UserForm “Menu”......Page 481
Selecting Ranges from a UserForm......Page 483
Creating a Splash Screen......Page 485
Disabling a UserForm’s Close Button......Page 487
Changing a UserForm’s Size......Page 488
Zooming and Scrolling a Sheet from a UserForm......Page 490
ListBox Techniques......Page 492
Using the MultiPage Control in a UserForm......Page 511
Using an External Control......Page 512
Animating a Label......Page 515
A Modeless Dialog Box......Page 519
Displaying a Progress Indicator......Page 523
Creating Wizards......Page 533
Emulating the MsgBox Function......Page 539
A UserForm with Movable Controls......Page 543
A UserForm with No Title Bar......Page 544
Simulating a Toolbar with a UserForm......Page 545
A Resizable UserForm......Page 547
Handling Multiple UserForm Controls with One Event Handler......Page 552
Selecting a Color in a UserForm......Page 555
Displaying a Chart in a UserForm......Page 557
Making a UserForm Semitransparent......Page 558
An Enhanced Data Form......Page 560
A Puzzle on a UserForm......Page 563
Video Poker on a UserForm......Page 564
Part V: Advanced Programming Techniques......Page 567
About Excel Utilities......Page 569
Using VBA to Develop Utilities......Page 570
Text Tools: The Anatomy of a Utility......Page 571
More about Excel Utilities......Page 589
An Introductory Pivot Table Example......Page 591
Creating a More Complex Pivot Table......Page 597
Creating Multiple Pivot Tables......Page 602
Creating a Reverse Pivot Table......Page 605
Getting the Inside Scoop on Charts......Page 609
Creating an Embedded Chart......Page 612
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
Sizing and Aligning ChartObjects......Page 622
Exporting a Chart......Page 624
Changing the Data Used in a Chart......Page 626
Using VBA to Display Arbitrary Data Labels on a Chart......Page 632
Displaying a Chart in a UserForm......Page 635
Understanding Chart Events......Page 637
Discovering VBA Charting Tricks......Page 644
Animating Charts......Page 651
Creating an Interactive Chart without VBA......Page 657
Working with Sparkline Charts......Page 661
What You Should Know about Events......Page 665
Getting Acquainted with Workbook-Level Events......Page 672
Examining Worksheet Events......Page 680
Checking Out Chart Events......Page 688
Monitoring with Application Events......Page 690
Using UserForm Events......Page 695
Accessing Events Not Associated with an Object......Page 696
Starting an Application from Excel......Page 703
Activating an Application with Excel......Page 707
Running Control Panel Dialog Boxes......Page 709
Using Automation in Excel......Page 710
Sending Personalized E-Mail via Outlook......Page 721
Sending E-Mail Attachments from Excel......Page 724
Using SendKeys......Page 727
What Is an Add-In?......Page 729
Understanding Excel’s Add-In Manager......Page 732
Creating an Add-in......Page 733
An Add-In Example......Page 734
Comparing XLAM and XLSM Files......Page 740
Manipulating Add-Ins with VBA......Page 747
Optimizing the Performance of Add-ins......Page 752
Special Problems with Add-Ins......Page 753
Part VI: Developing Applications......Page 757
Ribbon Basics......Page 759
Using VBA with the Ribbon......Page 763
Customizing the Ribbon......Page 769
Creating an Old-Style Toolbar......Page 790
CommandBar Overview......Page 795
Using VBA to Customize Shortcut Menus......Page 803
Shortcut Menus and Events......Page 809
Help for Your Excel Applications......Page 815
Help Systems That Use Excel Components......Page 816
Displaying Help in a Web Browser......Page 825
Using the HTML Help System......Page 827
Associating a Help File with Your Application......Page 831
The Loan Amortization Wizard......Page 835
Application Development Concepts......Page 846
Part VII: Other Topics......Page 849
What Is Compatibility?......Page 851
Types of Compatibility Problems......Page 852
Avoid Using New Features......Page 853
But Will It Work on a Mac?......Page 854
Dealing with 64-bit Excel......Page 856
Creating an International Application......Page 857
Performing Common File Operations......Page 865
Displaying Extended File Information......Page 874
Working with Text Files......Page 876
Text File Manipulation Examples......Page 880
Zipping and Unzipping Files......Page 891
Working with ADO......Page 894
Introducing the IDE......Page 897
The IDE Object Model......Page 899
Displaying All Components in a VBA Project......Page 902
Listing All VBA Procedures in a Workbook......Page 903
Replacing a Module with an Updated Version......Page 905
Using VBA to Write VBA Code......Page 907
Adding Controls to a UserForm at Design Time......Page 909
Creating UserForms Programmatically......Page 912
What is a Class Module?......Page 921
Example: Creating a NumLock Class......Page 922
More about Class Modules......Page 927
Example: A CSV File Class......Page 930
Specifying Colors......Page 937
Understanding Grayscale......Page 942
Experimenting with Colors......Page 945
Understanding Document Themes......Page 947
Working with Shape Objects......Page 953
Modifying Chart Colors......Page 959
Getting the Scoop on FAQs......Page 963
General Excel Questions......Page 964
The Visual Basic Editor......Page 968
Procedures......Page 970
Functions......Page 974
Objects, Properties, Methods, and Events......Page 977
UserForms......Page 985
Add-Ins......Page 988
User Interface......Page 990
Part VIII: Appendixes......Page 993
Microsoft Technical Support......Page 995
Internet Newsgroups......Page 997
Internet Web sites......Page 999
Appendix B: VBA Statements and Functions Reference......Page 1003
Invoking Excel functions in VBA instructions......Page 1006
Appendix C: VBA Error Codes......Page 1011
Using the CD......Page 1015
Files and Software on the CD......Page 1016
Troubleshooting......Page 1031
Index......Page 1033