Excel 2019 Power Programming with VBA. Michael Alexander

Чтение книги онлайн.

СКАЧАТЬ 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 СКАЧАТЬ