Excel 2016 Power Programming with VBA

Michael Alexander is a Microsoft Certified Application Developer and author of several books on Microsoft Access and Excel. He runs a free tutorial site at datapigtechnologies.com. Dick Kusleika has been helping users get the most out of Microsoft Office products for more than fifteen years through online forums, blogging, books, and conferences. John Walkenbach is arguably the foremost authority on Excel. He has written more than 30 books and maintains the popular Spreadsheet Page at http://spreadsheetpage.com

• Introduction xxviiPart I: Introduction to Excel VBAChapter 1: Essentials of Spreadsheet Application Development 3What Is a Spreadsheet Application? 3Steps for Application Development 4Determining User Needs 5Planning an Application That Meets User Needs 6Determining the Most Appropriate User Interface 8Concerning Yourself with the End User 12Other Development Issues 17Chapter 2: Introducing Visual Basic for Applications 19Getting a Head Start with the Macro Recorder 19Working with the Visual Basic Editor 32VBA Fundamentals 42Deep Dive: Working with Range Objects 47Essential Concepts to Remember 53Don’t Panic—You Are Not Alone55Chapter 3: VBA Programming Fundamentals 61VBA Language Elements: An Overview 61Comments 63Variables, Data Types, and Constants 64Assignment Statements 75Arrays 77Object Variables79User-Defined Data Types 80Built-In Functions 81Manipulating Objects and Collections 84Controlling Code Execution 87Chapter 4: Working with VBA Sub Procedures 105About Procedures 105Executing Sub Procedures 108Passing Arguments to Procedures 119Error-Handling Techniques 123A Realistic Example That Uses Sub Procedures 127Chapter 5: Creating Function Procedures 143Sub Procedures versus Function Procedures 143Why Create Custom Functions? 144An Introductory Function Example 144Function Procedures 148Function Arguments 153Function Examples 153Emulating Excel’s SUM Function 168Extended Date Functions 171Debugging Functions 173Dealing with the Insert Function Dialog Box 174Using Add-Ins to Store Custom Functions 178Using the Windows API 179Chapter 6: Understanding Excel’s Events 183What You Should Know about Events 183Getting Acquainted with Workbook-Level Events 190Examining Worksheet Events 197Monitoring with Application Events 206Accessing Events Not Associated with an Object 210Chapter 7: VBA Programming Examples and Techniques 217Learning by Example 217Working with Ranges 218Working with Workbooks and Sheets 246VBA Techniques 251Some Useful Functions for Use in Your Code 259Some Useful Worksheet Functions 264Windows API Calls 280Part II: Advanced VBA TechniquesChapter 8: Working with Pivot Tables 291An Introductory Pivot Table Example 291Creating a More Complex Pivot Table 297Creating Multiple Pivot Tables 301Creating a Reverse Pivot Table 304Chapter 9: Working with Charts 307Getting the Inside Scoop on Charts 307Creating an Embedded Chart 310Creating a Chart on a Chart Sheet 311Modifying Charts 312Using VBA to Activate a Chart 313Moving a Chart 314Using VBA to Deactivate a Chart 315Determining Whether a Chart Is Activated 316Deleting from the ChartObjects or Charts Collection 316Looping through All Charts 318Sizing and Aligning ChartObjects 320Creating Lots of Charts 321Exporting a Chart 324Changing the Data Used in a Chart 326Using VBA to Display Arbitrary Data Labels on a Chart 331Displaying a Chart in a UserForm 335Understanding Chart Events 337Discovering VBA Charting Tricks 344Working with Sparkline Charts 351Chapter 10: Interacting with Other Applications 355Understanding Microsoft Office Automation 355Automating Access from Excel 359Automating Word from Excel 361Automating PowerPoint from Excel 364Automating Outlook from Excel 369Starting Other Applications from Excel 373Chapter 11: Working with External Data and Files 379Working with External Data Connections 379Using ADO and VBA to Pull External Data 387Working with Text Files 394Text File Manipulation Examples 398Performing Common File Operations 402Zipping and Unzipping Files 410Part III: Working with UserFormsChapter 12: Leveraging Custom Dialog Boxes 415Before You Create That UserForm 415Using an Input Box415The VBA MsgBox Function 421The Excel GetOpenFilename Method 426The Excel GetSaveAsFilename Method 429Prompting for a Directory  430Displaying Excel’s Built-In Dialog Boxes 430Displaying a Data Form 433Chapter 13: Introducing UserForms 435How Excel Handles Custom Dialog Boxes 435Inserting a New UserForm 436Adding Controls to a UserForm 437Toolbox Controls  437Adjusting UserForm Controls 442Adjusting a Control’s Properties 443Displaying a UserForm 449Closing a UserForm 451Creating a UserForm: An Example 453Understanding UserForm Events 459Referencing UserForm Controls 465Customizing the Toolbox 466Creating UserForm Templates 469A UserForm Checklist 469Chapter 14: UserForm Examples 471Creating a UserForm “Menu” 471Selecting Ranges from a UserForm  474Creating a Splash Screen 476Disabling a UserForm's Close Button 478Changing a UserForm's Size 479Zooming and Scrolling a Sheet from a UserForm 480ListBox Techniques 482Using the MultiPage Control in a UserForm 503Using an External Control 504Animating a Label 506Chapter 15: Advanced UserForm Techniques 511A Modeless Dialog Box 512Displaying a Progress Indicator 516Creating Wizards 527Emulating the MsgBox Function 533A UserForm with Movable Controls 537A UserForm with No Title Bar 538Simulating a Toolbar with a UserForm 540Emulating a Task Pane with a UserForm 542A Resizable UserForm 543Handling Multiple UserForm Controls with One Event Handler 547Selecting a Color in a UserForm 550Displaying a Chart in a UserForm 552Making a UserForm Semitransparent 554A Puzzle on a UserForm 556Video Poker on a UserForm 557Part IV: Developing Excel ApplicationsChapter 16: Creating and Using Add-Ins 561What Is an Add-In? 561Understanding Excel’s Add-In Manager 564Creating an Add-in 566An Add-In Example 567Comparing XLAM and XLSM Files 572Manipulating Add-Ins with VBA 578Optimizing the Performance of Add-Ins 584Special Problems with Add-Ins 585Chapter 17: Working with the Ribbon 589Ribbon Basics 589Customizing the Ribbon 591Creating a Custom Ribbon 596Using VBA with the Ribbon 617Creating an Old-Style Toolbar 621Chapter 18: Working with Shortcut Menus 625CommandBar Overview 625Using VBA to Customize Shortcut Menus 632Shortcut Menus and Events 642Chapter 19: Providing Help for Your Applications 647Help for Your Excel Applications 647Help Systems That Use Excel Components 649Displaying Help in a Web Browser 657Using the HTML Help System 659Chapter 20: Leveraging Class Modules 665What Is a Class Module? 665Creating a NumLock Class 667Coding Properties, Methods, and Events 671Exposing a QueryTable Event 674Creating a Class to Hold Classes 677Chapter 21: Understanding Compatibility Issues 685What Is Compatibility? 685Types of Compatibility Problems 686Avoid Using New Features 687But Will It Work on a Mac? 689Dealing with 64-Bit Excel 690Creating an International Application 691Part V: AppendixAppendix A: VBA Statements and Function Reference 701Index 709