a Chart to a Specific Range Creating a Set of Disconnected Charts Printing All Charts on a Worksheet Chapter 9: Sending Emails from Excel Installing Macros Mailing the Active Workbook as an Attachment Mailing a Specific Range as an Attachment Mailing a Single Sheet as an Attachment Sending Mail with a Link to Your Workbook Mailing All Email Addresses in Your Contact List Saving All Attachments to a Folder Saving Certain Attachments to a Folder Chapter 10: Wrangling External Data with Macros Working with External Data Connections Using Macros to Create Dynamic Connections Iterating through All Connections in a Workbook Using ADO and VBA to Pull External Data Working with Text Files
9 Part 5: The Part of Tens Chapter 11: Ten Handy Visual Basic Editor Tips Applying Block Comments Copying Multiple Lines of Code at Once Jumping between Modules and Procedures Teleporting to Your Functions Staying in the Right Procedure Stepping through Your Code Stepping to a Specific Line in Your Code Stopping Your Code at a Predefined Point Seeing the Beginning and End of Variable Values Turning Off Auto Syntax Check Chapter 12: Ten Places to Turn for Macro Help Let Excel Write the Macro for You Use the VBA Help Files Pilfer Code from the Internet Leverage User Forums Visit Expert Blogs Mine YouTube for Video Training Attend Live and Online Training Classes Learn from the Microsoft Office Dev Center Dissect the Other Excel Files in your Organization Ask Your Local Excel Genius Chapter 13: Ten Ways to Speed Up Your Macros Halt Calculations Disable Screen Updating Turn Off Status Bar Updates Tell Excel to Ignore Events Hide Page Breaks Suspend PivotTable Updates Steer Clear of Copy and Paste Use the With Statement Don’t Explicitly Select Objects Avoid Excessive Trips to the Worksheet
10 Index
11 About the Author
List of Illustrations
1 Chapter 1FIGURE 1-1: The Record Macro dialog box.FIGURE 1-2: Your pre-totaled worksheet containing two tables.FIGURE 1-3: Your post-totaled worksheet.FIGURE 1-4: The Excel Macro dialog box.FIGURE 1-5: Recording a macro with relative references.FIGURE 1-6: The Trusted Locations window allows you to add directories that are...FIGURE 1-7: You can find the form controls on the Developer tab.FIGURE 1-8: Assign a macro to the newly added button.FIGURE 1-9: Adding a macro to the Quick Access Toolbar.FIGURE 1-10: Use macros to build buttons that help users navigate your reports.FIGURE 1-11: This report allows users to choose their perspective.FIGURE 1-12: The macros behind these buttons rearrange the data fields in a Piv...FIGURE 1-13: Offering prerecorded views not only saves time and effort, but it ...FIGURE 1-14: You can give your audience a choice in how they view data.
2 Chapter 2FIGURE 2-1: The VBE with significant elements identified.FIGURE 2-2: The Project Explorer with two projects open, expanded to show their...FIGURE 2-3: Code modules are visible in the Project Explorer in a folder called...FIGURE 2-4: The Editor tab in the Options dialog box.FIGURE 2-5: Change the way VBE’s looks with the Editor Format tab.FIGURE 2-6: The General tab of the Options dialog box.FIGURE 2-7: The Docking tab of the Options dialog box.
3 Chapter 3FIGURE 3-1: Getting to the built-in module for a worksheet.FIGURE 3-2: The default SelectionChange event for the Worksheet object.FIGURE 3-3: Click the Event drop-down list to choose the most appropriate event...FIGURE 3-4: The default Open event for the Workbook object.FIGURE 3-5: Click the Event drop-down list to choose the most appropriate event...
4 Chapter 4FIGURE 4-1: Changing any cell in range C5:C16 forces the workbook to save.FIGURE 4-2: Enter or paste your code in the Worksheet_Change event Code pane.FIGURE 4-3: A message box opens when you attempt to close the workbook.FIGURE 4-4: Type or paste your code in the Workbook_BeforeClose