Writing Excel Macros with VBA, 2nd Edition

Book description

Newly updated for Excel 2002, Writing Excel Macros with VBA, 2nd Edition provides Excel power-users, as well as programmers who are unfamiliar with the Excel object model, with a solid introduction to writing Visual Basic for Applications (VBA) macros and programs for Excel. In particular, the book focuses on:

  • The Visual Basic Editor and the Excel VBA programming environment. Excel features a complete, state-of-the-art integrated development environment for writing, running, testing, and debugging VBA macros.
The VBA programming language, the same programming language used by the other applications in Microsoft Office XP and 2000, as well as by the retail editions of Visual Basic 6.0. The Excel object model, including new objects and new members of existing objects in Excel 2002. Excel exposes nearly all of its functionality through its object model, which is the means by which Excel can be controlled programmatically using VBA. While the Excel object model, with 192 objects, is the second largest among the Office applications, you need to be familiar with only a handful of objects to write effective macros. Writing Excel Macros focuses on these essential objects, but includes a discussion of many more objects as well.Writing Excel Macros with VBA, 2nd Edition is written in a terse, no-nonsense manner that is characteristic of Steven Roman's straightforward, practical approach. Instead of a slow-paced tutorial with a lot of handholding, Roman offers the essential information about Excel VBA that you must master to write macros effectively. This tutorial is reinforced by interesting and useful examples that solve common problems you're sure to have encountered.Writing Excel Macros with VBA, 2nd Edition is the book you need to delve into the basics of Excel VBA programming, enabling you to increase your power and productivity.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Preface to the Second Edition
    2. The Book’s Audience
    3. Organization of This Book
    4. The Book’s Text and Sample Code
    5. About the Code
    6. Conventions in this Book
    7. Obtaining the Sample Programs
    8. How to Contact Us
    9. Acknowledgments
  3. 1. Introduction
    1. 1.1. Selecting Special Cells
    2. 1.2. Setting a Chart’s Data Point Labels
    3. 1.3. Topics in Learning Excel Programming
  4. I. The VBA Environment
    1. 2. Preliminaries
      1. 2.1. What Is a Programming Language?
      2. 2.2. Programming Style
        1. 2.2.1. Comments
        2. 2.2.2. Readability
        3. 2.2.3. Modularity
    2. 3. The Visual Basic Editor, Part I
      1. 3.1. The Project Window
        1. 3.1.1. Project Names
        2. 3.1.2. Project Contents
          1. 3.1.2.1. The ThisWorkbook object
          2. 3.1.2.2. Sheet objects
          3. 3.1.2.3. Standard modules
          4. 3.1.2.4. Class modules
          5. 3.1.2.5. UserForm objects
      2. 3.2. The Properties Window
      3. 3.3. The Code Window
        1. 3.3.1. Procedure and Full-Module Views
        2. 3.3.2. The Object and Procedure List Boxes
          1. 3.3.2.1. A workbook or sheet object
          2. 3.3.2.2. A standard module
          3. 3.3.2.3. A UserForm object
      4. 3.4. The Immediate Window
      5. 3.5. Arranging Windows
        1. 3.5.1. Docking
    3. 4. The Visual Basic Editor, Part II
      1. 4.1. Navigating the IDE
        1. 4.1.1. General Navigation
          1. 4.1.1.1. Navigating the code window at design time
          2. 4.1.1.2. Tracing code
          3. 4.1.1.3. Bookmarks
      2. 4.2. Getting Help
      3. 4.3. Creating a Procedure
      4. 4.4. Run Time, Design Time, and Break Mode
      5. 4.5. Errors
        1. 4.5.1. Design-Time Errors
        2. 4.5.2. Compile-Time Errors
        3. 4.5.3. Run-Time Errors
        4. 4.5.4. Logical Errors
      6. 4.6. Debugging
        1. 4.6.1. Tracing
          1. 4.6.1.1. Stepping into
          2. 4.6.1.2. Step Over (Shift-F8 or choose Step Over from the Debug menu)
          3. 4.6.1.3. Step Out (Ctrl-Shift-F8 or choose Step Out from the Debug menu)
          4. 4.6.1.4. Run To Cursor (Ctrl-F8 or choose Run To Cursor from the Debug menu)
          5. 4.6.1.5. Set Next Statement (Ctrl-F9 or choose Set Next Statement from the Debug menu)
          6. 4.6.1.6. Breaking out of Debug mode
        2. 4.6.2. Watching Expressions
          1. 4.6.2.1. Quick Watch (Shift-F9)
          2. 4.6.2.2. The Locals and Watches windows
      7. 4.7. Macros
        1. 4.7.1. Recording Macros
        2. 4.7.2. Running Macros
  5. II. The VBA Programming Language
    1. 5. Variables, Data Types, and Constants
      1. 5.1. Comments
      2. 5.2. Line Continuation
      3. 5.3. Constants
        1. 5.3.1. Enums
      4. 5.4. Variables and Data Types
        1. 5.4.1. Variable Declaration
        2. 5.4.2. The Importance of Explicit Variable Declaration
          1. 5.4.2.1. Option Explicit
        3. 5.4.3. Numeric Data Types
        4. 5.4.4. Boolean Data Type
        5. 5.4.5. String Data Type
        6. 5.4.6. Date Data Type
        7. 5.4.7. Variant Data Type
        8. 5.4.8. Excel Object Data Types
          1. 5.4.8.1. The generic As Object declaration
          2. 5.4.8.2. The Set statement
        9. 5.4.9. Arrays
          1. 5.4.9.1. The dimension of an array
          2. 5.4.9.2. Dynamic arrays
          3. 5.4.9.3. The UBound function
        10. 5.4.10. Variable Naming Conventions
        11. 5.4.11. Variable Scope
          1. 5.4.11.1. Procedure-level (local) variables
          2. 5.4.11.2. Module-level variables
        12. 5.4.12. Variable Lifetime
          1. 5.4.12.1. Static variables
        13. 5.4.13. Variable Initialization
      5. 5.5. VBA Operators
    2. 6. Functions and Subroutines
      1. 6.1. Calling Functions
      2. 6.2. Calling Subroutines
      3. 6.3. Parameters and Arguments
        1. 6.3.1. Optional Arguments
        2. 6.3.2. Named Arguments
        3. 6.3.3. ByRef Versus ByVal Parameters
      4. 6.4. Exiting a Procedure
      5. 6.5. Public and Private Procedures
      6. 6.6. Project References
        1. 6.6.1. Fully Qualified Procedure Names
    3. 7. Built-in Functions and Statements
      1. 7.1. The MsgBox Function
      2. 7.2. The InputBox Function
      3. 7.3. VBA String Functions
      4. 7.4. Miscellaneous Functions and Statements
        1. 7.4.1. The Is Functions
          1. 7.4.1.1. The IsDate function
          2. 7.4.1.2. The IsEmpty function
          3. 7.4.1.3. The IsNull function
          4. 7.4.1.4. The IsNumeric function
        2. 7.4.2. The Immediate If Function
        3. 7.4.3. The Switch Function
        4. 7.4.4. Units Conversions
        5. 7.4.5. The Beep Statement
      5. 7.5. Handling Errors in Code
        1. 7.5.1. The On Error Goto Label Statement
        2. 7.5.2. The Error Object
        3. 7.5.3. The On Error GoTo 0 Statement
        4. 7.5.4. The On Error Resume Next Statement
        5. 7.5.5. The Resume Statement
    4. 8. Control Statements
      1. 8.1. The If...Then Statement
      2. 8.2. The For Loop
        1. 8.2.1. Exit For
      3. 8.3. The For Each Loop
      4. 8.4. The Do Loop
      5. 8.5. The Select Case Statement
      6. 8.6. A Final Note on VBA
        1. 8.6.1. File-Related Functions
        2. 8.6.2. Date- and Time-Related Functions
        3. 8.6.3. The Format Function
  6. III. Excel Applications and the Excel Object Model
    1. 9. Object Models
      1. 9.1. Objects, Properties, and Methods
        1. 9.1.1. Properties
        2. 9.1.2. Methods
      2. 9.2. Collection Objects
        1. 9.2.1. The Base of a Collection
      3. 9.3. Object Model Hierarchies
      4. 9.4. Object Model Syntax
      5. 9.5. Object Variables
        1. 9.5.1. The With Statement
        2. 9.5.2. Object Variables Save Execution Time
        3. 9.5.3. An Object Variable Is a Pointer
        4. 9.5.4. Freeing an Object Variable: the Nothing Keyword
        5. 9.5.5. The Is Operator
        6. 9.5.6. Default Members
        7. 9.5.7. Global Members
    2. 10. Excel Applications
      1. 10.1. Providing Access to an Application’s Features
        1. 10.1.1. Working with Toolbars and Menus Interactively
        2. 10.1.2. Assigning Macros to Menus and Toolbars
      2. 10.2. Where to Store an Application
        1. 10.2.1. The Excel Startup Folder
        2. 10.2.2. Excel Templates
        3. 10.2.3. Excel Add-Ins
          1. 10.2.3.1. Creating an add-in
          2. 10.2.3.2. Characteristics of an add-in
          3. 10.2.3.3. Debugging add-ins
          4. 10.2.3.4. Deleting an add-in
      3. 10.3. An Example Add-In
        1. 10.3.1. Creating the Source Workbook
        2. 10.3.2. Setting Up the Custom Menus
        3. 10.3.3. Implementing the Features of the Add-In
        4. 10.3.4. Final Steps
    3. 11. Excel Events
      1. 11.1. The EnableEvents Property
      2. 11.2. Events and the Excel Object Model
      3. 11.3. Accessing an Event Procedure
      4. 11.4. Worksheet Events
      5. 11.5. WorkBook Events
      6. 11.6. Chart Events
      7. 11.7. Application Events
      8. 11.8. QueryTable Refresh Events
    4. 12. Custom Menus and Toolbars
      1. 12.1. Menus and Toolbars: An Overview
        1. 12.1.1. Menu Terminology
        2. 12.1.2. The CommandBar Object
        3. 12.1.3. Command-Bar Controls
          1. 12.1.3.1. Popup controls
          2. 12.1.3.2. Button controls
        4. 12.1.4. Adding a Menu Item
      2. 12.2. The CommandBars Collection
      3. 12.3. Creating a New Menu Bar or Toolbar
      4. 12.4. Command-Bar Controls
        1. 12.4.1. Creating a New Command-Bar Control
      5. 12.5. Built-in Command-Bar-Control IDs
      6. 12.6. Example: Creating a Menu
      7. 12.7. Example: Creating a Toolbar
      8. 12.8. Example: Adding an Item to an Existing Menu
      9. 12.9. Augmenting the SRXUtils Application
        1. 12.9.1. Creating the Data Worksheet
        2. 12.9.2. Setting Up the Custom Menus
        3. 12.9.3. Implementing the Features of the Add-in
        4. 12.9.4. Closing Any Open Add-Ins
    5. 13. Built-In Dialog Boxes
      1. 13.1. The Show Method
    6. 14. Custom Dialog Boxes
      1. 14.1. What Is a UserForm Object?
      2. 14.2. Creating a UserForm Object
      3. 14.3. ActiveX Controls
      4. 14.4. Adding UserForm Code
      5. 14.5. Excel’s Standard Controls
      6. 14.6. Example: The ActivateSheet Utility
        1. 14.6.1. Back to SRXUtils
        2. 14.6.2. Create the UserForm
          1. 14.6.2.1. List box
          2. 14.6.2.2. Activate button
          3. 14.6.2.3. Cancel button
        3. 14.6.3. Create the Code Behind the UserForm
          1. 14.6.3.1. Cancel button code
          2. 14.6.3.2. ActivateSelectedSheet procedure
          3. 14.6.3.3. Activate button code
          4. 14.6.3.4. Double-click lstSheets code
          5. 14.6.3.5. Enter key event
          6. 14.6.3.6. Fill the lstSheets list box
        4. 14.6.4. Trying the Activate Utility
      7. 14.7. ActiveX Controls on Worksheets
        1. 14.7.1. Referring to a Control on a Worksheet
        2. 14.7.2. Adding a Control to a Worksheet Programmatically
    7. 15. The Excel Object Model
      1. 15.1. A Perspective on the Excel Object Model
      2. 15.2. Excel Enums
      3. 15.3. The VBA Object Browser
    8. 16. The Application Object
      1. 16.1. Properties and Methods of the Application Object
        1. 16.1.1. Members that Return Children
        2. 16.1.2. Members that Affect the Display
        3. 16.1.3. Members that Enable Excel Features
        4. 16.1.4. Event-Related Members
          1. 16.1.4.1. OnKey method
          2. 16.1.4.2. OnTime method
        5. 16.1.5. Calculation-Related Members
          1. 16.1.5.1. Calculate method
          2. 16.1.5.2. CalculateFullRebuild method
          3. 16.1.5.3. Calculation property (R/W Long)
          4. 16.1.5.4. CalculateBeforeSave property (R/W Boolean)
          5. 16.1.5.5. CheckAbort method
        6. 16.1.6. File-Related Members
          1. 16.1.6.1. DefaultFilePath property (R/W String)
          2. 16.1.6.2. DefaultSaveFormat property (R/W Long)
          3. 16.1.6.3. FileDialog property
          4. 16.1.6.4. FindFile method
          5. 16.1.6.5. GetOpenFilename method
          6. 16.1.6.6. GetSaveAsFilename method
          7. 16.1.6.7. RecentFiles property (Read-Only)
          8. 16.1.6.8. SaveWorkspace method
        7. 16.1.7. Members that Affect the Current State of Excel
        8. 16.1.8. Members that Produce Actions
          1. 16.1.8.1. ConvertFormula method
          2. 16.1.8.2. Evaluate method
          3. 16.1.8.3. Goto method
          4. 16.1.8.4. Quit method
        9. 16.1.9. Miscellaneous Members
          1. 16.1.9.1. CellFormat, FindFormat and ReplaceFormat object
          2. 16.1.9.2. InputBox method
          3. 16.1.9.3. Selection property
          4. 16.1.9.4. StatusBar property (R/W String)
          5. 16.1.9.5. Intersect method
          6. 16.1.9.6. Union method
      2. 16.2. Children of the Application Object
        1. 16.2.1. Name Objects and the Names Collections
        2. 16.2.2. The Windows Collection and Window Objects
        3. 16.2.3. The WorksheetFunction Object
    9. 17. The Workbook Object
      1. 17.1. The Workbooks Collection
        1. 17.1.1. Add Method
        2. 17.1.2. Close Method
        3. 17.1.3. Count Property
        4. 17.1.4. Item Property
        5. 17.1.5. Open Method
        6. 17.1.6. OpenText Method
      2. 17.2. The Workbook Object
        1. 17.2.1. Activate Method
        2. 17.2.2. Close Method
        3. 17.2.3. DisplayDrawingObjects Property
        4. 17.2.4. FileFormat Property (Read-Only Long )
        5. 17.2.5. Name, FullName, and Path Properties
        6. 17.2.6. HasPassword Property (Read-Only Boolean)
        7. 17.2.7. PrecisionAsDisplayed Property (R/W Boolean)
        8. 17.2.8. PrintOut Method
        9. 17.2.9. PrintPreview Method
        10. 17.2.10. Protect Method
        11. 17.2.11. ReadOnly Property (Read-Only Boolean)
        12. 17.2.12. RefreshAll Method
        13. 17.2.13. Save Method
        14. 17.2.14. SaveAs Method
        15. 17.2.15. SaveCopyAs Method
        16. 17.2.16. Saved Property (R/W Boolean)
      3. 17.3. Children of the Workbook Object
        1. 17.3.1. The CustomView Object
        2. 17.3.2. The Names Collection
        3. 17.3.3. The Sheets Collection
        4. 17.3.4. The Styles Collection and the Style Object
      4. 17.4. Example: Sorting Sheets in a Workbook
    10. 18. The Worksheet Object
      1. 18.1. Properties and Methods of the Worksheet Object
      2. 18.2. Children of the Worksheet Object
      3. 18.3. Protection in Excel XP
        1. 18.3.1. The Protection Object
        2. 18.3.2. The AllowEditRange Object
        3. 18.3.3. The UserAccess Objects
      4. 18.4. Example: Printing Sheets
        1. 18.4.1. Create the UserForm
          1. 18.4.1.1. List box
          2. 18.4.1.2. Print button
          3. 18.4.1.3. Cancel button
        2. 18.4.2. Create the Code Behind the UserForm
          1. 18.4.2.1. The Declarations section
          2. 18.4.2.2. Cancel button code
          3. 18.4.2.3. Print button code
          4. 18.4.2.4. The Form’s Initialize event
          5. 18.4.2.5. The PrintSheets procedure
    11. 19. The Range Object
      1. 19.1. The Range Object as a Collection
      2. 19.2. Defining a Range Object
        1. 19.2.1. Range Property
        2. 19.2.2. Cells Property
        3. 19.2.3. Column, Columns, Row, and Rows Properties
        4. 19.2.4. Offset Property
      3. 19.3. Additional Members of the Range Object
        1. 19.3.1. Activate Method
        2. 19.3.2. AddComment Method
        3. 19.3.3. Address Property (Read-Only String)
        4. 19.3.4. AutoFill Method
        5. 19.3.5. AutoFilter Method
        6. 19.3.6. AutoFit Method
        7. 19.3.7. AutoFormat Method
        8. 19.3.8. BorderAround Method
        9. 19.3.9. Calculate Method
        10. 19.3.10. Clear Methods
        11. 19.3.11. ColumnDifferences and RowDifferences Methods
        12. 19.3.12. ColumnWidth and RowHeight Properties
        13. 19.3.13. Width, Height, Top, and Left Properties
        14. 19.3.14. Consolidate Method
        15. 19.3.15. Copy and Cut Methods
        16. 19.3.16. CopyFromRecordset Method
        17. 19.3.17. CreateNames Method
        18. 19.3.18. CurrentRegion Property
        19. 19.3.19. Delete Method
        20. 19.3.20. Dependents and DirectDependents Properties
        21. 19.3.21. Precedents and DirectPrecedents Properties
        22. 19.3.22. End Property
        23. 19.3.23. EntireColumn and EntireRow Properties
        24. 19.3.24. Fill Methods
        25. 19.3.25. Find Method
        26. 19.3.26. FindNext and FindPrevious Methods
        27. 19.3.27. Formula and FormulaR1C1 Properties
        28. 19.3.28. FormulaArray Property
        29. 19.3.29. FormulaHidden Property (R/W Boolean)
        30. 19.3.30. HasFormula Property (Read-Only)
        31. 19.3.31. HorizontalAlignment Property
        32. 19.3.32. IndentLevel Property and InsertIndent Method
        33. 19.3.33. Insert Method
        34. 19.3.34. Locked Property
        35. 19.3.35. Merge-Related Methods and Properties
        36. 19.3.36. Next and Previous Properties
        37. 19.3.37. NumberFormat Property
        38. 19.3.38. Parse Method
        39. 19.3.39. PasteSpecial Method
        40. 19.3.40. PrintOut Method
        41. 19.3.41. PrintPreview Method
        42. 19.3.42. Replace Method
        43. 19.3.43. Select Method
        44. 19.3.44. ShrinkToFit Property
        45. 19.3.45. Sort Method
        46. 19.3.46. SpecialCells Method
        47. 19.3.47. TextToColumns Method
        48. 19.3.48. Value Property
        49. 19.3.49. WrapText Property
      4. 19.4. Children of the Range Object
        1. 19.4.1. The Areas Collection
        2. 19.4.2. The Borders Collection
        3. 19.4.3. The Border Object
          1. 19.4.3.1. Color property
          2. 19.4.3.2. ColorIndex property
          3. 19.4.3.3. LineStyle property
          4. 19.4.3.4. Weight property
        4. 19.4.4. The Characters Object
        5. 19.4.5. The Comment Object
        6. 19.4.6. The Font Object
        7. 19.4.7. The FormatConditions Collection
        8. 19.4.8. The Interior Object
          1. 19.4.8.1. Color and ColorIndex properties
          2. 19.4.8.2. Pattern property
          3. 19.4.8.3. PatternColor and PatternColorIndex properties
        9. 19.4.9. The PivotField, PivotItem, and PivotTable Objects
        10. 19.4.10. The QueryTable Object
        11. 19.4.11. The Validation Object
      5. 19.5. Example: Getting the Used Range
      6. 19.6. Example: Selecting Special Cells
        1. 19.6.1. Designing the Utility
        2. 19.6.2. Designing the Dialog
          1. 19.6.2.1. The Frame control
          2. 19.6.2.2. Control names
          3. 19.6.2.3. Tab Order
          4. 19.6.2.4. Some final tips
        3. 19.6.3. Writing the Code
    12. 20. Pivot Tables
      1. 20.1. Pivot Tables
      2. 20.2. The PivotTable Wizard
      3. 20.3. The PivotTableWizard Method
      4. 20.4. The PivotTable Object
        1. 20.4.1. Naming Data Fields
        2. 20.4.2. The Complete Code
      5. 20.5. Properties and Methods of the PivotTable Object
        1. 20.5.1. Returning a Fields Collection
          1. 20.5.1.1. ColumnFields property
          2. 20.5.1.2. DataFields property
          3. 20.5.1.3. HiddenFields property
          4. 20.5.1.4. PageFields property
          5. 20.5.1.5. PivotFields property
          6. 20.5.1.6. RowFields property
          7. 20.5.1.7. VisibleFields property
        2. 20.5.2. Totals-Related Members
        3. 20.5.3. Returning a Portion of a PivotTable
          1. 20.5.3.1. ColumnRange property
          2. 20.5.3.2. DataBodyRange property
          3. 20.5.3.3. DataLabelRange property
          4. 20.5.3.4. PageRange and PageRangeCells properties
          5. 20.5.3.5. RowRange property
          6. 20.5.3.6. TableRange1 property
          7. 20.5.3.7. TableRange2 property
        4. 20.5.4. PivotSelect and PivotSelection
        5. 20.5.5. Additional Members of the PivotTable Object
          1. 20.5.5.1. AddFields method
          2. 20.5.5.2. CalculatedFields method
          3. 20.5.5.3. Errors-related properties
          4. 20.5.5.4. Null-related properties
          5. 20.5.5.5. EnableDrillDown property
          6. 20.5.5.6. Formatting properties and methods
          7. 20.5.5.7. Refreshing a pivot table
          8. 20.5.5.8. PageField-related properties
          9. 20.5.5.9. Name property
          10. 20.5.5.10. SaveData property (R/W Boolean)
          11. 20.5.5.11. ShowPages method
          12. 20.5.5.12. SourceData property
      6. 20.6. Children of the PivotTable Object
      7. 20.7. The PivotField Object
        1. 20.7.1. AutoShow-Related Members
        2. 20.7.2. Sorting Pivot Fields
        3. 20.7.3. The Fundamental Properties
          1. 20.7.3.1. Function property
          2. 20.7.3.2. NumberFormat property
          3. 20.7.3.3. Orientation property
          4. 20.7.3.4. Position property
        4. 20.7.4. Selecting Ranges
          1. 20.7.4.1. DataRange property
          2. 20.7.4.2. LabelRange property
        5. 20.7.5. Dragging Pivot Fields
        6. 20.7.6. Name, Value, and SourceName Properties
        7. 20.7.7. Grouping
        8. 20.7.8. Data Field Calculation
          1. 20.7.8.1. Calculation property
          2. 20.7.8.2. Calculations not requiring a BaseField/BaseItem
          3. 20.7.8.3. Calculations requiring a BaseField/BaseItem
        9. 20.7.9. CurrentPage Property
        10. 20.7.10. DataType Property
        11. 20.7.11. HiddenItems and VisibleItems Properties
        12. 20.7.12. MemoryUsed Property
        13. 20.7.13. ServerBased Property
        14. 20.7.14. ShowAllItems Property
        15. 20.7.15. Subtotals Method
      8. 20.8. The PivotCache Object
        1. 20.8.1. Refreshing a Pivot Cache
        2. 20.8.2. MemoryUsed Property
        3. 20.8.3. OptimizeCache Property
        4. 20.8.4. RecordCount Property
        5. 20.8.5. SourceData Property
        6. 20.8.6. Sql Property
      9. 20.9. The PivotItem Object
        1. 20.9.1. DataRange Property
        2. 20.9.2. LabelRange Property
        3. 20.9.3. IsCalculated Property
        4. 20.9.4. Name, Value, and SourceName Properties
        5. 20.9.5. Position Property
        6. 20.9.6. RecordCount Property
        7. 20.9.7. ShowDetail Property
        8. 20.9.8. Visible Property
      10. 20.10. PivotCell and PivotItemList Objects
        1. 20.10.1. ColumnItems, RowItems, and DataField Properties
        2. 20.10.2. CustomSubtotalFunction Property
        3. 20.10.3. PivotCellType Property
        4. 20.10.4. PivotTable, PivotField, and PivotItem Properties
      11. 20.11. Calculated Items and Calculated Fields
      12. 20.12. Example: Printing Pivot Tables
        1. 20.12.1. Create the UserForm
          1. 20.12.1.1. List box
          2. 20.12.1.2. Print button
          3. 20.12.1.3. Cancel button
        2. 20.12.2. Create the Code Behind the UserForm
          1. 20.12.2.1. The Declarations section
          2. 20.12.2.2. Cancel button code
          3. 20.12.2.3. Print button code
          4. 20.12.2.4. The Form’s Initialize event
          5. 20.12.2.5. PrintPTs procedure
    13. 21. The Chart Object
      1. 21.1. Chart Objects and ChartObject Objects
      2. 21.2. Creating a Chart
        1. 21.2.1. Creating Chart Sheets
        2. 21.2.2. Creating Embedded Charts
        3. 21.2.3. An Example of Chart Creation
        4. 21.2.4. Z-Order and ChartObject Objects
      3. 21.3. Chart Types
        1. 21.3.1. ChartType property
          1. 21.3.1.1. ApplyCustomType method
      4. 21.4. Children of the Chart Object
      5. 21.5. The Axes Collection
      6. 21.6. The Axis Object
        1. 21.6.1. AxisGroup Property
        2. 21.6.2. Axis Titles and Their Formatting
          1. 21.6.2.1. HasTitle property (R/W Boolean)
          2. 21.6.2.2. The Border property and the Border object
        3. 21.6.3. CategoryNames Property
        4. 21.6.4. CategoryType Property and BaseUnit Property
        5. 21.6.5. Crosses and CrossesAt Properties
        6. 21.6.6. Display Units
        7. 21.6.7. Gridline-Related Properties and the Gridline Object
        8. 21.6.8. Position- and Dimension-Related Properties
        9. 21.6.9. Tick Mark-Related Properties
          1. 21.6.9.1. The TickLabels object
        10. 21.6.10. Units-Related Properties
        11. 21.6.11. ReversePlotOrder Property
        12. 21.6.12. Type Property
      7. 21.7. The ChartArea Object
      8. 21.8. The ChartGroup Object
        1. 21.8.1. UpBars and DownBars
        2. 21.8.2. DropLines
        3. 21.8.3. HiLoLines
        4. 21.8.4. SeriesCollection and Series Objects
        5. 21.8.5. SeriesLines
      9. 21.9. The ChartTitle Object
      10. 21.10. The DataTable Object
      11. 21.11. The Floor Object
      12. 21.12. The Legend Object
        1. 21.12.1. The LegendEntry Object
        2. 21.12.2. The LegendKey Object
      13. 21.13. The PageSetup Object
      14. 21.14. The PlotArea Object
      15. 21.15. The Series Object
        1. 21.15.1. Adding a New Series
        2. 21.15.2. The DataLabel Object
        3. 21.15.3. The Point Object
          1. 21.15.3.1. Explosion property
          2. 21.15.3.2. MarkerSize and MarkerStyle
      16. 21.16. Properties and Methods of the Chart Object
        1. 21.16.1. ChartWizard Method
        2. 21.16.2. Export Method
        3. 21.16.3. PrintOut Method
      17. 21.17. Example: Scrolling Through Chart Types
      18. 21.18. Example: Printing Embedded Charts
        1. 21.18.1. Create the UserForm
          1. 21.18.1.1. List box
          2. 21.18.1.2. Print button
          3. 21.18.1.3. Cancel button
        2. 21.18.2. Create the Code Behind the UserForm
          1. 21.18.2.1. The Declarations section
          2. 21.18.2.2. Cancel button code
          3. 21.18.2.3. Print button code
          4. 21.18.2.4. The Form’s Initialize event
          5. 21.18.2.5. The PrintCharts procedure
      19. 21.19. Example: Setting Data Series Labels
    14. 22. Smart Tags
      1. 22.1. What Are Smart Tags?
      2. 22.2. SmartTagRecognizer Object
      3. 22.3. SmartTag Object
      4. 22.4. SmartTagAction Object
      5. 22.5. SmartTagOptions Object
  7. IV. Appendixes
    1. A. The Shape Object
      1. A.1. What Is the Shape Object?
      2. A.2. Z-Order
      3. A.3. Creating Shapes
        1. A.3.1. The TextFrame Object
        2. A.3.2. The FillFormat Object
        3. A.3.3. Examples
      4. A.4. Diagram, DiagramNode, and DiagramNodeChildren Objects
    2. B. Getting the Installed Printers
    3. C. Command Bar Controls
      1. C.1. Built-in Command-Bar Controls
    4. D. Face IDs
    5. E. Programming Excelfrom Another Application
      1. E.1. Setting a Reference to the Excel Object Model
      2. E.2. Getting a Reference to the Excel Application Object
        1. E.2.1. An Alternative Approach
          1. E.2.1.1. The CreateObject function
          2. E.2.1.2. The GetObject function
          3. E.2.1.3. No object library reference
    6. F. High-Level and Low-Level Languages
      1. F.1. BASIC
      2. F.2. Visual Basic
      3. F.3. C and C++
      4. F.4. Visual C++
      5. F.5. Pascal
      6. F.6. FORTRAN
      7. F.7. COBOL
      8. F.8. LISP
    7. G. New Objects in Excel XP
      1. AllowEditRange Object
      2. AutoRecover Object
      3. CalculatedMember Object
      4. CellFormat Object
      5. CustomProperty Object
      6. Diagram, DiagramNode and DiagramNodeChildren Objects
      7. Error Object
      8. ErrorCheckingOptions Object
      9. Graphic Object
      10. IRTDServer and IRTDUpdateEvent Objects
      11. PivotCell and PivotItemList Objects
      12. Protection Object
      13. RTD Object
      14. SmartTag Related Objects
      15. Speech Object
      16. SpellingOptions Object
      17. Tab Object
      18. UsedObjects Object
      19. UserAccessList andUserAccess Objects
      20. Watch Object
  8. Index
  9. Colophon
  10. Copyright

Product information

  • Title: Writing Excel Macros with VBA, 2nd Edition
  • Author(s): Steven Roman PhD
  • Release date: June 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596003593