Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

by Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham
Released March 2004
Publisher(s): Microsoft Press
ISBN: 9780735619852

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

Take your Microsoft Office experience to the next level by programming custom business solutions with Excel 2003. This supremely organized reference and tutorial packs hundreds of timesaving answers, tips, and workarounds for advanced Excel users.

Table of contents

  1. Microsoft® Office Excel 2003 Programming Inside Out
  2. A Note Regarding Supplemental Files
  3. Acknowledgments
  4. We’d Like to Hear from You
  5. About the CD
    1. What’s on the CD
    2. Using the CD
    3. System Requirements
    4. Support Information
  6. Conventions and Features Used in this Book
    1. Text Conventions
    2. Design Conventions
  7. 1. Preliminaries
    1. 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
        1. Creating Excel Lists
        2. Mapping Excel Data to XML Schemas
        3. Publishing Spreadsheets with Web Components
    2. 2. Exploring Excel
      1. Workbooks
      2. Worksheets
      3. Cells and Ranges
      4. The Excel Interface
      5. Formulas
  8. 2. Visual Basic for Applications
    1. 3. Exploring Visual Basic for Applications
      1. An Overview of Object-Oriented Programming
        1. Properties
        2. Methods
        3. Events
        4. Collections
      2. Working with Macros
        1. Recording and Viewing Macros
        2. Running a Macro
          1. Assigning a Macro to a Key Sequence
          2. Assigning a Macro to a Toolbar Button
          3. Assigning a Macro to a Menu Item
        3. Debugging a Macro
        4. Implementing Macro Security and Digital Signatures
          1. Introducing Digital Signatures
          2. Digitally Signing Workbooks and Macros
          3. Using Digital Signatures with the High Macro Security Setting
    2. 4. VBA Programming Starter Kit
      1. Introducing the Visual Basic Editor
        1. Opening the Visual Basic Editor
        2. Recognizing Parts of the Visual Basic Editor
          1. The View Menu
          2. The Insert Menu
          3. The Format Menu
          4. The Debug Menu
          5. The Run Menu
          6. The Tools Menu
        3. Customizing the Visual Basic Editor
      2. Managing Code Windows
        1. Handling Windows
        2. Adding Code
        3. Notes on Programming Style and Code Readability
      3. Developing Projects with the Project Explorer
        1. Creating VBA Modules
        2. Deleting VBA Modules
        3. Copying Modules Between Projects
      4. Declaring Variables, Constants, and Data Types
        1. Declaring Variables
        2. Defining Data Types
        3. Defining Constants
        4. Handling Strings
        5. Handling Dates and Times
        6. Handling Variants and Data Type Conversions
        7. Understanding Variable Scope and Lifetimes
      5. Assigning Values to Variables
      6. Working with Arrays
        1. Creating an Array
        2. Creating Multidimensional Arrays
      7. Creating Dynamic Arrays
      8. Writing to the Screen and Accepting Input
        1. Creating a Message Box
        2. Creating an Input Box
      9. Defining Object Variables
      10. Creating Custom Data Types
        1. With...End With Command
      11. Controlling Program Flow
        1. Selection Statements
          1. If...Then...Else Statement
          2. Select Case Statements
        2. Loops
          1. Iteration Loops
          2. Logical Loops
        3. GoTo Statement
      12. Error Handling
    3. 5. Creating Sub and Function Procedures
      1. Defining Sub Procedures
        1. Defining the Scope of a Sub Procedure
        2. Running a Sub Procedure from Within Another Procedure
        3. Retaining Values Between Procedure Calls
      2. Defining Function Procedures
        1. Creating a Function Procedure
        2. Running Function Procedures
        3. Passing Arguments to Procedures
        4. Passing Named Arguments
      3. Organizing for Success
  9. 3. The Excel Object Model
    1. 6. The Application Object
      1. Introducing the Application Object
        1. Properties
          1. ActiveCell Property
          2. ActiveChart Property
          3. ActiveSheet Property
          4. ActiveWindow Property
          5. ActiveWorkbook Property
          6. DisplayAlerts Property
          7. RangeSelection Property
          8. Selection Property
          9. StatusBar Property
          10. ScreenUpdating Property
          11. ThisWorkbook Property
        2. Methods
          1. Calculate Method
          2. CalculateFull Method
          3. FindFile Method
          4. InputBox Method
          5. Intersect Method
          6. OnKey Method
          7. SendKeys Method
    2. 7. Workbooks and Worksheets
      1. The Workbooks Collection
        1. Creating New Workbooks
        2. Opening Workbooks
          1. Saving Workbooks
          2. Activating Workbooks
          3. Closing Workbooks
        3. Workbook Properties
          1. Using the ActiveChart Property
          2. Displaying Drawing Objects
          3. Managing File Settings
          4. Requiring a Password to Open a Workbook
          5. Protecting Workbooks from Changes
        4. Workbook Methods
          1. Activate Method
          2. AddToFavorites Method
          3. FollowHyperlink Method
        5. Printing and Previewing Workbooks
      2. The Sheets and Worksheets Collections
        1. Properties
          1. Count Property
          2. Name Property
          3. Visible Property
        2. Methods
          1. Add Method
          2. Delete Method
          3. Move and Copy Methods
          4. PageSetup Object
        3. Worksheet Methods
          1. Calculate Method
          2. Protect Method
          3. SaveAs Method
          4. Select Method
    3. 8. Ranges and Cells
      1. Basic Range Manipulations
        1. Finding the Active Range
        2. Selecting a Range
      2. Referring to Ranges
        1. Referencing Ranges on the Active Worksheet
        2. Referencing Ranges on an Inactive Worksheet
        3. Referencing Cells in a Range
        4. Referencing Cells Using the Offset Property
        5. Defining a Range Using the Cells Property
        6. Referencing Columns and Rows
        7. Referencing Non-Contiguous Groups of Cells
      3. Manipulating Groups of Cells
        1. Resizing Cells Programmatically
        2. Joining Two Ranges Together
        3. Detecting Empty Cells
      4. Using Named Ranges
        1. Defining a Named Range
        2. Changing Notation Styles
        3. Reserved Range Names
        4. Copying Data Between Ranges and Arrays
      5. Getting Data Entry Right the First Time
  10. 4. Advanced VBA
    1. 9. Manipulating Data with VBA
      1. Manipulating Text
        1. Determining if the Value in a Cell Is Text
        2. Preparing String Data for Processing
        3. Determining the Number of Characters in a String
        4. Concatenating Text from Two or More Cells or Variables
        5. Returning the First or Last Several Characters from a String
        6. Returning Characters from Arbitrary Positions in a String
        7. Finding a String Within Another String
      2. Manipulating Numbers
        1. Performing Summary Calculations
        2. Performing Financial Calculations
          1. Determining the Present Value of an Investment
          2. Determining the Net Present Value of an Investment
      3. Manipulating Dates and Times
        1. Time and Date Serial Numbers
        2. Date and Time Functions
    2. 10. Formatting Excel Objects
      1. Using Excel Color Constants and the Color Palette
        1. Manipulating the Current Excel Color Palette
        2. Using the Excel Color Palette on the Web
      2. Formatting Worksheet Elements
      3. Formatting Fonts
      4. Formatting Cells
      5. Formatting Borders
    3. 11. Creating Add-Ins and COM Add-Ins
      1. Introducing Add-Ins
      2. Using the Add-Ins dialog box
        1. Installing an Add-In
        2. Unloading an Add-In
      3. Creating Excel Add-Ins
        1. Creating an Add-In
        2. Saving the Add-In
        3. Installing the Add-In
      4. Using the AddIns Collection
        1. AddIns Collection
        2. AddIn Object
      5. Creating Automation and COM Add-Ins
        1. Using the IDTExtensibility2 Interface
        2. Registry Keys
      6. Building an Automation Add-In with Visual Basic 6
        1. Designing the Add-In
        2. Registry Entries
      7. Building a COM Add-In with Visual Basic .NET
        1. Running the Shared Add-In Wizard
        2. Modifying the Template
        3. Installing the Add-In
    4. 12. Understanding and Using Events
      1. Enabling and Disabling Events
      2. Workbook Events
        1. Open Event
        2. Activate Event
        3. SheetActivate Event
        4. NewSheet Event
        5. BeforeSave Event
        6. Deactivate Event
        7. BeforePrint Event
        8. BeforeClose Event
      3. Worksheet Events
        1. Change Event
        2. SelectionChange Event
        3. BeforeRightClick Event
      4. Application Events
        1. Turning on Application Event Monitoring
        2. Detecting When a Workbook Is Opened
    5. 13. Manipulating Files
      1. Locating External Files
        1. Returning All Files
        2. Limiting the File Search
        3. Finding Files with the FileDialog Dialog Box
      2. Writing to an External File
      3. Reading from an External File
      4. Searching a File for a Value
    6. 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
        1. Accessing Objects
        2. Declaring Objects
        3. Objects and Nothing
        4. Objects with Multiple Object Variables
      7. Properties, Methods, and Events
        1. Public vs. Private Properties, Methods, and Events
        2. Properties
        3. Methods
        4. Events
      8. Building a Class
        1. Creating a Class Module
        2. Defining Simple Properties
        3. Defining Property Routines
        4. Defining Methods
        5. Defining Events
        6. Defining Private Variables, Subroutines, and Functions
        7. Special Events for Classes
        8. Resolving References
      9. Practical Class Design
        1. A Simple Class
        2. Extending a Simple Class
        3. A Collection Class
        4. A Class with Business Rules
  11. 5. Manipulating Excel Objects
    1. 15. Charts
      1. Introducing Charts
        1. Creating Embedded Charts or Chart Sheets
        2. Defining the Chart Object Model
      2. Manipulating Charts
        1. Activating a Chart
        2. Deactivating a Chart
        3. Modifying a Chart’s Data Series
        4. Modifying a Chart to Use Data from Arrays
        5. Defining a Chart’s Labels
        6. Formatting a Chart
        7. Modifying All Charts in a Workbook
        8. Printing Charts
      3. Final Thoughts on Programming Charts
    2. 16. PivotTables and PivotCharts
      1. PivotTables and PivotCharts
        1. Introducing PivotTables
        2. Creating a PivotTable with the PivotTable Wizard
        3. Introducing PivotCharts
        4. Creating a PivotChart with the PivotTable Wizard
        5. Online Analytical Processing (OLAP) Issues
      2. PivotTable Objects
        1. PivotTables Collection
        2. PivotTable Object
        3. PivotCaches Collection
        4. PivotCache Object
        5. PivotField Objects
        6. PivotItem Object
      3. Programming PivotTables
        1. Creating a PivotTable
        2. Creating a PivotChart
      4. Manipulating PivotTables Programmatically
        1. Pivoting a PivotTable Programmatically
        2. Resetting a PivotTable to Its Original Position
        3. Recording and Restoring Arbitrary PivotTable Positions
    3. 17. Command Bars
      1. Identifying Parts of the Menu System
        1. CommandBars Collection
        2. CommandBar Objects
        3. Listing CommandBar Objects
        4. Adding a Floating Command Bar
        5. Deleting a Command Bar
      2. Command Bar Controls
        1. CommandBarControls Collection
        2. CommandBarControl Object
        3. Button Controls
        4. Creating Toolbars
        5. Combo Box Controls
        6. Using a Combo Box
        7. Pop-Up Controls
        8. Displaying a Pop-Up
        9. Adding Items to an Existing Menu
    4. 18. Customizing Dialog Boxes
      1. Displaying Existing Dialog Boxes
      2. Modifying Existing Dialog Boxes
        1. Exploring the Dialogs Collection
        2. Passing Arguments to Existing Dialog Boxes
      3. Planning with Dialog Boxes
    5. 19. Creating User Forms
      1. Creating a UserForm
        1. Adding a UserForm
        2. Designing a UserForm
        3. Modifying a UserForm
        4. Properties of a UserForm
        5. Displaying a UserForm
      2. UserForm Controls
        1. Programming Controls
        2. Common Properties, Methods, and Events
        3. The Label Control
        4. The CommandButton Control
        5. The TextBox Control
        6. The CheckBox Control
        7. The ToggleButton Control
        8. The SpinButton Control
        9. The Frame Control
        10. The OptionButton Control
        11. The Image Control
        12. The ScrollBar Control
        13. The ListBox Control
        14. The ComboBox Control
        15. The RefEdit Control
        16. The TabStrip Control
        17. The MultiPage Control
    6. 20. Creating Advanced User Forms
      1. Capturing Information
        1. Form Application Overview
        2. Designing a Form
        3. Displaying Data
        4. Navigating The Worksheet
        5. Editing Data
        6. Adding Data
        7. Validating Data
        8. Displaying the User Form
      2. Building a Multi-Step Wizard
        1. Wizard Application Overview
        2. Handling Menus
        3. Building the UserForm
        4. Navigating the Pages
        5. Collecting Options for the Wizard
        6. Summarizing the Options
        7. Running the Wizard
  12. 6. Excel and the Outside World: Collaborating Made Easy
    1. 21. Excel and Other Office Applications
      1. Starting Another Application
      2. Activating Another Application
      3. Binding
        1. Late Binding
        2. Early Binding
      4. Interacting with Other Office Applications
        1. Opening a Document in Word
        2. Accessing an Active Word Document
        3. Creating a New Word Document
        4. Controlling Excel from Other Office Applications
      5. Working with Multiple Applications to Get the Job Done
    2. 22. Excel and the Structured Query Language
      1. Comparing Spreadsheets and Databases
        1. Fundamental Database Concepts
        2. Database Keys
        3. Accessing Databases from Excel
      2. Manipulating Databases with SQL
      3. The Select Statement
        1. Simple Select Statements
          1. Retrieving Everything
          2. Retrieving a List of Columns
        2. Retrieving Rows
          1. Using Simple Search Expressions
          2. Using Complex Search Expressions
          3. Using Null
          4. Using the Like Operator
        3. Sorting Rows
        4. Using Multiple Tables
          1. The Wrong Way to Join Two Tables
          2. The Right Way to Join Two Tables
          3. Resolving Column Names
          4. Using Aliases
        5. Using Functions
      4. The Insert Statement
        1. Using the Insert Statement
      5. The Update Statement
      6. The Delete Statement
    3. 23. Introducing ADO
      1. The ADO Object Model
        1. Using the ADO Object Model
      2. The Connection Object
        1. Key Properties and Methods of the Connection Object
        2. Connecting to Different Database Management Systems
          1. Connecting to Access Databases
          2. Connecting to SQL Server Databases
          3. Connecting to Other Databases
        3. Using the Errors Collection
        4. Using the Error Object
      3. The Command Object
        1. Key Properties and Methods of the Command Object
        2. Using the Parameters Collection
        3. Using the Parameter Object
      4. The Recordset Object
        1. Key Properties and Methods of the Recordset Object
        2. Using the Fields Collection
        3. Using the Field Object
    4. 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
        1. Initializing the DBInfo UserForm
        2. Changing Database Providers
        3. Selecting Windows Authentication
        4. Saving the Database Info
      6. Editing a Query
      7. Executing a Database Query
        1. Getting the Information to Run the Query
        2. Building a Connection String
        3. Getting the Query
        4. Running a Query
        5. Copying Rows
      8. Using the Excel Query Program
        1. Configure the Connection Information
        2. Enter a Query
        3. Run the Query
    5. 25. Excel and the Web
      1. HTML
        1. Saving a Worksheet as a Web Page
        2. Publishing a Worksheet to the Web
        3. Making Web-Based Worksheets Interactive
      2. Using the Internet as a Data Source
        1. Opening Web Pages as Workbooks
        2. Using Web Queries
        3. Parsing Web Pages for Specific Information
      3. Using the Internet to Publish Results
        1. Setting Up a Web Server
        2. Saving Worksheets as Web Pages
        3. Adding Interactivity with the Web Components
        4. Communicating with a Web Server
      4. Using Internet Solutions with Excel
    6. 26. Excel and the Extensible Markup Language (XML)
      1. Introducing Data Lists
        1. Creating Data Lists Programmatically
      2. Creating XML Schemas
      3. Creating XML Data Files
      4. Adding XML to a Workbook Manually
        1. Importing XML Data Manually
      5. Adding XML to a Worksheet Programmatically
        1. Mapping a Schema to a Worksheet Programmatically
        2. Mapping Schema Elements to Cells Using XPath
  13. About the Authors
  14. Index
  15. About the Authors
  16. Copyright

Product information

  • Title: Microsoft® Office Excel 2003 Programming Inside Out
  • Author(s): Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham
  • Release date: March 2004
  • Publisher(s): Microsoft Press
  • ISBN: 9780735619852