Читать книгу Excel Macros For Dummies - Dick Kusleika - Страница 4

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 ...