Table Of ContentTable of Contents
Cover
Section I: Understanding the BASICs
Lesson 1: Introducing VBA
What Is VBA?
A Brief History of VBA
What VBA Can Do for You
Liabilities of VBA
Try It
Lesson 2: Getting Started with Macros
Composing Your First Macro
Running a Macro
Try It
Lesson 3: Introducing the Visual Basic Editor
What Is the VBE?
How To Get Into the VBE
Understanding the VBE
Understanding Modules
Using the Object Browser
Exiting the VBE
Try It
Lesson 4: Working in the VBE
Toolbars in the VBE
Macros and Modules
Locking and Protecting the VBE
Try It
Section II: Diving Deeper into VBA
Lesson 5: Object-oriented Programming — An Overview
What “Object-Oriented Programming” Means
The Object Model
Try It
Lesson 6: Variables, Data Types, and Constants
What Is a Variable?
Assigning Values to Variables
Why You Need Variables
Data Types
Forcing Variable Declaration
Understanding a Variable’s Scope
Constants
Try It
Lesson 7: Understanding Objects and Collections
Workbooks
Worksheets
Cells and Ranges
SpecialCells
Try It
Lesson 8: Making Decisions with VBA
Understanding Logical Operators
Choosing Between This or That
Getting Users to Make Decisions
Try It
Section III: Beyond the Macro Recorder:
Writing Your Own Code
Lesson 9: Repeating Actions with Loops
What Is a Loop?
Types of Loops
Nesting Loops
Try It
Lesson 10: Working with Arrays
What Is an Array?
The Option Base Statement
Boundaries in Arrays
Declaring Arrays with Fixed Elements
Declaring Dynamic Arrays with ReDim and Preserve
Try It
Lesson 11: Automating Procedures with Worksheet
Events
What Is an “Event”?
Worksheet Events — an Overview
Examples of Common Worksheet Events
Try It
Lesson 12: Automating Procedures with Workbook
Events
Workbook Events — An Overview
Examples of Common Workbook Events
Try It
Lesson 13: Using Embedded Controls
Working With Forms Controls and ActiveX Controls
Try It
Lesson 14: Programming Charts
Adding a Chart to a Chart Sheet
Adding an Embedded Chart to a Worksheet
Moving a Chart
Looping through All Embedded Charts
Deleting Charts
Renaming a Chart
Try It
Lesson 15: Programming PivotTables and PivotCharts
Creating a PivotTable Report
Creating a PivotChart
Understanding PivotCaches
Manipulating PivotFields in VBA
Manipulating PivotItems with VBA
Creating a PivotTables Collection
Try It
Lesson 16: User Defined Functions
What Is a User Defined Function?
Volatile Functions
Try It
Lesson 17: Debugging Your Code
What Is Debugging?
What Causes Errors?
Weapons of Mass Debugging
Trapping Errors
Try It
Section IV: Advanced Programming Techniques
Lesson 18: Creating UserForms
What Is a UserForm?
Creating a UserForm
Designing a UserForm
Adding Controls to a UserForm
Showing a UserForm
Where Does the UserForm’s Code Go?
Closing a UserForm
Try It
Lesson 19: UserForm Controls and Their Functions
Understanding the Frequently Used UserForm Controls
Try It
Lesson 20: Advanced UserForms
The UserForm Toolbar
Modal Versus Modeless
Disabling the UserForm’s Close Button
Maximizing Your UserForm’s Size
Selecting and Displaying Photographs on a UserForm
Unloading a UserForm Automatically
Pre-sorting the ListBox and ComboBox Items
Populating ListBoxes and ComboBoxes with Unique Items
Display a Real-Time Chart in a UserForm
Try It
Lesson 21: Class Modules
What Is a Class?
What Is a Class Module?
Creating Your Own Objects
An Important Benefit of Class Modules
Creating Collections
Class Modules for Embedded Objects
Try It
Lesson 22: Add-Ins
What Is an Excel Add-in?
Creating an Add-In
Converting a File to an Add-In
Installing an Add-In
Creating a User Interface for Your Add-In
Changing the Add-In’s Code
Closing Add-Ins
Removing an Add-In from the Add-Ins List
Try It
Lesson 23: Managing External Data
Creating QueryTables from Web Queries
Creating a QueryTable for Access
Using Text Files to Store External Data
Try It
Lesson 24: Data Access with ActiveX Data Objects
Introducing ADO
An Introduction to Structured Query Language (SQL)
Try It
Lesson 25: Not Gone, Not Forgotten
Using Dialog Sheets
Using XLM Get.Cell Functions
Using the SendKeys Method
Try It
Section V: Interacting with Other Office
Applications
Lesson 26: Overview of Office Automation from Excel
Why Automate Another Application?
Understanding Office Automation
Try It
Lesson 27: Working with Word from Excel
Activating a Word Document
Creating a New Word Document
Copying an Excel Range to a Word Document
Printing a Word Document from Excel
Importing a Word Document to Excel
Try It
Lesson 28: Working with Outlook from Excel
Opening Outlook
Composing an E-mail in Outlook from Excel
E-mailing a Single Worksheet
Try It
Lesson 29: Working with Access from Excel
Adding a Record to an Access Table
Exporting an Access Table to an Excel Spreadsheet
Creating a New Table in Access
Try It
Lesson 30: Working with PowerPoint from Excel
Creating a New PowerPoint Presentation
Copying a Worksheet Range to a PowerPoint Slide
Copying Chart Sheets to PowerPoint Slides
Running a PowerPoint Presentation from Excel
Try It
About the Author
About the Technical Editor
Credits
Acknowledgments
Introduction
Who This Book Is For
What This Book Covers
How This Book Is Structured
What You Need to Use This Book
Instructional Videos on DVD
Conventions
Supporting Websites and Code
Errata
p2p.wrox.com
End-User License Agreement
Description:Increase your productivity and save time and effort with Excel VBA This unique book–and–DVD package prepares you to get more out of Excel by using Visual Basic for Applications (VBA) to automate your routine or labor–intensive Excel tasks. Microsoft Excel MVP and author Tom Urtis walks through
