Excel 2019 Power Programming with VBA. Michael Alexander

the OnKey Event

      6 Chapter 9TABLE 9.1 Events Recognized by the Chart Object

      7 Chapter 11TABLE 11.1 VBA File-Related StatementsTABLE 11.2 File Attribute Constants for the Dir Function

      8 Chapter 12TABLE 12.1 Codes to Determine the Data Type Returned by Excel's InputBox Method...TABLE 12.2 Constants Used for Buttons in the MsgBox Function TABLE 12.3 Constants Used for MsgBox Return Value

      9 Chapter 13TABLE 13.1 SpinButton Events

      10 Chapter 21TABLE 21.1 Excel Country CodesTABLE 21.2 Properties That Have Local VersionsTABLE 21.3 Constants for the International Property

      11 AppendixTABLE A.1 Summary of VBA StatementsTABLE A.2 Summary of VBA Functions

      List of Illustrations

      1 Chapter 1FIGURE 1.1 A customized shortcut menuFIGURE 1.2 A dialog box created with Excel's UserForm featureFIGURE 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 worksheetFIGURE 1.5 Using the Protect Sheet dialog box to specify what users can and c...

      2 Chapter 2FIGURE 2.1 The Record Macro dialog boxFIGURE 2.2 Your pretotaled worksheet containing two tablesFIGURE 2.3 Your post-totaled worksheetFIGURE 2.4 The Excel Macro dialog boxFIGURE 2.5 Recording a macro with relative referencesFIGURE 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 toolbarFIGURE 2.10 The VBE with significant elements identifiedFIGURE 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 boxFIGURE 2.14 Change the VBE's looks with the Editor Format tab.FIGURE 2.15 The General tab of the Options dialog boxFIGURE 2.16 The Docking tab of the Options dialog boxFIGURE 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