What is this book about? VBA is the programming language for the Microsoft Office suite and many other applications. VBA gives you complete control of Excel, allowing you to do anything from automating Excel tasks to developing full applications, using Excel as the development environment. Excel 2002 is an important upgrade to the Office suite spreadsheet program. It shows Microsoft's commitment to moving Office to be a web-enabled productivity tool, a rich client for working with web-based data, with new features such as SmartTags and XML support. Based on the successful content and format of Excel 2000 VBA Programmer's Reference, this new edition has been fully updated for Excel 2002. The authors are all Excel MVPs, involved daily in supporting the Excel VBA programmer community. What does this book cover? The first part of the book introduces Excel and VBA, including a VBA primer. The second part offers thematic, succinct, and practical discussions of the features available to Excel VBA programmers, with real-world examples answering frequently asked questions. The third and final part is a complete reference to the Excel, VBE, and Common Office Object Models. Here are just a few of the things you'll learn in this edition: The entire Excel Object Model and the Common Office Object Model Working with PivotTables Accessing and manipulating data sources from Excel with ADO Programming the Visual Basic Editor (VBE) New features in Excel 2002, SmartTags, XML, and the Web Expanded references for Excel versions 97, 2000, and 2002 Who is this book for? This book not only caters for beginner- and intermediate-level programmers with its introductory coverage of VBA and Excel, but also provides advanced information for experienced Excel developers in later chapters and the reference.

John Green works as an independent computer consultant and established his company, Execuplan Consulting, which specializes in developing computer based planning applications and in training. He's led training courses for software applications and operating systems and has been accorded the status of Most Valuable Professional by Microsoft for his contributions to the CompuServe Excel forum and MS Internet newsgroups. Contact him at jgreen@enternet.com.au. Stephen Bullen set-up his own company called Business Modelling Solutions Ltd, which specialises in Excel and Access development and consulting. The BMS web site contains a large number of examples of his work, including tools and utilities to extend Excel's functionality and many examples of Excel development techniques. Stephen can be contacted by email to Stephen@BMSLtd.co.uk. Rob Bovey is a software developer and is founder and president of the custom application development firm, Application Professionals. Rob developed several Addins shipped by Microsoft for Excel. He also co-authored the Microsoft Excel 97 Developers Kit. Microsoft has awarded him the title of Most Valuable Professional each year since 1995. Robert Rosenberg runs his own consulting business which specializes in Microsoft Office advanced training and custom solutions. As a Microsoft Valuable Professional in Excel, he also continually offers advanced online support on Excel on behalf of Microsoft to users of their Internet newsgroups. Robert can be contacted on at rrosenberg@r-cor.com.

Chapter 1: Introduction to Excel.

Early Spreadsheet Macros.

The XLM Macro Language.

Excel 5.

Excel 97.

Excel 2000.

Excel 2002.

Excel 2002 VBA Programmer's Reference.

Version Issues.

What You Need to Use this Book.

Conventions Used.

In Case of a Crisis.…

Feedback.

Questions?

Chapter 2: Primer in Excel VBA

Using the Macro Recorder

User Defined Functions.

The Excel Object Model.

The VBA Language.

Chapter 3: The Application Object.

Globals.

The Active Properties.

Display Alerts.

Screen Updating.

Evaluate.

InputBox.

StatusBar.

SendKeys.

OnTime.

OnKey.

Worksheet Functions.

Caller.

Summary.

Chapter 4: Workbooks and Worksheets.

The Workbooks Collection.

The Sheets Collection.

The Window Object.

Summary.

Chapter 5: Using Ranges.

Activate and Select.

Range Property.

Offset Property.

Resize Property.

SpecialCells Method.

CurrentRegion Property.

End Property.

Summing a Range.

Columns and Rows Properties.

Union and Intersect Methods.

Empty Cells.

Transferring Values between Arrays and Ranges.

Summary.

Chapter 6: Using Names.

Naming Ranges.

Special Names.

Storing Values in Names.

Storing Arrays.

Hiding Names.

Working with Named Ranges.

Searching for a Name.

Summary.

Chapter 7: PivotTables.

Creating a PivotTable Report.

PivotFields.

Pivotitems.

PivotCharts.

External Data Sources.

Chapter 8: Filtered Lists.

Structuring the Data.

Data Form.

AutoFilter.

Advanced Filter.

Summary.

Chapter 9: Generating Charts.

Chart Sheets.

Embedded Charts.

Editing Data Series.

Defining Chart Series with Arrays.

Converting a Chart to use Arrays.

Determining the Ranges used in a Chart.

Chart Labels.

Summary.

Chapter 10: Event Procedures.

Worksheet Events.

Chart Events.

Workbook Events.

Headers and Footers.

Summary.

Chapter 11: Adding Controls.

The Toolbars.

ActiveX Controls.

Forms Toolbar Controls.

Dynamic ActiveX Controls.

Controls on Charts.

Summary.

Chapter 12: Office Files and Folders.

FileSearch.

FileDialog.

Summary.

Chapter 13: UserForms.

Displaying a UserForm.

Creating a UserForm.

Directly Accessing Controls in UserForms.

Stopping the Close Button.

Maintaining a Data List.

Modeless UserForms.

Variable UserForm Name.

Summary.

Chapter 14: Command Bars.

Toolbars, Menu Bars, and Popups.

Excel's Built-In Command Bars.

Controls at All Levels.

Creating New Menus.

Passing Parameter Values.

Deleting a Menu.

Creating a Toolbar.

Pop-Up Menus.

Showing Pop-Up Command Bars.

Disabling Command Bars.

Disabling Shortcut Access to Customize.

Summary.

Chapter 15: Class Modules.

Creating Your Own Objects.

Property Procedures.

Creating Collections.

Encapsulation.

Trapping Application Events.

Embedded Chart Events.

A Collection of UserForm Controls.

Referencing Classes Across Projects.

Summary.

Chapter 16: Addins.

Hiding the Code.

Creating an Addin.

Closing Addins.

Code Changes.

Saving Changes.

Interface Changes.

Installing an Addin.

Addinlnstall Event.

Removing an Addin from the Addins List.

Summary.

Chapter 17: Automation Addins and COM Addins.

Automation Addins.

COM Addins.

Summary.

Chapter 18: SmartTags.

The FileName SmartTag.

Controlling SmartTags with VBA.

The Problems with SmartTags.

Summary.

Chapter 19: Interacting with Other Office Applications.

Establishing the Connection.

Opening a Document in Word.

Accessing an Active Word Document.

Creating a New Word Document.

Access and DAO.

Access, Excel and, Outlook.

When is a Virus not a Virus?

Summary.

Chapter 20: Data Access with ADO.

An Introduction to Structured Query Language (SQL).

An Overview of ADO.

Summary.

Chapter 21: Excel and the Internet.

So What's all the Hype About?

Using the Internet for Storing Workbooks.

Using the Internet as a Data Source.

Using the Internet to Publish Results.

Using the Internet as a Communication Channel.

Summary.

Chapter 22: International Issues.

Changing Windows Regional Settings and the Office XP UI Language.

Responding to Regional Settings and the Windows Language.

Interacting with Excel.

Interacting with Users.

Excel 2002's International Options.

Features That Don't Play by the Rules.

Responding to Office XP Language Settings.

Some Helpful Functions.

Summary.

Chapter 23: Programming the VBE.

Identifying VBE Objects in Code.

Starting Up.

Adding Menu Items to the VBE.

Displaying Built-In Dialogs, UserForms, and Messages.

Working with Code.

Working with UserForms.

Working with References.

COM Addins.

Summary.

Chapter 24: Programming with the Windows API.

Anatomy of an API Call.

Interpreting C-Style Declarations.

Constants, Structures, Handles, and Classes.

What if Something Goes Wrong?

Wrapping API Calls in Class Modules.

Some Example Classes.

Modifying UserForm Styles.

Resizable Userforms.

Other Examples.

Summary.

Appendix A: Excel 2002 Object Model.

Common Properties with Collections and Associated Objects.

Excel Objects and Their Properties, Methods and Events.

Appendix B: VBE Object Model.

Appendix C: Office XP Object Model.

Common Properties with Collections and Associated Objects.

Office Objects and Their Properties and Events.

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.