Excel 2019 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 twenty-five years through online forums, blogging, books, and conferences.

• Introduction xxxiiiPart I: Introduction to Excel VBA 1Chapter 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 7Concerning 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 43Deep Dive: Working with Range Objects 48Essential Concepts to Remember 52Don’t Panic–You Are Not Alone 54Chapter 3: VBA Programming Fundamentals 61VBA Language Elements: An Overview 61Comments 63Variables, Data Types, and Constants 65Assignment Statements 76Arrays 78Declaring Arrays 78Object Variables 80User-Defined Data Types 81Built-in Functions 82Manipulating Objects and Collections 85Controlling Code Execution 88Chapter 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 127Utility Availability 140Evaluating the Project 141Chapter 5: Creating Function Procedures 143Sub Procedures vs. Function Procedures 143Why Create Custom Functions? 144An Introductory Function Example 144Function Procedures 148Function Arguments 153Function Examples 153Emulating Excel’s SUM Function 167Extended Date Functions 170Debugging Functions 172Dealing with the Insert Function Dialog Box 173Using Add-Ins to Store Custom Functions 178Using the Windows API 178Chapter 6: Understanding Excel’s Events 183What You Should Know About Events 183Getting Acquainted with Workbook-Level Events 189Examining Worksheet Events 197Monitoring with Application Events 206Chapter 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 258Some Useful Worksheet Functions 263Windows API Calls 278Part II: Advanced VBA Techniques 287Chapter 8: Working with Pivot Tables 289An Introductory Pivot Table Example 289Creating a More Complex Pivot Table 295Creating Multiple Pivot Tables 299Creating a Reverse Pivot Table 302Chapter 9: Working with Charts 305Getting the Inside Scoop on Charts 305Creating an Embedded Chart 308Creating a Chart on a Chart Sheet 309Modifying Charts 309Using VBA to Activate a Chart 310Moving a Chart 311Using VBA to Deactivate a Chart 312Determining Whether a Chart Is Activated 313Deleting from the ChartObjects or Charts Collection 313Looping Through All Charts 314Sizing and Aligning ChartObjects 317Creating Lots of Charts 318Exporting a Chart 321Changing the Data Used in a Chart 322Using VBA to Display Custom Data Labels on a Chart 328Displaying a Chart in a UserForm 331Understanding Chart Events 334Discovering VBA Charting Tricks 340Working with Sparkline Charts 347Chapter 10: Interacting with Other Applications 351Understanding Microsoft Office Automation 351Automating Access from Excel 354Automating Word from Excel 356Automating PowerPoint from Excel 360Automating Outlook from Excel 365Starting Other Applications from Excel 369Chapter 11: Working with External Data and Files 377Working with External Data Connections 377Power Query Basics 377Using ADO and VBA to Pull External Data 390Working with Text Files 397Text File Manipulation Examples 401Performing Common File Operations 405Zipping and Unzipping Files 413Part III: Working with UserForms 417Chapter 12: Leveraging Custom Dialog Boxes 419Alternatives to UserForms 419Using an Input Box 419Using the VBA MsgBox Function 426Using the Excel GetOpenFilename Method 431Using the Excel GetSaveAsFilename Method 434Prompting for a Folder 435Displaying Excel’s Built-in Dialog Boxes 435Displaying a Data Form 438Chapter 13: Introducing UserForms 441How Excel Handles Custom Dialog Boxes 441Inserting a New UserForm 442Adding Controls to a UserForm 443Toolbox Controls 443Adjusting UserForm Controls 448Adjusting a Control’s Properties 450Displaying a UserForm 456Closing a UserForm 458Creating a UserForm: An Example 460Referencing UserForm Controls 473Customizing the Toolbox 474Creating UserForm Templates 477A UserForm Checklist 478Chapter 14: Looking at UserForm Examples 479Creating a UserForm “Menu” 479Selecting Ranges from a UserForm 481Creating a Splash Screen 483Disabling a UserForm’s Close Button 486Changing a UserForm’s Size 487Zooming and Scrolling a Sheet from a UserForm 488Exploring ListBox Techniques 490Using the MultiPage Control in a UserForm 512Using an External Control 513Animating a Label 516Chapter 15: Implementing Advanced UserForm Techniques 519A Modeless Dialog Box 519Displaying a Progress Indicator 523Creating Wizards 534Emulating the MsgBox Function 541A UserForm with Movable Controls 545A UserForm with No Title Bar 546Simulating a Toolbar with a UserForm 548Emulating a Task Pane with a UserForm 550A Resizable UserForm 551Handling Multiple UserForm Controls with One Event Handler 556Selecting a Color in a UserForm 559Displaying a Chart in a UserForm 561Making a UserForm Semitransparent 562A Puzzle on a UserForm 563Video Poker on a UserForm 565Part IV: Developing Excel Applications 567Chapter 16: Creating and Using Add-Ins 569What Is an Add-In? 569Understanding Excel’s Add-in Manager 572Creating an Add-In 574An Add-In Example 575Comparing XLAM and XLSM Files 581Manipulating Add-Ins with VBA 587Optimizing the Performance of Add-Ins 593Special Problems with Add-Ins 594Chapter 17: Working with the Ribbon 599Ribbon Basics 599Customizing the Ribbon 601Creating a Custom Ribbon 606Using VBA with the Ribbon 628Creating an Old-Style Toolbar 632Chapter 18: Working with Shortcut Menus 637CommandBar Overview 637Referring to Controls in a CommandBar 640Properties of CommandBar Controls 641Displaying All Shortcut Menu Items 642Using VBA to Customize Shortcut Menus 644Resetting a shortcut menu 646Shortcut Menus and Events 654Chapter 19: Providing Help for Your Applications 659Help for Your Excel Applications 659Help Systems That Use Excel Components 661Displaying Help in a Web Browser 670Using the HTML Help System 672Chapter 20: Leveraging Class Modules 679What Is a Class Module? 679Creating a NumLock Class 681Coding Properties, Methods, and Events 685Exposing a QueryTable Event 688Creating a Class to Hold Classes 692Chapter 21: Understanding Compatibility Issues 699What Is Compatibility? 699Types of Compatibility Problems 699Avoid Using New Features 701But Will It Work on a Mac? 703Dealing with 64-Bit Excel 704Creating an International Application 705Multilanguage Applications 707VBA Language Considerations 708Using Local Properties 708Identifying System Settings 709Date and Time Settings 711Part V: Appendix 713Appendix: VBA Statements and Functions Reference 715Index 725