You are previewing Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step.
O'Reilly logo
Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step

Book Description

Quickly teach yourself how to automate tasks and create custom spreadsheet solutions with Excel 2007 Visual Basic for Applications (VBA). With Step By Step, you set the pace—building and practicing the skills you need, just when you need them!

  • Create macros to automate repetitive tasks

  • Automatically format charts, shapes, and text

  • Manipulate tables and other objects—even build PivotTable reports

  • Write your own functions and procedures

  • Use loops and conditions to add decision logic to macros

  • Build custom command buttons, dialog boxes, and user forms



  • Your all-in-one learning experience includes:

  • Files for building skills and practicing the book’s lessons

  • Fully searchable eBook

  • Windows Vista Product Guide eReference—plus other resources on CD



  • For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

    Table of Contents

    1. Microsoft® Offic Excel® 2007 Visual Basic® for Applications Step by Step
    2. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    3. A Note Regarding Supplemental Files
    4. Features and Conventions of This Book
    5. Using the Book's CD
      1. What's on the CD?
      2. Minimum System Requirements
        1. Step-by-Step Exercises
        2. 2007 Microsoft Office System
      3. Installing the Practice Files
      4. Using the Practice Files
      5. Removing and Uninstalling the Practice Files
    6. Getting Help
      1. Errata & Book Support
      2. More Information
    7. 1. Make a Macro Do Simple Tasks
      1. Chapter at a Glance
      2. What's the Difference Between VBA and a Macro?
      3. Creating a Simple Macro
        1. Format Currency by Using a Built-In Tool
        2. Record a Macro to Format Currency
        3. Run the Macro
        4. Assign a Shortcut Key to the Macro
        5. Look at the Macro
        6. Save the Macro Workbook
      4. Changing Multiple Properties at Once
        1. Create Sidebar Headings with a Command
        2. Record a Macro to Merge Cells Vertically
        3. Eliminate Unnecessary Lines from the Macro
      5. Manipulating Recorded Properties
        1. Record a Macro to Remove Window Elements
        2. Run the Macro from the Visual Basic Editor
        3. Use a Macro to Toggle the Value of a Property
        4. Eliminate Repeated Objects in a Recorded Macro
        5. Run a Macro from the Quick Access Toolbar
      6. Recording Methods in a Macro
        1. Convert a Formula to a Value by Using Menu Commands
        2. Convert a Formula to a Value by Using a Macro
        3. Make a Long Statement More Readable
      7. Trusting Macro-Enabled Workbooks
        1. Designate a Trusted Location for Macros
        2. Designate a Trusted Publisher for Macros
      8. Key Points
    8. 2. Make a Macro Do Complex Tasks
      1. Chapter at a Glance
      2. Task One: Opening the Report File
        1. Open a Text File
        2. Watch a Macro Run by Stepping Through It
        3. Select a File While Running a Macro
      3. Task Two: Filling In Missing Labels
        1. Select Only the Blank Cells
        2. Fill the Selection with Values
        3. Record Filling In the Missing Values
        4. Watch the FillLabels Macro Run
      4. Task Three: Adding a Column of Dates
        1. Add a Constant Date
        2. Step Through the Macro
        3. Prompt for the Date
      5. Task Four: Appending to the Database
        1. Append Data to a Master List
        2. Step Through the AppendData Macro
        3. Record a Relative Movement
        4. Choose Whether to Save Changes While Closing a File
      6. Task Five: Deleting the Worksheet
        1. Create a Macro to Delete the Active Worksheet
        2. Make the Macro Operate Quietly
      7. Assembling the Pieces
        1. Record a Macro That Runs Other Macros
        2. Simplify the Subroutine Statements
      8. Key Points
    9. 3. Explore Workbooks and Worksheets
      1. Chapter at a Glance
      2. What Is an Object?
        1. Objects Come in Collections
        2. Objects Have Properties
        3. Objects Have Methods
        4. Methods Can Change Properties
        5. Properties Can Involve Actions
      3. Understanding Workbooks
        1. Add a New Workbook
        2. Count the Workbooks
        3. Close the Workbooks
        4. Refer to a Single Workbook
        5. Refer to a Workbook by Name
        6. Refer to a Workbook by Pointing
        7. Change a Workbook Property Value
      4. Understanding Worksheets
        1. Add a New Worksheet
        2. Rename and Delete a Worksheet
        3. Look at the Return Value of the Delete Method
        4. Look at the Result of the Add Method
        5. Copy a Worksheet
        6. Manipulate Multiple Worksheets
        7. Declare Variables to Enable Auto Lists
      5. Key Points
    10. 4. Explore Range Objects
      1. Chapter at a Glance
      2. Referring to a Range
        1. Refer to a Range by Using an Address
        2. Refer to a Range as a Collection of Cells
        3. Refer to a Range as a Collection of Rows or Columns
        4. Refer to a Range Based on the Active Cell
        5. Refer to Subsets of a Range
        6. Refer to a Relative Range
      3. Enhancing Recorded Selections
        1. Simplify Select…Selection Pairs
        2. Simplify Select Groups
      4. Entering Values and Formulas into a Range
        1. Relative References
        2. Absolute References
        3. R1C1 Reference Style
        4. Put Values and Formulas into a Range
        5. Construct Formulas to Fill a Grid
      5. Formatting a Range
        1. Add Borders to a Range
        2. Format the Interior of a Range
      6. Key Points
    11. 5. Explore Data Objects
      1. Chapter at a Glance
      2. Working with Excel Tables
        1. Create a New File from an Existing Worksheet
        2. Create a Table from an Internal Source
        3. Create a Table from an External Source
        4. Record a Macro to Manipulate a Table
        5. Manipulate Table Columns
        6. Manipulate Table Totals and Filters
      3. Working with PivotTable Reports
        1. Create a PivotTable Report from an Internal Source
        2. Create a PivotTable Report from an External Source
        3. Record a Macro to Set the PivotTable Structure
        4. Set the PivotTable Structure
        5. Record a Macro to Customize a PivotTable Layout
        6. Customize a PivotTable Layout
        7. Record a Macro to Customize a PivotTable Style
        8. Customize a PivotTable Style
      4. Key Points
    12. 6. Explore Graphical Objects
      1. Chapter at a Glance
      2. Exploring Graphical Objects
        1. Use Worksheet Cells as a Drawing Grid
        2. Add a Gradient Fill to a Cell
        3. Add a Gradient-Filled Shape
        4. Reference a Selected Shape
        5. Use an AutoShape to Create a Logo
        6. Use Grouped Shapes to Create Macro Buttons
      3. Exploring Chart Objects
        1. Create a Chart
        2. Synchronize Two Charts
        3. Format the Plot Area of a Chart
      4. Key Points
    13. 7. Control Visual Basic
      1. Chapter at a Glance
      2. Using Conditionals
        1. Make a Decision
        2. Make a Double Decision
        3. Ask Yourself a Question
        4. Test for a Valid Entry
        5. Ask with a Message
      3. Creating Loops
        1. Loop Through a Collection by Using a For Each Loop
        2. Loop with a Counter by Using a For Loop
        3. Loop Indefinitely by Using a Do Loop
      4. Managing Large Loops
        1. Set a Breakpoint
        2. Set a Temporary Breakpoint
        3. Show Progress in a Loop
      5. Key Points
    14. 8. Extend Excel and Visual Basic
      1. Chapter at a Glance
      2. Creating Custom Functions
        1. Use a Custom Function from a Worksheet
        2. Add Arguments to a Custom Function
        3. Make a Function Volatile
        4. Make Arguments Optional
        5. Use a Custom Function from a Macro
      3. Handling Errors
        1. Syntax Errors
        2. Compiler Errors
        3. Logic Errors
        4. Run-Time Errors
        5. Ignore an Error
        6. Ignore an Error Safely by Using a Subroutine
        7. Add Arguments to Generalize a Subroutine
        8. Check for an Error
        9. Loop Until an Error Goes Away
        10. Trap an Error
      4. Key Points
    15. 9. launch Macros with Events
      1. Chapter at a Glance
      2. Creating Custom Command Buttons
        1. Try the ZoomIn and ZoomOut Macros
        2. Enable the Developer Tab in the Ribbon
        3. Create a Custom Command Button
        4. Link a Command Button to a Macro
        5. Create an Event Handler on Your Own
        6. Make a Button Respond to Mouse Movements
        7. Explore the Visual Basic Project
      3. Handling Worksheet and Workbook Events
        1. Run a Procedure When the Selection Changes
        2. Handle an Event on Any Worksheet
        3. Suppress a Workbook Event
        4. Cancel an Event
      4. Key Points
    16. 10. Use Dialog Box Controls on a Worksheet
      1. Chapter at a Glance
      2. Using a Loan Payment Calculator
        1. Create a Loan Payment Model
        2. Use the Loan Payment Model
      3. Creating an Error-Resistant Loan Payment Calculator
        1. Restrict the Years to a Valid Range
        2. Restrict the Down Payment to Valid Values
        3. Restrict the Interest Rate to Valid Values
      4. Retrieving a Value from a List
        1. Prepare a List of Cars
        2. Retrieve the Price from the List
        3. Set the Column Widths
      5. Protecting the Worksheet
        1. Create an Event Handler for the Combo Box
        2. Protect the Worksheet
      6. Key Points
    17. 11. Create a Custom Form
      1. Chapter at a Glance
      2. Creating a Form's User Interface
        1. Create the Form
        2. Add Option Buttons
        3. Add a Check Box with a Related Text Box
        4. Initialize the Text Box
        5. Add Command Buttons
        6. Set the Tab Order for Controls
      3. Preparing a Form's Functionality
        1. Create Custom Views on a Worksheet
        2. Create a Macro to Switch Views
        3. Dynamically Hide Columns
      4. Implementing a Form
        1. Implement Option Buttons
        2. Implement a Check Box
        3. Check for Errors in an Edit Box
        4. Print the Report
        5. Launch the Form
      5. Key Points
    18. A. A Complete Enterprise Information System
    19. Index
    20. About the Author
    21. SPECIAL OFFER: Upgrade this ebook with O’Reilly