Introduction
Part 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?
Try It
Lesson 4 : Working in the Vbe
Toolbars in the Vbe
Macros and Modules
Understanding the Code
Editing a Macro with Comments and Improvements to the Code
Try It
Part 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
Try It
Lesson 7 : Understanding Objects and Collections
Workbooks
Cells And
Try It
Lesson 8 : Working with Ranges
Working with Noncontiguously Populated Ranges
Try It
Lesson 9 : Making Decisions with Vba
Understanding Logical Operators
Choosing Between This or That
Getting Users to Make Decisions
Try It
Part III : Beyond the Macro Recorder: Writing Your Own Code
Lesson 10 : Repeating Actions with Loops
What is a Loop?
Nesting Loops
Try It
Lesson 11 : Programming Formulas
Understanding A1 and R1c1 References
Programming Your Formula Solutions with Vba
Try It
Lesson 12 : 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 13 : Automating Procedures with Worksheet Events
What is an Event?
Worksheet Events: an Overview
Examples of Common Worksheet Events
Try It
Lesson 14 : Automating Procedures with Workbook Events
Workbook Events: an Overview
Examples of Common Workbook Events
Try It
Lesson 15 : Handling Duplicate Items and Records
Deleting Rows Containing Duplicate Entries
Working with Duplicate Data
Try It
Lesson 16 : Using Embedded Controls
Working with Form Controls And
Activex Controls
Try It
Lesson 17 : Programming Charts
Try It
Lesson 18 : Programming Pivottables and Pivotcharts
Creating a Pivottable Report
Understanding Pivotcaches
Manipulating Pivotfields in Vba
Manipulating Pivotitems with Vba
Creating a Pivottables Collection
Try It
Lesson 19 : User - Defined Functions
What is a User - Defi Ned Function?
Udf Examples that Solve Common Tasks
Volatile Functions
Try It
Lesson 20 : Debugging Your Code
What is Debugging?
What Causes Errors?
Weapons of Mass Debugging
Trapping Errors
Try It
Part IV : Advanced Programming Techniques
Lesson 21 : 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 22 : Userform Controls and Their Functions
Understanding the Frequently Used Userform Controls
Try It
Lesson 23 : 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
Displaying a Real - Time Chart in a Userform
Try It
Lesson 24 : Class Modules
What is a Class?
What is a Class Module?
Creating Your Own Objects
An Important Benefi T of Class Modules
Creating Collections
Class Modules for Embedded Objects
Try It
Lesson 25 : 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
Closing Add - Ins
Removing an Add - in from the Add - Ins List
Try It
Lesson 26 : Managing External Data
Creating Querytables from Web Queries
Creating a Querytable for Access
Using Text Files to Store External Data
Try It
Lesson 27 : Data Access with Activex Data Objects
Introducing Ado
An Introduction to Structured Query Language (Sql)
Try It
Lesson 28 : Impressing Your Boss (Or at Least Your Friends)
Selecting Cells and Ranges
Filtering Dates
Setting Page Breaks for Specifi Ed Areas
Using a Comment to Log Changes in a Cell
Using the Windows Api with Vba
Scheduling Your Workbook for Suicide
Try It
Part V : Interacting with Other Office Applications
Lesson 29 : Overview of Office Automation from Excel
Why Automate Another Application?
Understanding Offi Ce Automation
Try It
Lesson 30 : 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 31 : Working with Outlook from Excel
Opening Outlook
Composing an E - Mail in Outlook from Excel
Putting it All Together
E - Mailing a Single Worksheet
Try It
Lesson 32 : 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 33 : 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
Index
