Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 2

1  Cover

2  Introduction Topics Covered What You Need to Know What You Need to Have Conventions Used in This Book What the Icons Mean How This Book Is Organized How to Use This Book What's on the Website

3  Part I: Introduction to Excel VBA CHAPTER 1: Essentials of Spreadsheet Application Development What Is a Spreadsheet Application? Steps for Application Development Determining User Needs Planning an Application That Meets User Needs Determining the Most Appropriate User Interface Concerning Yourself with the End User Other Development Issues CHAPTER 2: Introducing Visual Basic for Applications Getting a Head Start with the Macro Recorder Working with the Visual Basic Editor VBA Fundamentals Deep Dive: Working with Range Objects Essential Concepts to Remember Don't Panic—You Are Not Alone CHAPTER 3: VBA Programming Fundamentals VBA Language Elements: An Overview Comments Variables, Data Types, and Constants Assignment Statements Arrays Declaring arrays Object Variables User-Defined Data Types Built-in Functions Manipulating Objects and Collections Controlling Code Execution CHAPTER 4: Working with VBA Sub Procedures About Procedures Executing Sub Procedures Passing Arguments to Procedures Error-Handling Techniques A Realistic Example That Uses Sub Procedures Utility availability Evaluating the project CHAPTER 5: Creating Function Procedures Sub Procedures vs. Function Procedures Why Create Custom Functions? An Introductory Function Example Function Procedures Function Arguments Function Examples Emulating Excel's SUM Function Extended Date Functions Debugging Functions Dealing with the Insert Function Dialog Box Using Add-Ins to Store Custom Functions Using the Windows API CHAPTER 6: Understanding Excel's Events What You Should Know About Events Getting Acquainted with Workbook-Level Events Examining Worksheet Events Monitoring with Application Events CHAPTER 7: VBA Programming Examples and Techniques Learning by Example Working with Ranges Working with Workbooks and Sheets VBA Techniques Some Useful Functions for Use in Your Code Some Useful Worksheet Functions Windows API Calls

4  Part II: Advanced VBA Techniques CHAPTER 8: Working with Pivot Tables An Introductory Pivot Table Example Creating a More Complex Pivot Table Creating Multiple Pivot Tables Creating a Reverse Pivot Table CHAPTER 9: Working with Charts Getting the Inside Scoop on Charts Creating an Embedded Chart Creating a Chart on a Chart Sheet Modifying Charts Using VBA to Activate a Chart Moving a Chart Using VBA to Deactivate a Chart Determining Whether a Chart Is Activated Deleting from the ChartObjects or Charts Collection Looping Through All Charts Sizing and Aligning ChartObjects Creating Lots of Charts Exporting a Chart Changing the Data Used in a Chart Using VBA to Display Custom Data Labels on a Chart Displaying a Chart in a UserForm Understanding Chart Events Discovering VBA Charting Tricks Working with Sparkline Charts CHAPTER 10: Interacting with Other Applications Understanding Microsoft Office Automation Automating Access from Excel Automating Word from Excel Automating PowerPoint from Excel Automating Outlook from Excel Starting Other Applications from Excel CHAPTER 11: Working with External Data and Files Working with External Data Connections Power Query Basics Using ADO and VBA to Pull External Data Working with Text Files Text File Manipulation Examples Performing Common File Operations Zipping and Unzipping Files

5  Part III: Working with UserForms CHAPTER 12: Leveraging Custom Dialog Boxes Alternatives to UserForms Using an Input Box Using the VBA MsgBox Function Using the Excel GetOpenFilename Method Using the Excel GetSaveAsFilename Method Prompting for a Folder Displaying Excel's Built-in Dialog Boxes Displaying a Data Form CHAPTER 13: Introducing UserForms How Excel Handles Custom Dialog Boxes Inserting a New UserForm Adding Controls to a UserForm Toolbox Controls Adjusting UserForm Controls Adjusting a Control's Properties Displaying a UserForm Closing a UserForm Creating a UserForm: An Example Referencing UserForm Controls Customizing the Toolbox Creating UserForm Templates A UserForm Checklist CHAPTER 14: Looking at UserForm Examples Creating a UserForm “Menu” Selecting Ranges from a UserForm Creating a Splash Screen Disabling a UserForm's Close Button Changing a UserForm's Size Zooming and Scrolling a Sheet from a UserForm Exploring ListBox Techniques Using the MultiPage Control in a UserForm Using an External Control Animating a Label CHAPTER 15: Implementing Advanced UserForm Techniques A Modeless Dialog Box Displaying a Progress Indicator Creating Wizards Emulating the MsgBox Function A UserForm with Movable Controls A UserForm with No Title Bar Simulating a Toolbar with a UserForm Emulating a Task Pane with a UserForm A Resizable UserForm Handling Multiple UserForm Controls with One Event Handler Selecting a Color in a UserForm Displaying a Chart in a UserForm Making a UserForm Semitransparent A Puzzle on a UserForm Video Poker on a UserForm

6  Part IV: Developing Excel Applications CHAPTER 16: Creating and Using Add-Ins What Is an Add-In? Understanding Excel's Add-in Manager Creating an Add-In An Add-In Example Comparing XLAM and XLSM Files Manipulating Add-Ins with VBA Optimizing the Performance of Add-Ins Special Problems with Add-Ins CHAPTER 17: Working with the Ribbon Ribbon Basics Customizing the Ribbon Creating a Custom Ribbon Using VBA with the Ribbon Creating an Old-Style Toolbar CHAPTER 18: Working with Shortcut Menus CommandBar Overview Referring to Controls in a CommandBar Properties of CommandBar Controls Displaying All Shortcut Menu Items Using VBA to Customize Shortcut Menus Resetting a Shortcut Menu Shortcut Menus and Events CHAPTER 19: Providing Help for Your Applications Help for Your Excel Applications Help Systems That Use Excel Components Displaying Help in a Web Browser Using the HTML Help System CHAPTER 20: Leveraging Class Modules What Is a Class Module? Creating a NumLock Class Coding Properties, Methods, and Events Exposing a QueryTable Event Creating a Class to Hold Classes CHAPTER 21: Understanding Compatibility Issues What Is Compatibility? Types of Compatibility Problems Avoid Using New Features But Will It Work on a Mac? Dealing with 64-Bit Excel Creating an International Application Multilanguage Applications VBA Language Considerations Using Local Properties Identifying System Settings Date and Time Settings

7  Part V: Appendix: VBA Statements and Functions Reference APPENDIX: VBA Statements and Functions Reference VBA Statements Functions

8  Index

9  End User License Agreement