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 xxvii Part I: Introduction to Excel VBA Chapter 1: Essentials of Spreadsheet Application Development 3 What Is a Spreadsheet Application? 3 Steps for Application Development 4 Determining User Needs 5 Planning an Application That Meets User Needs 6 Determining the Most Appropriate User Interface 8 Concerning Yourself with the End User 12 Other Development Issues 17 Chapter 2: Introducing Visual Basic for Applications 19 Getting a Head Start with the Macro Recorder 19 Working with the Visual Basic Editor 32 VBA Fundamentals 42 Deep Dive: Working with Range Objects 47 Essential Concepts to Remember 53 Dont PanicYou Are Not Alone55 Chapter 3: VBA Programming Fundamentals 61 VBA Language Elements: An Overview 61 Comments 63 Variables, Data Types, and Constants 64 Assignment Statements 75 Arrays 77 Object Variables79 User-Defined Data Types 80 Built-In Functions 81 Manipulating Objects and Collections 84 Controlling Code Execution 87 Chapter 4: Working with VBA Sub Procedures 105 About Procedures 105 Executing Sub Procedures 108 Passing Arguments to Procedures 119 Error-Handling Techniques 123 A Realistic Example That Uses Sub Procedures 127 Chapter 5: Creating Function Procedures 143 Sub Procedures versus Function Procedures 143 Why Create Custom Functions? 144 An Introductory Function Example 144 Function Procedures 148 Function Arguments 153 Function Examples 153 Emulating Excels SUM Function 168 Extended Date Functions 171 Debugging Functions 173 Dealing with the Insert Function Dialog Box 174 Using Add-Ins to Store Custom Functions 178 Using the Windows API 179 Chapter 6: Understanding Excels Events 183 What You Should Know about Events 183 Getting Acquainted with Workbook-Level Events 190 Examining Worksheet Events 197 Monitoring with Application Events 206 Accessing Events Not Associated with an Object 210 Chapter 7: VBA Programming Examples and Techniques 217 Learning by Example 217 Working with Ranges 218 Working with Workbooks and Sheets 246 VBA Techniques 251 Some Useful Functions for Use in Your Code 259 Some Useful Worksheet Functions 264 Windows API Calls 280 Part II: Advanced VBA Techniques Chapter 8: Working with Pivot Tables 291 An Introductory Pivot Table Example 291 Creating a More Complex Pivot Table 297 Creating Multiple Pivot Tables 301 Creating a Reverse Pivot Table 304 Chapter 9: Working with Charts 307 Getting the Inside Scoop on Charts 307 Creating an Embedded Chart 310 Creating a Chart on a Chart Sheet 311 Modifying Charts 312 Using VBA to Activate a Chart 313 Moving a Chart 314 Using VBA to Deactivate a Chart 315 Determining Whether a Chart Is Activated 316 Deleting from the ChartObjects or Charts Collection 316 Looping through All Charts 318 Sizing and Aligning ChartObjects 320 Creating Lots of Charts 321 Exporting a Chart 324 Changing the Data Used in a Chart 326 Using VBA to Display Arbitrary Data Labels on a Chart 331 Displaying a Chart in a UserForm 335 Understanding Chart Events 337 Discovering VBA Charting Tricks 344 Working with Sparkline Charts 351 Chapter 10: Interacting with Other Applications 355 Understanding Microsoft Office Automation 355 Automating Access from Excel 359 Automating Word from Excel 361 Automating PowerPoint from Excel 364 Automating Outlook from Excel 369 Starting Other Applications from Excel 373 Chapter 11: Working with External Data and Files 379 Working with External Data Connections 379 Using ADO and VBA to Pull External Data 387 Working with Text Files 394 Text File Manipulation Examples 398 Performing Common File Operations 402 Zipping and Unzipping Files 410 Part III: Working with UserForms Chapter 12: Leveraging Custom Dialog Boxes 415 Before You Create That UserForm 415 Using an Input Box415 The VBA MsgBox Function 421 The Excel Ge