Excel 2003 VBA Programmer's Reference

Excel 2003 VBA Programmer's Reference

by Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg
Released July 2004
Publisher(s): Wrox
ISBN: 9780764556609

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

What is this book about?

Excel 2003 VBA Programmer's Reference is an updated and expanded version of the two previous editions now with a reference section downloadable from the Web for easy perusal. The book is aimed at Excel users who want to gain more control over their spreadsheets using VBA or who want to develop Excel applications for other users. The book starts with a primer chapter focused on bringing the readers up to speed with Excel and VBA. From there, the book expands to focus on major issues faced by advanced Excel users and developers.

What does this book cover?

In this book, you'll discover how to do the following:

  • Set up applications and convert them to add-ins

  • Package and distribute Excel applications

  • Set up interaction with other Office applications and databases

  • Program the VB Editor and use the Windows API

  • Use VB6 and VB.NET with Excel

  • Set up internationalization

  • Advanced debugging and error handling techniques

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright
  4. About the Authors
  5. Credits
  6. Acknowledgments
    1. Paul Kimmel
    2. Stephen Bullen
    3. Rob Bovey
    4. Robert Rosenberg
  7. Contents
  8. Introduction
    1. Early Spreadsheet Macros
    2. The XLM Macro Language
    3. Excel 5
    4. Excel 97
    5. Excel 2000
    6. Excel 2002
    7. Excel 2003
    8. What This Book Covers
    9. Version Issues
    10. What You Need to Use this Book
    11. Conventions
    12. Source Code
    13. Errata
    14. In Case of a Crisis…
    15. p2p.wrox.com
  9. 1: Primer in Excel VBA
    1. Using the Macro Recorder
    2. User Defined Functions
    3. The Excel Object Model
    4. The VBA Language
    5. Summary
  10. 2: Programming in the VBE
    1. Writing Code
    2. Managing a Project
    3. Modifying Properties
    4. Importing and Exporting Visual Basic Code
    5. Editing
    6. Managing Editor Options
    7. Running and Debugging Code
    8. Using Watches
    9. Using the Object Browser
    10. Summary
  11. 3: The Application Object
    1. Globals
    2. The Active Properties
    3. Display Alerts
    4. Screen Updating
    5. Evaluate
    6. InputBox
    7. StatusBar
    8. SendKeys
    9. OnTime
    10. OnKey
    11. Worksheet Functions
    12. Caller
    13. Summary
  12. 4: Object-Oriented Theory and VBA
    1. Comparing Classes and Interfaces
    2. Defining Methods
    3. Defining Fields
    4. Defining Properties
    5. Defining Events
    6. Information Hiding and Access Modifiers
    7. Encapsulation, Aggregation, and References
    8. Summary
  13. 5: Event Procedures
    1. Worksheet Events
    2. Chart Events
    3. Workbook Events
    4. Headers and Footers
    5. Summary
  14. 6: Class Modules
    1. Creating Your Own Objects
    2. Using Collections
    3. Trapping Application Events
    4. Embedded Chart Events
    5. A Collection of UserForm Controls
    6. Referencing Classes Across Projects
    7. Summary
  15. 7: Writing Bulletproof Code
    1. Using Debug.Print
    2. Using Debug.Assert
    3. A Brief Exemplar of PC Debugging
    4. Creating Reusable Tools with the Debug Object
    5. Raising Errors
    6. Writing Error Handlers
    7. Scaffolding
    8. Writing to the EventLog
    9. Summary
  16. 8: Debugging and Testing
    1. Stepping Through Code
    2. Using Breakpoints
    3. Using Watches
    4. Testing an Expression in the Immediate Window
    5. Resources for Finding Definitions
    6. Viewing the Call Stack
    7. Asserting Application Invariants
    8. Summary
  17. 9: UserForms
    1. Displaying a UserForm
    2. Creating a UserForm
    3. Directly Accessing Controls in UserForms
    4. Stopping the Close Button
    5. Maintaining a Data List
    6. Modeless UserForms
    7. Summary
  18. 10: Adding Controls
    1. The Toolbars
    2. ActiveX Controls
    3. Forms Toolbar Controls
    4. Dynamic ActiveX Controls
    5. Controls on Charts
    6. Summary
  19. 11: Data Access with ADO
    1. An Introduction to Structured Query Language (SQL)
    2. An Overview of ADO
    3. Using ADO with Non-Standard Data Sources
    4. Summary
  20. 12: Creating and Using Add-ins
    1. Hiding the Code
    2. Converting the Workbook to an Add-in
    3. Closing Add-ins
    4. Code Changes
    5. Saving Changes
    6. Installing an Add-in
    7. Add-in Install Event
    8. Removing an Add-in from the Add-ins List
    9. Summary
  21. 13: Automation Addins and COM Addins
    1. Automation Addins
    2. COM Addins
    3. Summary
  22. 14: Customizing the VBE
    1. Identifying VBE Objects in Code
    2. Starting Up
    3. Adding Menu Items to the VBE
    4. Displaying Built-in Dialogs, UserForms, and Messages
    5. Working with Code
    6. Working with UserForms
    7. Working with References
    8. Summary
  23. 15: Interacting with Other Office Applications
    1. Establishing the Connection
    2. Opening a Document in Word
    3. Accessing an Active Word Document
    4. Creating a New Word Document
    5. Access and DAO
    6. Access, Excel and, Outlook
    7. When Is a Virus not a Virus?
    8. Summary
  24. 16: Programming with the Windows API
    1. Anatomy of an API Call
    2. Interpreting C-Style Declarations
    3. Constants, Structures, Handles, and Classes
    4. What if Something Goes Wrong?
    5. Wrapping API Calls in Class Modules
    6. Some Example Classes
    7. Modifying UserForm Styles
    8. Resizable Userforms
    9. Other Examples
    10. Summary
  25. 17: International Issues
    1. Changing Windows Regional Settings and the Office XP UI Language
    2. Responding to Regional Settings and the Windows Language
    3. Interacting with Excel
    4. Interacting with Users
    5. Excel 2003's International Options
    6. Responding to Office XP Language Settings
    7. Some Helpful Functions
    8. Summary
  26. 18: Workbooks and Worksheets
    1. Using the Workbooks Collection
    2. The Sheets Collection
    3. The Window Object
    4. Summary
  27. 19: Using Ranges
    1. Activate and Select
    2. Range Property
    3. Offset Property
    4. Resize Property
    5. SpecialCells Method
    6. CurrentRegion Property
    7. End Property
    8. Summing a Range
    9. Columns and Rows Properties
    10. Union and Intersect Methods
    11. Empty Cells
    12. Transferring Values between Arrays and Ranges
    13. Summary
  28. 20: Using Names
    1. Naming Ranges
    2. Special Names
    3. Storing Values in Names
    4. Storing Arrays
    5. Hiding Names
    6. Working with Named Ranges
    7. Searching for a Name
    8. Summary
  29. 21: Working with Lists
    1. Creating a List
    2. Resizing Lists
    3. Totaling Rows
    4. Converting Lists to a Range
    5. Publishing Lists
    6. Summary
  30. 22: PivotTables
    1. Creating a PivotTable Report
    2. PivotFields
    3. PivotItems
    4. PivotCharts
    5. External Data Sources
    6. Summary
  31. 23: Filtered Lists
    1. Structuring the Data
    2. Data Form
    3. AutoFilter
    4. Advanced Filter
    5. Summary
  32. 24: Generating Charts
    1. Chart Sheets
    2. Embedded Charts
    3. Editing Data Series
    4. Defining Chart Series with Arrays
    5. Converting a Chart to use Arrays
    6. Determining the Ranges used in a Chart
    7. Chart Labels
    8. Summary
  33. 25: Office Files and Folders
    1. FileSearch
    2. FileDialog
    3. Summary
  34. 26: Command Bars
    1. Toolbars, Menu Bars, and Popups
    2. Excel's Built-In Commandbars
    3. Controls at All Levels
    4. Creating New Menus
    5. Passing Parameter Values
    6. Deleting a Menu
    7. Creating a Toolbar
    8. Pop-Up Menus
    9. Showing Pop-Up Command Bars
    10. Disabling Commandbars
    11. Disabling Shortcut Access to Customize
    12. Summary
  35. 27: SmartTags
    1. SmartTag Enhancements
    2. The FileName SmartTag
    3. Controlling SmartTags with VBA
    4. The Problems with SmartTags
    5. Summary
  36. 28: Excel and the Internet
    1. So What's all the Hype About?
    2. Using the Internet for Storing Workbooks
    3. Using the Internet as a Data Source
    4. Using the Internet to Publish Results
    5. Using the Internet as a Communication Channel
    6. Summary
  37. 29: XML and Excel
    1. What Is XML?
    2. What Is XSD?
    3. What Is XMLSS?
    4. Importing XML Data
    5. BlackJack: Data Versatility
    6. Summary
  38. A: Excel 2003 Object Model
    1. Common Properties with Collections and Associated Objects
    2. Excel Objects and Their Properties, Methods and Events
    3. ChartGroup Object and the ChartGroups Collection
    4. ChartObject Object and the ChartObjects Collection
    5. Comment Object and the Comments Collection
    6. CubeField Object and the CubeFields Collection
    7. CustomProperty Object and the CustomProperties Collection
    8. CustomView Object and the CustomViews Collection
    9. DataLabel Object and the DataLabels Collection
    10. DiagramNode Object and the DiagramNodes Collection
    11. Error Object and the Errors Collection
    12. ErrorCheckingOptions Collection Object
    13. FormatCondition Object and the FormatConditions Collection
  39. B: VBE Object Model
    1. Links Between the Excel and VBE Object Models
    2. Common Properties and Methods
    3. AddIn Object and AddIns Collection
    4. AddIn Common Properties
    5. CodeModule Object
    6. CodeModule Common Properties
    7. CodeModule Properties
    8. CodeModule Methods
    9. CodeModule Examples
    10. CodePane Object and CodePanes Collection
    11. CodePane Common Properties
    12. CodePane Properties
    13. CodePane Methods
    14. CodePanes Collection Properties
    15. CommandBarEvents Object
    16. CommandBarEvents Events
    17. CommandBarEvents Examples
    18. Events Object
    19. LinkedWindows Collection
    20. LinkedWindows Collection Methods
    21. Property Object and Properties Collection
    22. Property Common Properties
    23. Reference Object and References Collection
    24. Reference Common Properties
    25. References Collection Methods
    26. References Collection Events
    27. ReferencesEvents Object
    28. ReferencesEvents Events
    29. ReferencesEvents Examples
    30. VBComponent Object and VBComponents Collection
    31. VBE Object
    32. VBProject Object and VBProjects Collection
    33. VBProjects Collection Methods
    34. Window Object and Windows Collection
    35. Window Methods
    36. Windows Collection Methods
    37. Window Examples
  40. C: Office 2003 Object Model
    1. AnswerWizardFiles Collection Object
  41. Index

Product information

  • Title: Excel 2003 VBA Programmer's Reference
  • Author(s): Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg
  • Release date: July 2004
  • Publisher(s): Wrox
  • ISBN: 9780764556609