Dick Kusleika - Excel Macros For Dummies

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

12 Advertisement Page

13 Connect with Dummies

14 End User License Agreement

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 event Code pane...FIGURE 4-5: Type or paste your code in the Workbook BeforeClose event Code.FIGURE 4-6: Type or paste your code in the Workbook Open event Code pane.FIGURE 4-7: You want your workbook to automatically open to the sheet called St...FIGURE 4-8: Type or paste your code in the Workbook Open event Code pane.FIGURE 4-9: The macro opens the Open dialog box.FIGURE 4-10: You can avoid this annoying message box when opening a workbook th...FIGURE 4-11: You can prevent your workbook from closing until a specific cell i...FIGURE 4-12: Type or paste your code in the Workbook_BeforeClose event Code pan...

5 Chapter 5FIGURE 5-1: Omit the Application.DisplayAlerts = False line in the macro to ens...FIGURE 5-2: Without a macro, you’re stuck using Excel’s Unhide dialog box to un...FIGURE 5-3: It’s often useful to have your worksheets sorted in alphabetical or...FIGURE 5-4: You can right-click any worksheet to choose a tab color for the she...FIGURE 5-5: The Protect Sheet command is disabled if you try to protect more th...FIGURE 5-6: The Unprotect Sheet command is disabled if you try to unprotect mor...FIGURE 5-7: A table of contents can help you more easily navigate your workbook...FIGURE 5-8: Type or paste your code into the Worksheet_BeforeDoubleClick event ...FIGURE 5-9: A highlighted row and column makes it easy to track data horizontal...

6 Chapter 6FIGURE 6-1: Click the Define Name command to name a chosen range.FIGURE 6-2: Give your range a name.FIGURE 6-3: The Name Manager dialog box lists all the applied named ranges.FIGURE 6-4: Type or paste your code in the Workbook Open event Code pane.FIGURE 6-5: The Go To Special dialog box.FIGURE 6-6: Choosing Formulas in the Go To Special dialog box tells Excel to se...FIGURE 6-7: You can use a macro to tell Excel to dynamically find the first ava...

7 Chapter 7FIGURE 7-1: Imported numbers are sometimes formatted as text.FIGURE 7-2: Click the Text to Columns command.FIGURE 7-3: Clicking Finish in the Text to Columns dialog box corrects incorrec...FIGURE 7-4: This macro dynamically finds and highlights the duplicate values in...FIGURE 7-5: This macro ensures that only those rows that contain duplicate valu...FIGURE 7-6: The standard AutoFilter functionality adds drop-down arrows to all ...FIGURE 7-7: With a little VBA, you can choose to hide certain AutoFilter drop-d...FIGURE 7-8: This macro lists all filtered columns in the status bar.FIGURE 7-9: Type or paste your code in the Worksheet_Calculate event Code pane.

8 Chapter 8FIGURE 8-1: A PivotTable inventory summary.FIGURE 8-2: Excel shows the enumeration choices.FIGURE 8-3: Type or paste your code in the Worksheet_BeforeDoubleClick event Co...FIGURE 8-4: Enter or paste your code in the Workbook_BeforeClose event Code pan...FIGURE 8-5: The Selection pane allows you to see all of your chart objects' nam...

9 Chapter 10FIGURE 10-1: Show legacy wizards in the Ribbon.FIGURE 10-2: Choose the source database that contains the data you want importe...FIGURE 10-3: Select the Access object you want to import.FIGURE 10-4: Choosing how and where to view your Access data.FIGURE 10-5: Data imported from Access.FIGURE 10-6: As long as a connection to your database is available, you can upd...FIGURE 10-7: Select the SQL command type and enter your SQL statement.FIGURE 10-8: Designate a cell that will trap the criteria selection.FIGURE 10-9: You now have an easy-to-use mechanism to pull external data for a ...FIGURE 10-10: Select the latest version of the Microsoft ActiveX Data Objects L...

10 Chapter 11FIGURE 11-1: A single apostrophe in front of any line turns that line into a co...FIGURE 11-2: The Edit toolbar allows you to select entire blocks of code, and t...FIGURE 11-3: Holding down the Ctrl key while dragging code creates a copy of th...FIGURE 11-4: Pressing Shift+F2 with your cursor on a function or variable name ...FIGURE 11-5: Use the Procedure View button to limit scrolling to just the activ...FIGURE 11-6: Press F8 key to step through each line of your macro at your own p...FIGURE 11-7: You can click and drag the yellow arrow while stepping through you...FIGURE 11-8: A breakpoint is marked by a dot in the left margin along with shad...FIGURE 11-9: Showing the beginning and ending characters in a variable tooltip.FIGURE 11-10: Leaving a line of code unfinished, even for a second, results in ...FIGURE 11-11: Uncheck the Auto Syntax Check option to prevent warning messages ...

1 Cover

2 Title Page

3 Copyright

4 Table of Contents

5 Begin Reading

6 Index

7 About the Author