Dick Kusleika - Excel VBA Programming For Dummies

9 Part 5: Putting It All Together Chapter 20: Creating Worksheet Functions Create Custom Functions to Simplify Your Work Understanding VBA Function Basics Writing Functions Working with Function Arguments Introducing Wrapper Functions Working with Functions That Return an Array Using the Insert Function Dialog Box Chapter 21: Creating Excel Add-Ins Add-Ins Defined Reasons to Create Add-Ins Working with Add-Ins Understanding Add-In Basics Looking at an Add-In Example

10 Part 6: The Part of Tens Chapter 22: Ten Handy Visual Basic Editor Tips Applying Block Comments Copying Multiple Lines of Code at Once Jumping between Modules and Procedures Teleporting to Your Functions Staying in the Right Procedure Stepping through Your Code Stepping to a Specific Line in Your Code Stopping Your Code at a Predefined Point Seeing the Beginning and End of Variable Values Turning Off Auto Syntax Check Chapter 23: Resources for VBA Help Letting Excel Write Code for You Referencing the Help System Pilfering Code from the Internet Leveraging User Forums Visiting Expert Blogs Mining YouTube for Video Training Attending Live and Online Training Classes Learning from the Microsoft Office Dev Center Dissecting the Other Excel Files in Your Organization Asking Your Local Excel Guru Chapter 24: Ten VBA Do’s and Don’ts Do Declare All Variables Don’t Confuse Passwords with Security Do Clean Up Your Code Don’t Put Everything in One Procedure Do Consider Other Software Don’t Assume That Everyone Enables Macros Do Get in the Habit of Experimenting Don’t Assume That Your Code Will Work with Other Excel Versions Do Keep Your Users in Mind Don’t Forget about Backups

11 Index

12 About the Author

13 Connect with Dummies

14 End User License Agreement

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 ...