O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Excel® VBA: 24-Hour Trainer

Excel® VBA: 24-Hour Trainer

by Tom Urtis
Publisher: Wrox
Release Date: May 2011
ISBN: 9780470890691
View table of contents
Start reading

Book Description

This unique book-and-video package of Excel® VBA: 24-Hour Trainer, helps even non-programmers extend Excel with VBA

Virtually every manual task in Excel can be automated with VBA, which increases your productivity and saves enormous amounts of time. This unique book-and-video package prepares you to get more out of Excel by using Visual Basic for Applications (VBA) to automate many routine or labor-intensive Excel tasks. Microsoft Excel MVP and author Tom Urtis walks through a series of lessons and illustrations, while the accompanying DVD provides demos and screencasts to complement each lesson.

  • Introduces you to programming with Visual Basic for Applications (VBA), macro composition, and the programming environment

  • Explains events programming, embedded controls, user forms, message boxes, input boxes, looping, and more

  • Shows you how to control other Office applications from Excel, such as Word, Outlook, Access, and PowerPoint

  • Includes enhanced coverage of each lesson on the DVD, which also offers detailed examples

  • Provides ideas for applying VBA to everyday tasks in a way that's both practical and fun

    • Use this book-and-video package to get the total learning experience of VBA for Excel!

    Table of Contents

    1. Copyright
    2. ABOUT THE AUTHOR
    3. ABOUT THE TECHNICAL EDITOR
    4. CREDITS
    5. ACKNOWLEDGMENTS
    6. INTRODUCTION
      1. WHO THIS BOOK IS FOR
      2. WHAT THIS BOOK COVERS
      3. HOW THIS BOOK IS STRUCTURED
      4. WHAT YOU NEED TO USE THIS BOOK
      5. INSTRUCTIONAL VIDEOS ON DVD
      6. CONVENTIONS
      7. SUPPORTING WEBSITES AND CODE
      8. ERRATA
      9. P2P.WROX.COM
    7. I. Understanding the BASICs
      1. 1. Introducing VBA
        1. 1.1. WHAT IS VBA?
        2. 1.2. A BRIEF HISTORY OF VBA
        3. 1.3. WHAT VBA CAN DO FOR YOU
          1. 1.3.1. Automating a Recurring Task
          2. 1.3.2. Automating a Repetitive Task
          3. 1.3.3. Running a Macro Automatically if Another Action Takes Place
          4. 1.3.4. Creating Your Own Worksheet Functions
          5. 1.3.5. Simplifying the Workbook's Look and Feel for Other Users
          6. 1.3.6. Controlling Other Office Applications from Excel
        4. 1.4. LIABILITIES OF VBA
        5. 1.5. TRY IT
      2. 2. Getting Started with Macros
        1. 2.1. COMPOSING YOUR FIRST MACRO
          1. 2.1.1. Accessing the VBA Environment
          2. 2.1.2. Using the Macro Recorder
        2. 2.2. RUNNING A MACRO
          1. 2.2.1. The Macro Dialog Box
          2. 2.2.2. Shortcut Key
        3. 2.3. TRY IT
          1. 2.3.1. Lesson Requirements
          2. 2.3.2. Step-by-Step
      3. 3. Introducing the Visual Basic Editor
        1. 3.1. WHAT IS THE VBE?
        2. 3.2. HOW TO GET INTO THE VBE
        3. 3.3. UNDERSTANDING THE VBE
          1. 3.3.1. The Project Explorer Window
          2. 3.3.2. The Code Window
          3. 3.3.3. The Properties Window
          4. 3.3.4. The Immediate Window
        4. 3.4. UNDERSTANDING MODULES
        5. 3.5. USING THE OBJECT BROWSER
        6. 3.6. EXITING THE VBE
        7. 3.7. TRY IT
      4. 4. Working in the VBE
        1. 4.1. TOOLBARS IN THE VBE
        2. 4.2. MACROS AND MODULES
          1. 4.2.1. Locating Your Macros
          2. 4.2.2. Understanding the Code
          3. 4.2.3. Editing a Macro with Comments and Improvements to the Code
          4. 4.2.4. Deleting a Macro
          5. 4.2.5. Inserting a Module
          6. 4.2.6. Renaming a Module
          7. 4.2.7. Deleting a Module
        3. 4.3. LOCKING AND PROTECTING THE VBE
        4. 4.4. TRY IT
          1. 4.4.1. Lesson Requirements
          2. 4.4.2. Step-by-Step
    8. II. Diving Deeper into VBA
      1. 5. Object-oriented Programming — An Overview
        1. 5.1. WHAT "OBJECT-ORIENTED PROGRAMMING" MEANS
        2. 5.2. THE OBJECT MODEL
          1. 5.2.1. Properties
          2. 5.2.2. Methods
          3. 5.2.3. Collections
        3. 5.3. TRY IT
      2. 6. Variables, Data Types, and Constants
        1. 6.1. WHAT IS A VARIABLE?
        2. 6.2. ASSIGNING VALUES TO VARIABLES
        3. 6.3. WHY YOU NEED VARIABLES
        4. 6.4. DATA TYPES
          1. 6.4.1. Understanding the Different Data Types
          2. 6.4.2. Declaring a Variable for Dates and Times
          3. 6.4.3. Declaring a Variable with the Proper Data Type
        5. 6.5. FORCING VARIABLE DECLARATION
        6. 6.6. UNDERSTANDING A VARIABLE'S SCOPE
          1. 6.6.1. Local Macro Level Only
          2. 6.6.2. Module Level
          3. 6.6.3. Application Level
        7. 6.7. CONSTANTS
          1. 6.7.1. Choosing the Scope and Lifetime of Your Constants
        8. 6.8. TRY IT
          1. 6.8.1. Lesson Requirements
          2. 6.8.2. Step-by-Step
      3. 7. Understanding Objects and Collections
        1. 7.1. WORKBOOKS
        2. 7.2. WORKSHEETS
        3. 7.3. CELLS AND RANGES
        4. 7.4. SPECIALCELLS
        5. 7.5. TRY IT
          1. 7.5.1. Lesson Requirements
          2. 7.5.2. Step-by-Step
      4. 8. Making Decisions with VBA
        1. 8.1. UNDERSTANDING LOGICAL OPERATORS
          1. 8.1.1. AND
          2. 8.1.2. OR
          3. 8.1.3. NOT
        2. 8.2. CHOOSING BETWEEN THIS OR THAT
          1. 8.2.1. If...Then
          2. 8.2.2. If...Then...Else
          3. 8.2.3. If...Then...ElseIf
          4. 8.2.4. Select Case
        3. 8.3. GETTING USERS TO MAKE DECISIONS
          1. 8.3.1. Message Boxes
          2. 8.3.2. Input Boxes
        4. 8.4. TRY IT
          1. 8.4.1. Lesson Requirements
          2. 8.4.2. Step-by-Step
    9. III. Beyond the Macro Recorder: Writing Your Own Code
      1. 9. Repeating Actions with Loops
        1. 9.1. WHAT IS A LOOP?
        2. 9.2. TYPES OF LOOPS
          1. 9.2.1. For...Next
          2. 9.2.2. For...Each...Next
          3. 9.2.3. Exiting a For... Loop
          4. 9.2.4. Looping In Reverse with Step
          5. 9.2.5. Do...While
          6. 9.2.6. Do...Until
          7. 9.2.7. Do...Loop...While
          8. 9.2.8. Do...Loop...Until
          9. 9.2.9. While...Wend
        3. 9.3. NESTING LOOPS
        4. 9.4. TRY IT
          1. 9.4.1. Lesson Requirements
          2. 9.4.2. Step-by-Step
      2. 10. Working with Arrays
        1. 10.1. WHAT IS AN ARRAY?
          1. 10.1.1. What Arrays Can Do for You
          2. 10.1.2. Declaring Arrays
        2. 10.2. THE OPTION BASE STATEMENT
        3. 10.3. BOUNDARIES IN ARRAYS
        4. 10.4. DECLARING ARRAYS WITH FIXED ELEMENTS
        5. 10.5. DECLARING DYNAMIC ARRAYS WITH REDIM AND PRESERVE
        6. 10.6. TRY IT
          1. 10.6.1. Lesson Requirements
          2. 10.6.2. Step-by-Step
      3. 11. Automating Procedures with Worksheet Events
        1. 11.1. WHAT IS AN "EVENT"?
        2. 11.2. WORKSHEET EVENTS — AN OVERVIEW
          1. 11.2.1. Where Does the Worksheet Event Code Go?
          2. 11.2.2. Enabling and Disabling Events
        3. 11.3. EXAMPLES OF COMMON WORKSHEET EVENTS
          1. 11.3.1. Worksheet_Change Event
          2. 11.3.2. Worksheet_SelectionChange Event
          3. 11.3.3. Worksheet_BeforeDoubleClick Event
          4. 11.3.4. Worksheet_BeforeRightClick Event
          5. 11.3.5. Worksheet_FollowHyperlink Event
          6. 11.3.6. Worksheet_Activate Event
          7. 11.3.7. Worksheet_Deactivate Event
          8. 11.3.8. Worksheet_Calculate Event
          9. 11.3.9. Worksheet_PivotTableUpdate Event
        4. 11.4. TRY IT
          1. 11.4.1. Lesson Requirements
          2. 11.4.2. Step-by-Step
      4. 12. Automating Procedures with Workbook Events
        1. 12.1. WORKBOOK EVENTS — AN OVERVIEW
          1. 12.1.1. Where Does the Workbook Event Code Go?
          2. 12.1.2. Entering Workbook Event Code
        2. 12.2. EXAMPLES OF COMMON WORKBOOK EVENTS
          1. 12.2.1. Workbook_Open Event
          2. 12.2.2. Workbook_BeforeClose Event
          3. 12.2.3. Workbook_Activate Event
          4. 12.2.4. Workbook_Deactivate Event
          5. 12.2.5. Workbook_SheetChange Event
          6. 12.2.6. Workbook_SheetSelectionChange Event
          7. 12.2.7. Workbook_SheetBeforeDoubleClick Event
          8. 12.2.8. Workbook_SheetBeforeRightClick Event
          9. 12.2.9. Workbook_SheetPivotTableUpdate Event
          10. 12.2.10. Workbook_NewSheet Event
          11. 12.2.11. Workbook_BeforePrint Event
          12. 12.2.12. Workbook_SheetActivate Event
          13. 12.2.13. Workbook_SheetDeactivate Event
          14. 12.2.14. Workbook_BeforeSave Event
        3. 12.3. TRY IT
          1. 12.3.1. Lesson Requirements
          2. 12.3.2. Step-by-Step
      5. 13. Using Embedded Controls
        1. 13.1. WORKING WITH FORMS CONTROLS AND ACTIVEX CONTROLS
          1. 13.1.1. The Forms Toolbar
            1. 13.1.1.1. Buttons
            2. 13.1.1.2. Using Application.Caller with Forms Controls
          2. 13.1.2. The Control Toolbox
            1. 13.1.2.1. CommandButtons
        2. 13.2. TRY IT
          1. 13.2.1. Lesson Requirements
          2. 13.2.2. Step-by-Step
      6. 14. Programming Charts
        1. 14.1. ADDING A CHART TO A CHART SHEET
        2. 14.2. ADDING AN EMBEDDED CHART TO A WORKSHEET
        3. 14.3. MOVING A CHART
        4. 14.4. LOOPING THROUGH ALL EMBEDDED CHARTS
        5. 14.5. DELETING CHARTS
        6. 14.6. RENAMING A CHART
        7. 14.7. TRY IT
          1. 14.7.1. Lesson Requirements
          2. 14.7.2. Step-by-Step
      7. 15. Programming PivotTables and PivotCharts
        1. 15.1. CREATING A PIVOTTABLE REPORT
          1. 15.1.1. Hiding the PivotTable Field List
          2. 15.1.2. Using the Report Filter Area
          3. 15.1.3. Formatting Numbers in the Values Area
          4. 15.1.4. Why It'S Called a Pivottable
        2. 15.2. CREATING A PIVOTCHART
        3. 15.3. UNDERSTANDING PIVOTCACHES
        4. 15.4. MANIPULATING PIVOTFIELDS IN VBA
        5. 15.5. MANIPULATING PIVOTITEMS WITH VBA
        6. 15.6. CREATING A PIVOTTABLES COLLECTION
        7. 15.7. TRY IT
          1. 15.7.1. Lesson Requirements
          2. 15.7.2. Step-by-Step
      8. 16. User Defined Functions
        1. 16.1. WHAT IS A USER DEFINED FUNCTION?
          1. 16.1.1. Characteristics of User Defined Functions
          2. 16.1.2. Anatomy of a UDF
          3. 16.1.3. UDF Examples That Solve Common Tasks
            1. 16.1.3.1. Sum Numbers in Colored Cells
            2. 16.1.3.2. Extract Numbers or Letters from an Alphanumeric String
            3. 16.1.3.3. Extract the Address from a Hyperlink
        2. 16.2. VOLATILE FUNCTIONS
          1. 16.2.1. The Name of the Active Worksheet and Workbook
          2. 16.2.2. UDFs with Conditional Formatting
          3. 16.2.3. Calling Your Function from a Macro
          4. 16.2.4. Adding a Description to the Insert Function Dialog
        3. 16.3. TRY IT
          1. 16.3.1. Lesson Requirements
          2. 16.3.2. Step-by-Step
      9. 17. Debugging Your Code
        1. 17.1. WHAT IS DEBUGGING?
        2. 17.2. WHAT CAUSES ERRORS?
        3. 17.3. WEAPONS OF MASS DEBUGGING
          1. 17.3.1. The Debugging Toolbar
            1. 17.3.1.1. Design Mode
            2. 17.3.1.2. Run
            3. 17.3.1.3. Break
            4. 17.3.1.4. Reset
            5. 17.3.1.5. Stepping through Code
              1. 17.3.1.5.1. Using the Step Into Command
              2. 17.3.1.5.2. Using the Step Over Command
              3. 17.3.1.5.3. Using the Step Out Command
            6. 17.3.1.6. Toggle Breakpoint
            7. 17.3.1.7. Locals Window
            8. 17.3.1.8. Immediate Window
            9. 17.3.1.9. Watch Window
            10. 17.3.1.10. Quick Watch
            11. 17.3.1.11. Call Stack
        4. 17.4. TRAPPING ERRORS
          1. 17.4.1. Error Handler
          2. 17.4.2. Bypassing Errors
        5. 17.5. TRY IT
          1. 17.5.1. Lesson Requirements
          2. 17.5.2. Step-by-Step
    10. IV. Advanced Programming Techniques
      1. 18. Creating UserForms
        1. 18.1. WHAT IS A USERFORM?
        2. 18.2. CREATING A USERFORM
        3. 18.3. DESIGNING A USERFORM
        4. 18.4. ADDING CONTROLS TO A USERFORM
        5. 18.5. SHOWING A USERFORM
        6. 18.6. WHERE DOES THE USERFORM'S CODE GO?
        7. 18.7. CLOSING A USERFORM
          1. 18.7.1. Unloading a UserForm
          2. 18.7.2. Hiding a UserForm
        8. 18.8. TRY IT
          1. 18.8.1. Lesson Requirements
          2. 18.8.2. Step-by-Step
      2. 19. UserForm Controls and Their Functions
        1. 19.1. UNDERSTANDING THE FREQUENTLY USED USERFORM CONTROLS
          1. 19.1.1. CommandButtons
          2. 19.1.2. Labels
          3. 19.1.3. TextBoxes
          4. 19.1.4. ListBoxes
          5. 19.1.5. ComboBoxes
          6. 19.1.6. CheckBoxes
          7. 19.1.7. OptionButtons
          8. 19.1.8. Frames
          9. 19.1.9. MultiPages
        2. 19.2. TRY IT
          1. 19.2.1. Lesson Requirements
          2. 19.2.2. Step-by-Step
      3. 20. Advanced UserForms
        1. 20.1. THE USERFORM TOOLBAR
        2. 20.2. MODAL VERSUS MODELESS
        3. 20.3. DISABLING THE USERFORM'S CLOSE BUTTON
        4. 20.4. MAXIMIZING YOUR USERFORM'S SIZE
        5. 20.5. SELECTING AND DISPLAYING PHOTOGRAPHS ON A USERFORM
        6. 20.6. UNLOADING A USERFORM AUTOMATICALLY
        7. 20.7. PRE-SORTING THE LISTBOX AND COMBOBOX ITEMS
        8. 20.8. POPULATING LISTBOXES AND COMBOBOXES WITH UNIQUE ITEMS
        9. 20.9. DISPLAY A REAL-TIME CHART IN A USERFORM
        10. 20.10. TRY IT
          1. 20.10.1. Lesson Requirements
          2. 20.10.2. Step-by-Step
      4. 21. Class Modules
        1. 21.1. WHAT IS A CLASS?
        2. 21.2. WHAT IS A CLASS MODULE?
        3. 21.3. CREATING YOUR OWN OBJECTS
        4. 21.4. AN IMPORTANT BENEFIT OF CLASS MODULES
        5. 21.5. CREATING COLLECTIONS
        6. 21.6. CLASS MODULES FOR EMBEDDED OBJECTS
        7. 21.7. TRY IT
          1. 21.7.1. Lesson Requirements
          2. 21.7.2. Step-by-Step
      5. 22. Add-Ins
        1. 22.1. WHAT IS AN EXCEL ADD-IN?
        2. 22.2. CREATING AN ADD-IN
        3. 22.3. CONVERTING A FILE TO AN ADD-IN
        4. 22.4. INSTALLING AN ADD-IN
        5. 22.5. CREATING A USER INTERFACE FOR YOUR ADD-IN
        6. 22.6. CHANGING THE ADD-IN'S CODE
        7. 22.7. CLOSING ADD-INS
        8. 22.8. REMOVING AN ADD-IN FROM THE ADD-INS LIST
        9. 22.9. TRY IT
          1. 22.9.1. Lesson Requirements
          2. 22.9.2. Step-by-Step
      6. 23. Managing External Data
        1. 23.1. CREATING QUERYTABLES FROM WEB QUERIES
        2. 23.2. CREATING A QUERYTABLE FOR ACCESS
        3. 23.3. USING TEXT FILES TO STORE EXTERNAL DATA
        4. 23.4. TRY IT
          1. 23.4.1. Lesson Requirements
          2. 23.4.2. Step-by-Step
      7. 24. Data Access with ActiveX Data Objects
        1. 24.1. INTRODUCING ADO
          1. 24.1.1. The Connection Object
          2. 24.1.2. The Recordset Object
          3. 24.1.3. The Command Object
        2. 24.2. AN INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL)
          1. 24.2.1. The SELECT Statement
          2. 24.2.2. The INSERT Statement
          3. 24.2.3. The UPDATE Statement
          4. 24.2.4. The DELETE Statement
        3. 24.3. TRY IT
      8. 25. Not Gone, Not Forgotten
        1. 25.1. USING DIALOG SHEETS
          1. 25.1.1. What Does a Dialog Sheet Look Like?
          2. 25.1.2. Option to Show Message Only Once
        2. 25.2. USING XLM GET.CELL FUNCTIONS
        3. 25.3. USING THE SENDKEYS METHOD
        4. 25.4. TRY IT
          1. 25.4.1. Lesson Requirements
          2. 25.4.2. Step-by-Step
    11. V. Interacting with Other Office Applications
      1. 26. Overview of Office Automation from Excel
        1. 26.1. WHY AUTOMATE ANOTHER APPLICATION?
        2. 26.2. UNDERSTANDING OFFICE AUTOMATION
          1. 26.2.1. Early Binding
          2. 26.2.2. Late Binding
          3. 26.2.3. Which One Is Better?
        3. 26.3. TRY IT
          1. 26.3.1. Lesson Requirements
          2. 26.3.2. Step-by-Step
      2. 27. Working with Word from Excel
        1. 27.1. ACTIVATING A WORD DOCUMENT
          1. 27.1.1. Activating the Word Application
          2. 27.1.2. Opening and Activating a Word Document
        2. 27.2. CREATING A NEW WORD DOCUMENT
        3. 27.3. COPYING AN EXCEL RANGE TO A WORD DOCUMENT
        4. 27.4. PRINTING A WORD DOCUMENT FROM EXCEL
        5. 27.5. IMPORTING A WORD DOCUMENT TO EXCEL
        6. 27.6. TRY IT
          1. 27.6.1. Lesson Requirements
          2. 27.6.2. Step-by-Step
      3. 28. Working with Outlook from Excel
        1. 28.1. OPENING OUTLOOK
        2. 28.2. COMPOSING AN E-MAIL IN OUTLOOK FROM EXCEL
          1. 28.2.1. Creating a MailItem Object
          2. 28.2.2. Transferring an Excel Range to the Body of Your E-mail
          3. 28.2.3. Putting It All Together
        3. 28.3. E-MAILING A SINGLE WORKSHEET
        4. 28.4. TRY IT
          1. 28.4.1. Lesson Requirements
          2. 28.4.2. Step-by-Step
      4. 29. Working with Access from Excel
        1. 29.1. ADDING A RECORD TO AN ACCESS TABLE
        2. 29.2. EXPORTING AN ACCESS TABLE TO AN EXCEL SPREADSHEET
        3. 29.3. CREATING A NEW TABLE IN ACCESS
        4. 29.4. TRY IT
          1. 29.4.1. Lesson Requirements
          2. 29.4.2. Step-by-Step
      5. 30. Working with PowerPoint from Excel
        1. 30.1. CREATING A NEW POWERPOINT PRESENTATION
        2. 30.2. COPYING A WORKSHEET RANGE TO A POWERPOINT SLIDE
        3. 30.3. COPYING CHART SHEETS TO POWERPOINT SLIDES
        4. 30.4. RUNNING A POWERPOINT PRESENTATION FROM EXCEL
        5. 30.5. TRY IT
          1. 30.5.1. Lesson Requirements
          2. 30.5.2. Step-by-Step
      6. A. What's on the DVD?
        1. A.1. SYSTEM REQUIREMENTS
        2. A.2. USING THE DVD
        3. A.3. WHAT'S ON THE DVD?
        4. A.4. TROUBLESHOOTING
        5. A.5. CUSTOMER CARE