Michael Alexander - Excel 2019 Power Programming with VBA

Steps for Application Development Steps for Application Development There is no simple, surefire recipe for developing an effective spreadsheet application. Everyone has his or her own style for creating such applications. In addition, every project is different and therefore requires its own approach. Finally, the demands and technical expertise of the people with whom (or for whom) you work also play a role in how the development process proceeds. Spreadsheet developers typically perform the following activities: Determine the needs of the user(s) Plan an application that meets these needs Determine the most appropriate user interface Create the spreadsheet, formulas, macros, and user interface Test and debug the application Attempt to make the application bulletproof Make the application aesthetically appealing and intuitive Document the development effort Develop user documentation and Help systems Distribute the application to the user Update the application when necessary Not all of these steps are required for each application, and the order in which these activities are performed varies from project to project. We describe each of these activities in the pages that follow. For most of these items, we cover the technical details in subsequent chapters. 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

1 Chapter 1 TABLE 1.1 ActiveX Controls versus Form Controls

2 Chapter 2 TABLE 2.1 Some Useful Properties of the Application Object

3 Chapter 3 TABLE 3.1 VBA Built-in Data Types TABLE 3.2 Variable Scope TABLE 3.3 Operator Precedence TABLE 3.4 VBA Logical Operators

4 Chapter 5TABLE 5.1 Function Categories

5 Chapter 6TABLE 6.1 Commonly Used Workbook EventsTABLE 6.2 Worksheet EventsTABLE 6.3 Commonly Used Events Recognized by the Application ObjectTABLE 6.4 Key Codes for the OnKey Event

Читать дальше