Microsoft® Office Excel 2003 Programming Inside Out

Larger Cover

By Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher: Microsoft Press

Released: February 2004

Pages: 624

You’re a sophisticated user of Excel—a real pro with spreadsheets—so now dig into its powerful programming capabilities and really put your data to work! From writing code in Microsoft Visual Basic for Applications (VBA) to taking advantage of new XML data-sharing features, this book packs hundreds of timesaving programming solutions, troubleshooting tips, and handy workarounds all in concise, fast-answer format.

Discover the best and fastest ways to build custom business solutions with Excel—and challenge yourself to new levels of mastery!

From automating simple tasks to building custom solutions, write better code with VBA
Learn best practices for writing and debugging your own macros
Use VBA code to manage dates and times, reformat worksheets, and locate external files
Extend your Excel solutions by developing your own add-ins
Get powerful perspectives on data by programmatically manipulating PivotTable and PivotChart dynamic views
Create custom command bars, dialog boxes, and user forms
Employ ActiveX Data Objects (ADO) to access external databases
Exploit the new XML capabilities in Excel 2003

CD features:

Complete eBook in PDF format
Insider Extras—including all the authors’ sample files and code
Catalog of Excel resources with links to third-party tools and demos
Microsoft resources and demos, including Insider’s Guide to Microsoft Office OneNote 2003 eBook
FREE! Microsoft Computer Dictionary, Fifth Edition, eBook—10,000+ entries

A Note Regarding the CD or DVD

The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via O'Reilly Media's Digital Distribution services. To download this content, please visit O'Reilly's web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to booktech@oreilly.com.

Preliminaries
1. Chapter 1 What’s New in Excel 2003
  1. Using Improved Statistical Functions
  2. Creating Smart Documents
  3. Creating Document Workspaces
  4. Implementing Information Rights Management
  5. Comparing Workbooks Side By Side
  6. Getting Information with the Research Task Pane
  7. Using Extended XML Capabilities
2. Chapter 2 Exploring Excel
  1. Workbooks
  2. Worksheets
  3. Cells and Ranges
  4. The Excel Interface
  5. Formulas
Visual Basic for Applications
1. Chapter 3 Exploring Visual Basic for Applications
  1. An Overview of Object-Oriented Programming
  2. Working with Macros
2. Chapter 4 VBA Programming Starter Kit
  1. Introducing the Visual Basic Editor
  2. Managing Code Windows
  3. Developing Projects with the Project Explorer
  4. Declaring Variables, Constants, and Data Types
  5. Assigning Values to Variables
  6. Working with Arrays
  7. Creating Dynamic Arrays
  8. Writing to the Screen and Accepting Input
  9. Defining Object Variables
  10. Creating Custom Data Types
  11. Controlling Program Flow
  12. Error Handling
3. Chapter 5 Creating Sub and Function Procedures
  1. Defining Sub Procedures
  2. Defining Function Procedures
  3. Organizing for Success
The Excel Object Model
1. Chapter 6 The Application Object
  1. Introducing the Application Object
2. Chapter 7 Workbooks and Worksheets
  1. The Workbooks Collection
  2. The Sheets and Worksheets Collections
3. Chapter 8 Ranges and Cells
  1. Basic Range Manipulations
  2. Referring to Ranges
  3. Manipulating Groups of Cells
  4. Using Named Ranges
  5. Getting Data Entry Right the First Time
Advanced VBA
1. Chapter 9 Manipulating Data with VBA
  1. Manipulating Text
  2. Manipulating Numbers
  3. Manipulating Dates and Times
2. Chapter 10 Formatting Excel Objects
  1. Using Excel Color Constants and the Color Palette
  2. Formatting Worksheet Elements
  3. Formatting Fonts
  4. Formatting Cells
  5. Formatting Borders
3. Chapter 11 Creating Add-Ins and COM Add-Ins
  1. Introducing Add-Ins
  2. Using the Add-Ins dialog box
  3. Creating Excel Add-Ins
  4. Using the AddIns Collection
  5. Creating Automation and COM Add-Ins
  6. Building an Automation Add-In with Visual Basic 6
  7. Building a COM Add-In with Visual Basic .NET
4. Chapter 12 Understanding and Using Events
  1. Enabling and Disabling Events
  2. Workbook Events
  3. Worksheet Events
  4. Application Events
5. Chapter 13 Manipulating Files
  1. Locating External Files
  2. Writing to an External File
  3. Reading from an External File
  4. Searching a File for a Value
6. Chapter 14 Developing Class Modules
  1. What Is an Object?
  2. What Is a Class?
  3. What Are Properties?
  4. What Are Methods?
  5. What Are Events?
  6. Introducing Class Modules
  7. Properties, Methods, and Events
  8. Building a Class
  9. Practical Class Design
Manipulating Excel Objects
1. Chapter 15 Charts
  1. Introducing Charts
  2. Manipulating Charts
  3. Final Thoughts on Programming Charts
2. Chapter 16 PivotTables and PivotCharts
  1. PivotTables and PivotCharts
  2. PivotTable Objects
  3. Programming PivotTables
  4. Manipulating PivotTables Programmatically
3. Chapter 17 Command Bars
  1. Identifying Parts of the Menu System
  2. Command Bar Controls
4. Chapter 18 Customizing Dialog Boxes
  1. Displaying Existing Dialog Boxes
  2. Modifying Existing Dialog Boxes
  3. Planning with Dialog Boxes
5. Chapter 19 Creating User Forms
  1. Creating a UserForm
  2. UserForm Controls
6. Chapter 20 Creating Advanced User Forms
  1. Capturing Information
  2. Building a Multi-Step Wizard
Excel and the Outside World: Collaborating Made Easy
1. Chapter 21 Excel and Other Office Applications
  1. Starting Another Application
  2. Activating Another Application
  3. Binding
  4. Interacting with Other Office Applications
  5. Working with Multiple Applications to Get the Job Done
2. Chapter 22 Excel and the Structured Query Language
  1. Comparing Spreadsheets and Databases
  2. Manipulating Databases with SQL
  3. The Select Statement
  4. The Insert Statement
  5. The Update Statement
  6. The Delete Statement
3. Chapter 23 Introducing ADO
  1. The ADO Object Model
  2. The Connection Object
  3. The Command Object
  4. The Recordset Object
4. Chapter 24 Excel Query Program
  1. Excel Query Program Overview
  2. Setting Up the Project
  3. Initializing the Program
  4. Ending the Program
  5. Connecting to a Database
  6. Editing a Query
  7. Executing a Database Query
  8. Using the Excel Query Program
5. Chapter 25 Excel and the Web
  1. HTML
  2. Using the Internet as a Data Source
  3. Using the Internet to Publish Results
  4. Using Internet Solutions with Excel
6. Chapter 26 Excel and the Extensible Markup Language (XML)
  1. Introducing Data Lists
  2. Creating XML Schemas
  3. Creating XML Data Files
  4. Adding XML to a Workbook Manually
  5. Adding XML to a Worksheet Programmatically