VBA and Macros for Microsoft Excel

Bill Jelen Mr. Excel is an accomplished Excel author and the principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts over 10 million page views annually. Prior to founding MrExcel.com, Jelen spent twelve years in the trenches, working as a financial analyst for finance, marketing, accounting and operations departments of a $500 million public company.Tracy Syrstad works as an Excel and Access Consultant. As part of the Mr.Excel.com Consulting team, she has helped develop VBA solutions for clients around the world. She remembers the painful trek up the VBA learning curve while developing applications for co-workers at a former job. She is co-editor of Holy Macro!, it's 1,600 Excel VBA Examples CD and editor of Dreamboat On Word.

Introduction.

    Getting Results with VBA.

    What Is in This Book.

    A Brief History of Spreadsheets and Macros.

    The Future of VBA and Excel.

    Special Elements and Typographical Conventions.

    Next Steps.

I. FIRST STEPS UP THE VBA LEARNING CURVE.

1. Unleash the Power of Excel with VBA!

    The Power of Excel.

    Barriers to Entry.

    Knowing Your Tools-The Visual Basic Toolbar.

    Macro Security.

  Overview of Recording, Storing, and Running a Macro.

    Running a Macro.

    Understanding the Visual Basic Editor (VBE).

    Examining Code in the Programming Window.

    Running the Macro on Another Day Produces Undesired Results.

    Frustration.

    Next Steps: Learning VBA Is the Solution.

2. This Sounds Like BASIC, So Why Doesn't It Look Familiar?

I Can't Understand This Code. Understanding the Parts of VBA "Speech". Is VBA Really This Hard? No! Examining Recorded Macro Code-Using the VB Editor and Help. Using Debugging Tools to Figure Out Recorded Code. The Ultimate Reference to All Objects, Methods, Properties. Five Easy Tips for Cleaning Up Recorded Code. Putting It All Together-Fixing the Recorded Code. Next Steps.

3. Referring to Ranges.

The Range Object. Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range. Referencing Ranges in Other Sheets. Referencing a Range Relative to Another Range. Using the Cells Property to Select a Range. Using the Offset Property to Refer to a Range. Using the Resize Property to Change the Size of a Range. Using the Columns and Rows Properties to Specify a Range. Using the Union Method to Join Multiple Ranges. Using the Intersect Method to Create a New Range from Overlapping Ranges. Using the IsEmpty Function to Check Whether a Cell Is Empty. Using the CurrentRegion Property to Quickly Select a Data Range. Using the Areas Collection to Return a Non-contiguous Range. Next Steps.

4. User-Defined Functions.

Creating User-Defined Functions. Useful Custom Excel Functions. Next Steps.

5. Looping.

For...Next Loops. Do Loops. The VBA Loop: For Each. Next Steps.

6. R1C1 Style Formulas.

    Referring to Cells: A1 Versus R1C1 References.

    Switching Excel to Display R1C1 Style References.

    The Miracle of Excel Formulas.

    Explanation of R1C1 Reference Style.

    Conditional Formatting-R1C1 Required.

    Arr...