Читать книгу Excel VBA Programming For Dummies - Dick Kusleika - Страница 5

1 Chapter 2FIGURE 2-1: The Developer tab is normally hidden, but it’s easy to unhide.FIGURE 2-2: The Record Macro dialog box appears when you’re about to record a m...FIGURE 2-3: The completed Record Macro dialog box.FIGURE 2-4: The VBE displays the VBA code in Module1 of Book1.FIGURE 2-5: If your workbook contains macros, and you attempt to save it in a m...FIGURE 2-6: The Macro Settings section of the Trust Center dialog box.FIGURE 2-7: Excel’s warning that the file to be opened contains macros.FIGURE 2-8: Excel's warning that the workbook just opened contains macros. You ...

2 Chapter 3FIGURE 3-1: The VBE is your customizable friend.FIGURE 3-2: This Project Explorer lists projects that can be expanded to show m...FIGURE 3-3: Code pane overload isn’t a pretty sight.FIGURE 3-4: The GuessName procedure displays this dialog box.FIGURE 3-5: The Editor tab of the Options dialog box.FIGURE 3-6: An example of Auto List Members.FIGURE 3-7: Auto Quick Info offers help about the MsgBox function.FIGURE 3-8: Change how the VBE looks with the Editor Format tab.FIGURE 3-9: The General tab of the Options dialog box.FIGURE 3-10: The Docking tab of the Options dialog box.

3 Chapter 4FIGURE 4-1: This message box displays a Range object’s Value property.FIGURE 4-2: The VBE displays a list of arguments while you type.FIGURE 4-3: An example from VBA’s Help system.FIGURE 4-4: Browsing for objects with the Object Browser.FIGURE 4-5: The Auto List Members feature helps you identify properties and met...

4 Chapter 5FIGURE 5-1: Using the built-in VBA InputBox function to get a number.FIGURE 5-2: Displaying the cube root of a number via the MsgBox function.FIGURE 5-3: The Macro dialog box lists all available Sub procedures.FIGURE 5-4: The Macro Options dialog box lets you set options for your macros.FIGURE 5-5: The Ribbon, showing the controls available when you click Insert on...FIGURE 5-6: When you add a button to a worksheet, Excel automatically displays ...FIGURE 5-7: Executing a Function in the Immediate window returns the answer imm...FIGURE 5-8: The CubeRoot function appears in the User Defined category of the I...FIGURE 5-9: Using the CubeRoot function in formulas.

5 Chapter 6FIGURE 6-1: A convenient window arrangement for watching the macro recorder do ...FIGURE 6-2: The Record Macro dialog box provides several options.

6 Chapter 7FIGURE 7-1: Pressing Ctrl+spacebar displays a list of variable names, reserved ...FIGURE 7-2: Each VBA module has a Declarations section, which appears before an...

7 Chapter 8FIGURE 8-1: A noncontiguous range selection.FIGURE 8-2: This message box displays the Address property of a 5 × 5 rang...

8 Chapter 9FIGURE 9-1: Calculating the length of your name.FIGURE 9-2: A way to display a list of VBA functions.FIGURE 9-3: Using a worksheet function in your VBA code.FIGURE 9-4: The range, named PriceList, contains prices for parts.FIGURE 9-5: Getting a list of worksheet functions that you can use in your VBA ...

9 Chapter 10FIGURE 10-1: A message displayed by the CheckCell procedure.FIGURE 10-2: Using a loop to apply background shading to rows.FIGURE 10-3: These cells were filled using a nested For-Next loop.FIGURE 10-4: Using loops to create a checkerboard pattern.

10 Chapter 11FIGURE 11-1: The Project window displays items for a single project.FIGURE 11-2: Choosing an event in the ThisWorkbook object’s module.FIGURE 11-3: This event-handler procedure is executed when the workbook is open...FIGURE 11-4: Using a Workbook_Open event-handler to keep track of how many time...FIGURE 11-5: When a chart sheet is activated, the user sees a message like this...FIGURE 11-6: Performing data validation with an event procedure.

11 Chapter 12FIGURE 12-1: The InputBox function displays a dialog box asking the user for a ...FIGURE 12-2: Excel displays this error message when the procedure attempts to c...FIGURE 12-3: Running the procedure when a chart is selected generates this erro...FIGURE 12-4: A runtime error in the procedure generates this semi-helpful error...FIGURE 12-5: If an error occurs, the user can decide whether to try again.

12 Chapter 13FIGURE 13-1: An error message like this often means that your VBA code contains...FIGURE 13-2: Using a message box to display the value of three variables.FIGURE 13-3: Pressing Ctrl+Break halts execution of your code and gives you som...FIGURE 13-4: A Debug.Print statement sends output to the Immediate window.FIGURE 13-5: The highlighted statement marks a breakpoint in this procedure.FIGURE 13-6: A typical scene in Break mode.FIGURE 13-7: The Add Watch dialog box lets you specify a condition that causes ...FIGURE 13-8: The Watches window displays all watches.FIGURE 13-9: The Locals window displays all local variables and their content.

13 Chapter 14FIGURE 14-1: This range can consist of any number of rows.FIGURE 14-2: Use the VBA InputBox function to get a value from the user.FIGURE 14-3: Excel doesn’t like it when you try to copy a multiple selection.FIGURE 14-4: You can instruct Excel to not display these types of alerts while ...

14 Chapter 15FIGURE 15-1: A simple message box.FIGURE 15-2: A simple message box, with two buttons.FIGURE 15-3: The MsgBox function’s buttons argument determines what appears in ...FIGURE 15-4: This dialog box, displayed by the MsgBox function, displays a titl...FIGURE 15-5: The InputBox function displays this dialog box.FIGURE 15-6: Another example of using the InputBox function.FIGURE 15-7: Using the Application.InputBox method to get a range.FIGURE 15-8: The GetOpenFilename method displays a customizable dialog box and ...FIGURE 15-9: Displaying one of Excel's dialog boxes by using VBA.FIGURE 15-10: Using the Customize Ribbon tab to identify a command name.

15 Chapter 16FIGURE 16-1: You can get information from the user by displaying a UserForm.FIGURE 16-2: A new UserForm object.FIGURE 16-3: Use the Properties windows to change the properties of UserForm co...FIGURE 16-4: The UserForm with two CommandButton controls.FIGURE 16-5: This is the UserForm after adding three OptionButton controls insi...FIGURE 16-6: Assign a shortcut key to execute the ChangeCase macro.FIGURE 16-7: Adding the ChangeChase macro to the Quick Access toolbar.FIGURE 16-8: The UserForm is in action.FIGURE 16-9: The text has been converted to uppercase.

16 Chapter 17FIGURE 17-1: A UserForm in the VBE, with a few controls added.FIGURE 17-2: Use the Properties window to make design-time changes to a control...FIGURE 17-3: Change some properties by selecting from a drop-down list of valid...FIGURE 17-4: CheckBox Controls in a UserForm.FIGURE 17-5: ComboBox controls in a UserForm.FIGURE 17-6: CommandButton controls.FIGURE 17-7: An Image control displays a photo.FIGURE 17-8: Label controls can take on many different looks.FIGURE 17-9: ListBox controls.FIGURE 17-10: Use a MultiPage control to create a tabbed dialog box.FIGURE 17-11: Two sets of OptionButton controls, each contained in a Frame cont...FIGURE 17-12: Two RefEdit controls.FIGURE 17-13: A ScrollBar control with a Label control below it.FIGURE 17-14: SpinButton controls.FIGURE 17-15: TextBox controls.FIGURE 17-16: ToggleButton controls.FIGURE 17-17: Choose Format ⇒ Align to change the alignment of UserForm con...FIGURE 17-18: The Tab Order dialog box.FIGURE 17-19: Use labels to provide direct access to controls that don’t have a...

17 Chapter 18FIGURE 18-1: This dialog box logs dinner choices for guests.FIGURE 18-2: Executing the LogDinnerGuest procedure displays the dialog box.FIGURE 18-3: Use the custom dialog box for data entry.FIGURE 18-4: Determining which item in a list box is selected.FIGURE 18-5: Determining the selected items in a list box that allows multiple ...FIGURE 18-6: This dialog box lets the user select a range.FIGURE 18-7: This dialog box contains three sets of OptionButton controls.FIGURE 18-8: A UserForm with a spin button and a companion text box.FIGURE 18-9: This UserForm functions as a progress indicator for a lengthy macr...FIGURE 18-10: The progress-indicator UserForm.FIGURE 18-11: The three tabs of a MultiPage control.FIGURE 18-12: Displaying a chart in a UserForm.

18 Chapter 19FIGURE 19-1: The Customize Ribbon tab of the Excel Options dialog box.FIGURE 19-2: The View tab with a new group named Text To Speech.FIGURE 19-3: RibbonX code displayed in the Custom UI Editor.FIGURE 19-4: The VBA callback procedure that is executed by clicking the Ribbon...FIGURE 19-5: Proof that adding a new Ribbon command using XML is actually possi...FIGURE 19-6: A control on the Add-in tab isn’t flashy, but it gets the job done...FIGURE 19-7: The Cell shortcut menu showing a custom menu item: Change Case.

19 Chapter 20FIGURE 20-1: Using the Commission function in a worksheet.FIGURE 20-2: Using the Commission2 function, which takes two arguments.FIGURE 20-3: Using a custom function to sum only odd numbers.FIGURE 20-4: Using the ExtractElement function to return an element from a stri...FIGURE 20-5: Using the MonthNames function to return a 12-element array.FIGURE 20-6: Using a custom function to return a sorted range.FIGURE 20-7: By default, the Insert Function dialog box doesn’t provide a descr...FIGURE 20-8: The custom function now displays a description.FIGURE 20-9: By default, the Function Arguments dialog box displays Function ar...

20 Chapter 21FIGURE 21-1: The Add-Ins dialog box lists all the add-ins known to Excel.FIGURE 21-2: The UserForm for the Change Case add-in.FIGURE 21-3: Use the Properties section to enter descriptive information about ...FIGURE 21-4: The Add-Ins dialog box has the new add-in selected.FIGURE 21-5: Making an add-in not an add-in.

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