Excel VBA Programming For Dummies. Dick Kusleika

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

      1 Chapter 7TABLE 7-1 VBA’s Built-In Data TypesTABLE 7-2 Variable’s ScopeTABLE 7-3 VBA’s OperatorsTABLE 7-4 VBA’s Logical Operators

      2 Chapter 9TABLE 9-1 VBA Functions with Useful Side BenefitsTABLE 9-2 VBA’s Most Useful Built-In Functions

      3 Chapter 10TABLE 10-1 Programming Constructs for Making Decisions

      4 Chapter 11TABLE 11-1 Workbook EventsTABLE 11-2 Worksheet Events

      5 Chapter 12TABLE 12-1 Using the On Error StatementTABLE 12-2 Using the Resume Statement

      6 Chapter 15TABLE 15-1 MsgBox Function ArgumentsTABLE 15-2 Constants Used in the MsgBox FunctionTABLE 15-3 Constants Used as Return Values for the MsgBox FunctionTABLE 15-4 InputBox Function ArgumentsTABLE 15-5 GetOpenFilename Method ArgumentsTABLE 15-6 GetSaveAsFilename Method Arguments

      7 Chapter 16TABLE 16-1 Toolbox Controls

      8 Chapter 17TABLE 17-1 Common Control Properties

      9 Chapter 18TABLE 18-1 Settings for the MultiSelect Property

      10 Chapter 20TABLE 20-1 Commission Rates by Sales

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