Introduction .1 What Is in This Book? .1 Reducing the Learning Curve .1 Excel VBA Power .2 Techie Stuff Needed to Produce Applications .2 Does This Book Teach Excel? .2 The Future of VBA and Windows Versions of Excel .4 Versions of Excel .

4 Differences for Mac Users .4 Special Elements and Typographical Conventions .5 Code Files .5 Next Steps .5 1 Unleashing the Power of Excel with VBA .7 The Power of Excel .7 Barriers to Entry .7 The Macro Recorder Doesn''t Work! .

7 No One on the Excel Team Is Focused on the Macro Recorder .8 Visual Basic Is Not Like BASIC.8 Good News: Climbing the Learning Curve Is Easy .9 Great News: Excel with VBA Is Worth the Effort .9 Knowing Your Tools: The Developer Tab .9 Understanding Which File Types Allow Macros .10 Macro Security .12 Adding a Trusted Location .

12 Using Macro Settings to Enable Macros in Workbooks Outside Trusted Locations .13 Using Disable All Macros with Notification.14 Overview of Recording, Storing, and Running a Macro .14 Filling Out the Record Macro Dialog .15 Running a Macro .16 Creating a Macro Button on the Ribbon .16 Creating a Macro Button on the Quick Access Toolbar .17 Assigning a Macro to a Form Control, Text Box, or Shape .

18 Understanding the VB Editor.19 VB Editor Settings .20 The Project Explorer .20 The Properties Window .21 Understanding Shortcomings of the Macro Recorder .21 Recording the Macro .23 Examining Code in the Programming Window .23 Running the Macro on Another Day Produces Undesired Results .

25 Possible Solution: Use Relative References When Recording .26 Never Use AutoSum or Quick Analysis While Recording a Macro .30 Four Tips for Using the Macro Recorder.31 Next Steps .32 2 This Sounds Like BASIC, So Why Doesn''t It Look Familiar?.33 I Can''t Understand This Code .33 Understanding the Parts of VBA "Speech" .34 VBA Is Not Really Hard .

37 VBA Help Files: Using F1 to Find Anything .38 Using Help Topics .38 Examining Recorded Macro Code: Using the VB Editor and Help .39 Optional Parameters .39 Defined Constants .40 Properties Can Return Objects .43 Using Debugging Tools to Figure Out Recorded Code .43 Stepping Through Code .

43 More Debugging Options: Breakpoints .45 Backing Up or Moving Forward in Code .45 Not Stepping Through Each Line of Code .46 Querying Anything While Stepping Through Code .46 Using a Watch to Set a Breakpoint .49 Using a Watch on an Object .49 Object Browser: The Ultimate Reference .50 Seven Tips for Cleaning Up Recorded Code.

51 Tip 1: Don''t Select Anything .51 Tip 2: Use Cells(2,5) Because It''s More Convenient Than Range("E2") .52 Tip 3: Use More Reliable Ways to Find the Last Row .52 Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas.53 Tip 5: Use R1C1 Formulas That Make Your Life Easier .54 Tip 6: Copy and Paste in a Single Statement .54 Tip 7: Use With.End With to Perform Multiple Actions .

54 Next Steps .57 3 Referring to Ranges.59 The Range Object .59 Syntax for Specifying a Range .60 Named Ranges .60 Shortcut for Referencing Ranges .60 Referencing Ranges in Other Sheets .61 Referencing a Range Relative to Another Range .

61 Using the Cells Property to Select a Range .62 Using the Offset Property to Refer to a Range .63 Using the Resize Property to Change the Size of a Range .65 Using the Columns and Rows Properties to Specify a Range .66 Using the Union Method to Join Multiple Ranges .66 Using the Intersect Method to Create a New Range from Overlapping Ranges .67 Using the IsEmpty Function to Check Whether a Cell Is Empty .67 Using the CurrentRegion Property to Select a Data Range .

68 Using the Areas Collection to Return a Noncontiguous Range .70 Referencing Tables .71 Next Steps .72 4 Looping and Flow Control .73 For.Next Loops .73 Using Variables in the For Statement .