Excel VBA 24-Hour Trainer, 2nd Edition

Excel VBA 24-Hour Trainer, 2nd Edition

Released March 2015
Publisher(s): Wrox
ISBN: 9781118991374

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master VBA automation quickly and easily to get more out of Excel

Excel VBA 24-Hour Trainer, 2nd Edition is the quick-start guide to getting more out of Excel, using Visual Basic for Applications. This unique book/video package has been updated with fifteen new advanced video lessons, providing a total of eleven hours of video training and 45 total lessons to teach you the basics and beyond. This self-paced tutorial explains Excel VBA from the ground up, demonstrating with each advancing lesson how you can increase your productivity. Clear, concise, step-by-step instructions are combined with illustrations, code examples, and downloadable workbooks to give you a practical, in-depth learning experience and results that apply to real-world scenarios.

This is your comprehensive guide to becoming a true Excel power user, with multimedia instruction and plenty of hands-on practice.

  • Program Excel's newest chart and pivot table object models

  • Manipulate the user interface to customize the look and feel of a project

  • Utilize message boxes, input boxes, and loops to yield customized logical results

  • Interact with and manipulate Word, Access, PowerPoint, and Outlook from Excel

    • If you're ready to get more out of this incredibly functional program, Excel VBA 24-Hour Trainer, 2nd Edition provides the expert instruction and fast, hands-on learning you need.

    Table of contents

    1. 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. Conventions
      6. Errata
      7. p2p.wrox.com
    2. Part I: Understanding the BASICs
      1. Lesson 1: Introducing VBA
        1. What is VBA?
        2. A Brief History of VBA
        3. What VBA Can Do for You
        4. Liabilities of VBA
        5. Try It
      2. Lesson 2: Getting Started with Macros
        1. Composing Your First Macro
        2. Running A Macro
        3. Try It
      3. Lesson 3: Introducing the Visual Basic Editor
        1. What is the VBE?
        2. Try It
      4. Lesson 4: Working in the VBE
        1. Toolbars in the VBE
        2. Macros and Modules
        3. Understanding the Code
        4. Editing a Macro with Comments and Improvements to the Code
        5. Try It
    3. Part II: Diving Deeper into VBA
      1. Lesson 5: Object-Oriented Programming: An Overview
        1. What “Object-Oriented Programming” Means
        2. The Object Model
        3. Try It
      2. Lesson 6: Variables, Data Types, and Constants
        1. What is a Variable?
        2. Assigning Values to Variables
        3. Why You Need Variables
        4. Data Types
        5. Forcing Variable Declaration
        6. Understanding a Variable's Scope
        7. Try It
      3. Lesson 7: Understanding Objects and Collections
        1. Workbooks
        2. Cells and Ranges
        3. Try It
      4. Lesson 8: Working with Ranges
        1. Working with Contiguously Populated Ranges
        2. Working with Noncontiguously Populated Ranges
        3. Try It
      5. Lesson 9: Making Decisions with VBA
        1. Understanding Logical Operators
        2. Choosing between this or that
        3. Getting Users to Make Decisions
        4. Try It
    4. Part III: Beyond the Macro Recorder: Writing Your Own Code
      1. Lesson 10: Repeating Actions with Loops
        1. What is a Loop?
        2. Nesting Loops
        3. Try It
      2. Lesson 11: Programming Formulas
        1. Understanding A1 and R1C1 References
        2. Programming Your Formula Solutions with VBA
        3. Try It
      3. Lesson 12: Working with Arrays
        1. What is an Array?
        2. The Option Base Statement
        3. Boundaries in Arrays
        4. Declaring Arrays with Fixed Elements
        5. Declaring Dynamic Arrays with Redim and Preserve
        6. Try It
      4. Lesson 13: Automating Procedures with Worksheet Events
        1. What Is an Event?
        2. Worksheet Events: An Overview
        3. Examples of Common Worksheet Events
        4. Try It
      5. Lesson 14: Automating Procedures with Workbook Events
        1. Workbook Events: An Overview
        2. Examples of Common Workbook Events
        3. Try It
      6. Lesson 15: Handling Duplicate Items and Records
        1. Deleting Rows Containing Duplicate Entries
        2. Working with Duplicate Data
        3. Try It
      7. Lesson 16: Using Embedded Controls
        1. Working with Form Controls and ActiveX Controls
        2. Try It
      8. Lesson 17: Programming Charts
        1. Adding a Chart to a Chart Sheet
        2. Adding an Embedded Chart to a Worksheet
        3. Moving a Chart
        4. Looping Through All Embedded Charts
        5. Try It
      9. Lesson 18: Programming PivotTables and PivotCharts
        1. Creating a PivotTable Report
        2. Understanding PivotCaches
        3. Manipulating PivotFields in VBA
        4. Manipulating PivotItems with VBA
        5. Creating a PivotTables Collection
        6. Try It
      10. Lesson 19: User-Defined Functions
        1. What Is a User-Defined Function?
        2. UDF Examples That Solve Common Tasks
        3. Volatile Functions
        4. Try It
      11. Lesson 20: Debugging Your Code
        1. What Is Debugging?
        2. What Causes Errors?
        3. Weapons of Mass Debugging
        4. Trapping Errors
        5. Try It
    5. Part IV: Advanced Programming Techniques
      1. Lesson 21: Creating UserForms
        1. What Is a UserForm?
        2. Creating a UserForm
        3. Designing a UserForm
        4. Adding Controls to a UserForm
        5. Showing a UserForm
        6. Where Does the UserForm's Code Go?
        7. Closing a UserForm
        8. Try It
      2. Lesson 22: UserForm Controls and Their Functions
        1. Understanding the Frequently Used UserForm Controls
        2. Try It
      3. Lesson 23: Advanced UserForms
        1. The UserForm Toolbar
        2. Modal versus Modeless
        3. Disabling the UserForm's Close Button
        4. Maximizing Your UserForm's Size
        5. Selecting and Displaying Photographs on a UserForm
        6. Unloading a UserForm Automatically
        7. Pre-sorting the ListBox and ComboBox Items
        8. Populating ListBoxes and ComboBoxes with Unique Items
        9. Displaying a Real-Time Chart in a UserForm
        10. Try It
      4. Lesson 24: Class Modules
        1. What Is a Class?
        2. What Is a Class Module?
        3. Creating Your Own Objects
        4. An Important Benefit of Class Modules
        5. Creating Collections
        6. Class Modules for Embedded Objects
        7. Try It
      5. Lesson 25: Add-Ins
        1. What Is an Excel Add-In?
        2. Creating an Add-In
        3. Converting a File to an Add-In
        4. Installing an Add-In
        5. Creating a User Interface for Your Add-In
        6. Closing Add-Ins
        7. Removing an Add-In from the Add-Ins List
        8. Try It
      6. Lesson 26: Managing External Data
        1. Creating QueryTables from Web Queries
        2. Creating a QueryTable for Access
        3. Using Text Files to Store External Data
        4. Try It
      7. Lesson 27: Data Access with ActiveX Data Objects
        1. Introducing ADO
        2. An Introduction to Structured Query Language (SQL)
        3. Try It
      8. Lesson 28: Impressing Your Boss (or at Least Your Friends)
        1. Selecting Cells and Ranges
        2. Filtering Dates
        3. Setting Page Breaks for Specified Areas
        4. Using a Comment to Log Changes in a Cell
        5. Using the Windows API with VBA
        6. Scheduling Your Workbook for Suicide
        7. Try It
    6. Part V: Interacting with Other Office Applications
      1. Lesson 29: Overview of Office Automation from Excel
        1. Why Automate Another Application?
        2. Understanding Office Automation
        3. Try It
      2. Lesson 30: Working with Word from Excel
        1. Activating a Word Document
        2. Creating a New Word Document
        3. Copying an Excel Range to a Word Document
        4. Printing a Word Document from Excel
        5. Importing a Word Document to Excel
        6. Try It
      3. Lesson 31: Working with Outlook from Excel
        1. Opening Outlook
        2. Composing an E-mail in Outlook from Excel
        3. Putting It All Together
        4. E-mailing a Single Worksheet
        5. Try It
      4. Lesson 32: Working with Access from Excel
        1. Adding a Record to an Access Table
        2. Exporting an Access Table to an Excel Spreadsheet
        3. Creating a New Table in Access
        4. Try It
      5. Lesson 33: Working with PowerPoint from Excel
        1. Creating a New PowerPoint Presentation
        2. Copying a Worksheet Range to a PowerPoint Slide
        3. Copying Chart Sheets to PowerPoint Slides
        4. Running a PowerPoint Presentation from Excel
        5. Try It
      6. Advertisement
    7. End User License Agreement

    Product information

    • Title: Excel VBA 24-Hour Trainer, 2nd Edition
    • Author(s):
    • Release date: March 2015
    • Publisher(s): Wrox
    • ISBN: 9781118991374