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

List of Illustrations

1 Chapter 1FIGURE 1.1 A customized shortcut menu FIGURE 1.2 A dialog box created with Excel's UserForm feature FIGURE 1.3 You can add UserForm controls to worksheets and link them to cells...FIGURE 1.4 Using the Ribbon to add controls to a worksheet FIGURE 1.5 Using the Protect Sheet dialog box to specify what users can and c...
2 Chapter 2FIGURE 2.1 The Record Macro dialog box FIGURE 2.2 Your pretotaled worksheet containing two tables FIGURE 2.3 Your post-totaled worksheet FIGURE 2.4 The Excel Macro dialog box FIGURE 2.5 Recording a macro with relative references FIGURE 2.6 The Trusted Locations tab allows you to add directories that are c...FIGURE 2.7 You can find the form controls on the Developer tab.FIGURE 2.8 Assign a macro to the newly added button.FIGURE 2.9 Adding a macro to the Quick Access toolbar FIGURE 2.10 The VBE with significant elements identified FIGURE 2.11 This Project window lists two projects. They are expanded to show...FIGURE 2.12 Code modules are visible in the Project window in a folder called...FIGURE 2.13 The Editor tab in the Options dialog box FIGURE 2.14 Change the VBE's looks with the Editor Format tab.FIGURE 2.15 The General tab of the Options dialog box FIGURE 2.16 The Docking tab of the Options dialog box FIGURE 2.17 The Object Browser is a great reference source.
3 Chapter 3FIGURE 3.1 VBA's way of telling you that your procedure contains an undeclare...FIGURE 3.2 VBA displays a list of constants that you can assign to a property...FIGURE 3.3 Displaying a list of VBA functions in VBE
4 Chapter 4FIGURE 4.1 The Macro dialog boxFIGURE 4.2 The Macro Options dialog box lets you assign a Ctrl key shortcut a...FIGURE 4.3 The References dialog box lets you establish a reference to anothe...FIGURE 4.4 Assigning a macro to a buttonFIGURE 4.5 Executing a procedure by entering its name in the Immediate window...FIGURE 4.6 VBA error messages aren't always user friendly.FIGURE 4.7 You can create a message box to display the error code and descrip...FIGURE 4.8 The SpecialCells method generates this error if no cells are found...FIGURE 4.9 Using the VBE Immediate window to test a statementFIGURE 4.10 An empty procedure in a module located in the Personal Macro Work...FIGURE 4.11 Using a temporary procedure to test the BubbleSort codeFIGURE 4.12 This message box tells the user that the sheets cannot be sorted....FIGURE 4.13 This message box appears before the sheets are sorted.FIGURE 4.14 Adding a new command to the Ribbon
5 Chapter 5FIGURE 5.1 Using a custom function in a worksheet formulaFIGURE 5.2 Using a custom function in a VBA procedureFIGURE 5.3 Using a custom VBA function for conditional formattingFIGURE 5.4 Calling a Function procedure from the Immediate windowFIGURE 5.5 Using a function to display the result of a calculationFIGURE 5.6 Different ways of passing an array or a single value to a workshee...FIGURE 5.7 Comparing SUM with MYSUMFIGURE 5.8 The Extended Date functions used in formulasFIGURE 5.9 Use the Immediate window to display results while a function is ru...FIGURE 5.10 The Insert Function and Function Arguments dialog boxes for a cus...FIGURE 5.11 Using Windows API functions to determine which keys were pressed ...
6 Chapter 6FIGURE 6.1 The components for each VBA project are listed in the Project wind...FIGURE 6.2 The best way to create an event procedure is to let the VBE do it ...FIGURE 6.3 This message box was triggered by a SheetActivate event.FIGURE 6.4 Clicking No cancels the print operation by changing the Cancel arg...FIGURE 6.5 When this message appears, Workbook_BeforeClose has already done i...FIGURE 6.6 A message displayed by the Workbook_BeforeClose event procedureFIGURE 6.7 This message box describes the problem when the user makes an inva...FIGURE 6.8 The Worksheet_Change procedure ensures that data validation isn't ...FIGURE 6.9 Moving the cell cursor shades the active cell's row and column.FIGURE 6.10 This workbook uses a class module to monitor all Application-leve...FIGURE 6.11 This message box was programmed to display at a particular time o...FIGURE 6.12 Pressing Shift+F10 displays this message.
7 Chapter 7FIGURE 7.1 The number of rows in the data range changes every week.FIGURE 7.2 This workbook uses a custom shortcut menu to demonstrate how to se...FIGURE 7.3 The InputBox function gets a value from the user to be inserted in...FIGURE 7.4 Validate a user's entry with the VBA InputBox function.FIGURE 7.5 A macro for inserting data into the next empty row in a worksheet...FIGURE 7.6 Use an input box to pause a macro.FIGURE 7.7 A VBA procedure analyzes the currently selected range.FIGURE 7.8 Using the intersection of the used range and the selected ranged r...FIGURE 7.9 The goal is to duplicate rows based on the value in column B.FIGURE 7.10 New rows were added, according to the value in column B.FIGURE 7.11 Using a function to determine the type of data in a cellFIGURE 7.12 Displaying the time to write to a range and read from a range, us...FIGURE 7.13 Using Excel's InputBox method to prompt for a cell locationFIGURE 7.14 All rows and columns are hidden, except for a range (G7:L19).FIGURE 7.15 Hyperlinks to each worksheet, created by a macroFIGURE 7.16 A message box displaying the date and timeFIGURE 7.17 Using a function to display time differences in a friendly manner...FIGURE 7.18 Listing font names in the actual fontsFIGURE 7.19 Comparing the time required to perform sorts of various array siz...FIGURE 7.20 Examples of the SPELLDOLLARS functionFIGURE 7.21 Determining the path and name of the application associated with ...
8 Chapter 8FIGURE 8.1 This table is a good candidate for a pivot table.FIGURE 8.2 A pivot table created from the data in Figure 8.1FIGURE 8.3 The data in this workbook will be summarized in a pivot table.FIGURE 8.4 A pivot table created from the budget dataFIGURE 8.5 The Pivot Table Fields task paneFIGURE 8.6 Several pivot tables created by a VBA procedureFIGURE 8.7 The summary table on the left will be converted to the table on th...FIGURE 8.8 This dialog box asks the user for the ranges.
9 Chapter 9FIGURE 9.1 These charts use different formatting.FIGURE 9.2 A simple macro applied consistent formatting to the four charts.FIGURE 9.3 Each row of data will be used to create a chart.FIGURE 9.4 A sampling of the 50 charts created by the macroFIGURE 9.5 This chart always displays the data from the row of the active cel...FIGURE 9.6 Data labels from an arbitrary range show the percent change for ea...FIGURE 9.7 Data labels created from a range of data are not compatible with v...FIGURE 9.8 An XY chart that would benefit by having data labelsFIGURE 9.9 This XY chart has data labels, thanks to a VBA procedure.FIGURE 9.10 A chart within a UserFormFIGURE 9.11 Selecting an event in the code module for a Chart objectFIGURE 9.12 This chart serves as a clickable image map.FIGURE 9.13 After converting a chart to a picture, you can manipulate it by u...FIGURE 9.14 A text box displays information about the data point under the mo...FIGURE 9.15 Range B7:C9 contains data point information that's displayed in t...FIGURE 9.16 An example of a scrollable chartFIGURE 9.17 Sparkline examplesFIGURE 9.18 The result of running the SparklineReport procedure
10 Chapter 10FIGURE 10.1 Add a reference to the object library for the application that yo...
11 Chapter 11FIGURE 11.1 Starting a Power Query web queryFIGURE 11.2 Enter the target URL containing the data you need.FIGURE 11.3 Select the correct data source and then click the Edit button.FIGURE 11.4 The Power Query Editor window allows you to shape, clean, and tra...FIGURE 11.5 Select the columns that you want to keep and then select Remove Ot...FIGURE 11.6 You can click the Column Actions icon to select actions (such as R...FIGURE 11.7 The Power Query Editor can be used to apply transformation actions...FIGURE 11.8 The Import Data dialog box gives you more control over how the re...FIGURE 11.9 Your final query pulled from the Internet: transformed, put into ...FIGURE 11.10 Query steps can be viewed and managed in the Applied Steps secti...FIGURE 11.11 The Advanced Editor windowFIGURE 11.12 Designate a cell that will trap the criteria selection.FIGURE 11.13 Select the latest version of the Microsoft ActiveX Data Objects ...
12 Chapter 12FIGURE 12.1 The VBA InputBox function at workFIGURE 12.2 Using the VBA InputBox function with a long promptFIGURE 12.3 Using the InputBox method to specify a rangeFIGURE 12.4 Excel's InputBox method performs validation automaticallyFIGURE 12.5 Another example of validating an entry in Excel's InputBoxFIGURE 12.6 The button argument of the MsgBox function determines which buttons...FIGURE 12.7 Displaying lengthy text in a message boxFIGURE 12.8 This message box displays text with tabs and line breaks.FIGURE 12.9 The GetOpenFilename method displays a dialog box used to specify a ...FIGURE 12.10 This dialog box was displayed with a VBA statement.FIGURE 12.11 Using the Customize Ribbon panel to identify a command nameFIGURE 12.12 Some users prefer to use Excel's built-in data form for data-entry...
13 Chapter 13FIGURE 13.1 The Properties window for an empty UserFormFIGURE 13.2 Use the Toolbox to add controls to a UserForm.FIGURE 13.3 This UserForm displays all of the controls.FIGURE 13.4 Use the Format ➪ Align command to change the alignment of controls....FIGURE 13.5 The OptionButton controls, aligned and evenly spacedFIGURE 13.6 The Properties window for an OptionButton controlFIGURE 13.7 Use the Tab Order dialog box to specify the tab order of the contro...FIGURE 13.8 This dialog box asks the user to enter a name and a gender.FIGURE 13.9 The CommandButton1_Click procedure is executed when the button on t...FIGURE 13.10 The CommandButton's Click event procedure displays the...FIGURE 13.11 The event list for a CheckBox controlFIGURE 13.12 This SpinButton is paired with a TextBox.FIGURE 13.13 The Toolbox, with a new page of controlsFIGURE 13.14 The Additional Controls dialog box lets you add other ActiveX cont...
14 Chapter 14FIGURE 14.1 This dialog box uses command buttons as a menu.FIGURE 14.2 This dialog box uses a list box as a menu.FIGURE 14.3 The RefEdit control allows the user to select a range.FIGURE 14.4 This splash screen is displayed briefly when the workbook is opened...FIGURE 14.5 A dialog box before and after displaying optionsFIGURE 14.6 Here, scroll bars allow zooming and scrolling of the worksheet.FIGURE 14.7 Setting the RowSource property at design timeFIGURE 14.8 A Collection object is used to fill a list box with the unique item...FIGURE 14.9 This message box displays a list of items selected in a list box.FIGURE 14.10 The contents of this list box depend on the option button selected...FIGURE 14.11 Building a list from another listFIGURE 14.12 The buttons allow the user to move items up or down in the ListBox...FIGURE 14.13 This ListBox displays a three-column list with column headers.FIGURE 14.14 A two-column ListBox filled with data stored in an arrayFIGURE 14.15 This list box makes selecting rows in a worksheet easy.FIGURE 14.16 This dialog box lets the user activate a sheet.FIGURE 14.17 Use a text box to filter a list box.FIGURE 14.18 MultiPage groups your controls on pages, making them accessible fr...FIGURE 14.19 The Windows Media Player control in a UserFormFIGURE 14.20 The Windows Media Player controlFIGURE 14.21 Generating a random numberFIGURE 14.22 A random number has been chosen.
15 Chapter 15FIGURE 15.1 This modeless dialog box remains visible while the user continues...FIGURE 15.2 This modeless UserForm displays various pieces of information abo...FIGURE 15.3 A UserForm displays the progress of a macro.FIGURE 15.4 This UserForm will serve as a progress indicator.FIGURE 15.5 The user specifies the number of rows and columns for the random ...FIGURE 15.6 Page2 of the MultiPage control will display the progress indicato...FIGURE 15.7 The progress indicator will be hidden by reducing the height of t...FIGURE 15.8 The progress indicator in actionFIGURE 15.9 The steps are listed in a ListBox control.FIGURE 15.10 Files are added to the list to show progress.FIGURE 15.11 This four-step wizard uses a MultiPage control.FIGURE 15.12 Clicking the Cancel button displays a confirmation message box....FIGURE 15.13 The result of the MsgBox emulation functionFIGURE 15.14 The UserForm for the MyMsgBox functionFIGURE 15.15 You can drag and rearrange the three Image controls by using the...FIGURE 15.16 This UserForm lacks a title bar.FIGURE 15.17 Another UserForm without a title barFIGURE 15.18 A UserForm set up to function as a toolbarFIGURE 15.19 The UserForm that simulates a toolbarFIGURE 15.20 A UserForm designed to look like a task paneFIGURE 15.21 This UserForm is resizable.FIGURE 15.22 The UserForm after it was increasedFIGURE 15.23 The VBA code converts Label control movements into new Width and...FIGURE 15.24 Multiple command buttons with a single event-handler procedureFIGURE 15.25 The ButtonGroup_Click procedure describes the button that was cl...FIGURE 15.26 This dialog box lets the user select a color by specifying the r...FIGURE 15.27 The user's scroll bar values are stored in the Windows Registry ...FIGURE 15.28 A UserForm can display "live" charts.FIGURE 15.29 A semitransparent UserFormFIGURE 15.30 Creating a light-box effect in ExcelFIGURE 15.31 A sliding tile puzzle in a UserFormFIGURE 15.32 A feature-packed video poker game
16 Chapter 16FIGURE 16.1 Excel warns you if an add-in uses a nonstandard file extension.FIGURE 16.2 These settings affect whether add-ins can be used.FIGURE 16.3 The Add-ins dialog boxFIGURE 16.4 The Export Charts workbook will make a useful add-in.FIGURE 16.5 The Add-ins dialog box with the new add-in selectedFIGURE 16.6 Making an add-in not an add-inFIGURE 16.7 One way to remove a member of the AddIns collectionFIGURE 16.8 A table that lists information about all members of the AddIns co...FIGURE 16.9 When attempting to open the add-in incorrectly, the user sees thi...
17 Chapter 17FIGURE 17.1 The Page Layout tab contains many different control types.FIGURE 17.2 The Customize Ribbon tab allows you to add macros to the Ribbon....FIGURE 17.3 The Rename dialog lets you choose an icon for your Ribbon button....FIGURE 17.4 The custom Ribbon button executes the HelloWorld macro.FIGURE 17.5 You can add a macro to the Quick Access toolbar.FIGURE 17.6 The new QAT button executes your macro.FIGURE 17.7 Excel can't find the macro associated with the Ribbon button.FIGURE 17.8 XML to create two buttons in a custom groupFIGURE 17.9 The editor generates VBA code to use in your workbook.FIGURE 17.10 Modify the callback procedures in the VBE.FIGURE 17.11 Two new buttons added to the Data tabFIGURE 17.12 This check box control is always in sync with the page break dis...FIGURE 17.13 A new Ribbon tab with five groups of controlsFIGURE 17.14 A Ribbon group with two labels.FIGURE 17.15 An editBox control in a custom Ribbon groupFIGURE 17.16 Three controls in a custom Ribbon group.FIGURE 17.17 This group contains built-in controls.FIGURE 17.18 This Ribbon group contains two galleries.FIGURE 17.19 A gallery that displays month names, plus a buttonFIGURE 17.20 A gallery of imagesFIGURE 17.21 The dynamicMenu control lets you create a menu that varies depen...FIGURE 17.22 Using the Customize Ribbon tab of the Excel Options dialog box t...FIGURE 17.23 An old-style toolbar, located in the Custom Toolbars group of th...
18 Chapter 18FIGURE 18.1 A simple macro generates a list of all shortcut menus.FIGURE 18.2 Displaying the Caption property for controlsFIGURE 18.3 Listing the items in all shortcut menusFIGURE 18.4 The Cell shortcut menu with a custom menu itemFIGURE 18.5 This shortcut menu has a submenu with three submenu items.FIGURE 18.6 A new shortcut menu appears only when the user right-clicks a cel...
19 Chapter 19FIGURE 19.1 Using cell comments to display helpFIGURE 19.2 Using a shape object with text to display help for the userFIGURE 19.3 An easy method is to put user help in a separate worksheet.FIGURE 19.4 Clicking one of the arrows on the SpinButton changes the text dis...FIGURE 19.5 Inserting a Label control inside a Frame control adds scrolling t...FIGURE 19.6 Using a drop-down list control to select a help topicFIGURE 19.7 Displaying help in a web browserFIGURE 19.8 Displaying an MHTML file in a web browserFIGURE 19.9 An example of HTML HelpFIGURE 19.10 Using HTML Help Workshop to create a help fileFIGURE 19.11 Specify a context ID for a custom function.
20 Chapter 20FIGURE 20.1 An empty class module named CNumLockFIGURE 20.2 A message box shows the change in status of the Num Lock key.FIGURE 20.3 A web query for financial informationFIGURE 20.4 The code pane lists available events.FIGURE 20.5 After a web query is refreshed, the last update time is recor...FIGURE 20.6 Excel tables hold the information for the objects.FIGURE 20.7 The commission calculation is output to the Immediate Window.
21 Chapter 21FIGURE 21.1 Compatibility CheckerFIGURE 21.2 A summary report from Microsoft Office Code Compatibility Inspect...FIGURE 21.3 The wizard demo in English, Spanish, and German

Подняться наверх