Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

by Stephen Bullen, Rob Bovey, John Green
Released February 2005
Publisher(s): Addison-Wesley Professional
ISBN: 0321262506

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

Finally, there's a book that treats Excel as the powerful development platform it really is, and covers every facet of developing commercial-quality Excel applications.

This is not a book for beginners. Writing for professional developers and true Excel experts, the authors share insider's knowledge they've acquired building Excel applications for many of the world's largest companies—including Microsoft. Professional Excel Development demonstrates how to get the utmost from Excel, addressing everything from application architectures through worksheet and userform design, charting, debugging, error handling and optimizing performance. Along the way, the authors offer best practices for every type of Excel development, from building add-ins through interacting with XML Web services. Coverage includes

  • Building add-ins to provide new Excel functions

  • Designing effective worksheets, userforms and other user interface elements

  • Leveraging Excel's powerful data analysis features

  • Creating sophisticated custom charts

  • Handling errors, debugging applications and optimizing performance

  • Using class modules and interfaces to create custom objects

  • Understanding Windows API calls: when to use them, and how to modify them

  • Adding worksheet functions with

  • C-based XLLs

  • Programming with databases

  • Controlling external applications from Excel

  • Integrating with Visual Basic 6, VB.NET and Visual Studio Tools for Office

  • Using XML to import and export data and communicate with Web services

  • Providing help, securing, packaging and distributing

    • The accompanying CD-ROM contains the book's sample timesheet application at every stage of construction, with detailed code comments. It also includes many examples of the concepts introduced in each chapter and a collection of the authors' Excel development utilities.

    © Copyright Pearson Education. All rights reserved.

    Table of contents

    1. Copyright
    2. Praise for Professional Excel Development
    3. Acknowledgments
    4. About the Authors
      1. Stephen Bullen
      2. Rob Bovey
      3. John Green
    5. 1. Introduction
      1. About This Book
      2. The Excel Developer
      3. Excel as an Application Development Platform
        1. The Worksheet as a Presentation Layer for Data Entry and Reporting
        2. The Worksheet as a Simple Data Store
        3. VBA: Excel's Programming Language
        4. The Worksheet as a Declarative Programming Language
        5. The Excel Object Model
      4. Structure
      5. Examples
      6. Supported Versions
      7. Typefaces
      8. On the CD
      9. Help and Support
      10. Feedback
    6. 2. Application Architectures
      1. Concepts
        1. Codeless Applications
        2. Self-Automated Workbooks
        3. Function and General-Purpose Add-ins
          1. Structure of a Function or General-Purpose Add-in
        4. Application-Specific Add-ins
          1. Structure of an Application-Specific Add-in
        5. Dictator Applications
          1. Requirements of a Dictator Application
          2. Structure of a Dictator Application
        6. Technical Implementations
      2. Conclusion
    7. 3. Excel and VBA Development Best Practices
      1. Naming Conventions
        1. What Is a Naming Convention and Why Is It Important
        2. A Sample Naming Convention
          1. The Scope Specifier (<scope>)
          2. The Array Specifier (<array>)
          3. The Data Type Specifier (<data type>)
          4. Using Descriptive Names
          5. A Few Words About Enumerations
        3. Naming Convention Examples
          1. Variables
          2. Constants
          3. User-Defined Types
          4. Enumerations
        4. Procedures
        5. Modules, Classes and Userforms
        6. Worksheets and Chart Sheets
        7. The Visual Basic Project
        8. Excel UI Naming Conventions
          1. Shapes
          2. Embedded Objects
          3. Defined Names
        9. Exceptions—When Not to Apply the Naming Convention
      2. Best Practices for Application Structure and Organization
        1. Application Structure
          1. The One-Workbook vs. the N-Workbook Application
          2. Separation of Logical Tiers
          3. Separation of Data/UI from Code
        2. Application Organization for Procedural Programming
          1. Organizing Code into Modules by Function/Category
          2. Functional Decomposition
          3. Best Practices for Creating Procedures
      3. General Application Development Best Practices
        1. Code Commenting
          1. Module-Level Comments
          2. Procedure-Level Comments
          3. Internal Comments
          4. Avoiding the Worst Code-Commenting Mistake
        2. Code Readability
        3. VBA Programming Best Practices
          1. General VBA Best Practices
            1. Use of Module Directives
            2. Variables and Constants
              1. Avoid Reusing Variables
              2. Avoid the Variant Data Type
              3. Beware of Evil Type Coercion
              4. Avoid the As New Declaration Syntax
              5. Always Fully Qualify Object Names
              6. Never Hard Code Array Bounds
              7. Always Specify the Loop Counter After a Next Statement
              8. Make Use of Constants
            3. Variable Scope
            4. Early Binding vs. Late Binding
          2. Defensive Coding
            1. Write Your Application in the Earliest Version of Excel That You Expect It to Run In
            2. Explicitly Use ByRef or ByVal
            3. Explicitly Call the Default Property of an Object
            4. Validate Arguments Before Using Them in Procedures
            5. Use Guard Counters to Protect Against Infinite Loops
            6. Use Debug > Compile Early and Often
            7. Use CodeNames to Reference Sheet Objects
            8. Validate the Data Types of Selections
        4. Change Control
          1. Saving Versions
          2. Documenting Changes with Comments
      4. Conclusion
    8. 4. Worksheet Design
      1. Principles of Good Worksheet UI Design
      2. Program Rows and Columns: The Fundamental UI Design Technique
      3. Defined Names
        1. Named Constants
        2. Named Ranges
        3. Named Formulas
        4. Scope of Defined Names
      4. Styles
        1. Advantages of Styles
        2. Creating and Using Styles
        3. Modifying Styles
        4. Adding the Style Drop-Down to the Toolbar
      5. User Interface Drawing Techniques
        1. Using Borders to Create Special Effects
        2. Creating Well-Formatted Tables
        3. Cell Comments for Help Text
        4. Using Shapes
      6. Data Validation
        1. Unique Entries
        2. Cascading Lists
      7. Conditional Formatting
        1. Creating Dynamic Tables
        2. Calling Out Error Conditions
      8. Using Controls on Worksheets
        1. Advantages of Forms Controls
        2. Advantages of ActiveX Controls
      9. Practical Example
        1. Hidden Rows and Columns
        2. Defined Names
        3. Styles
        4. User Interface Drawing Techniques
        5. Data Validation
        6. Conditional Formatting
      10. Conclusion
    9. 5. Function, General and Application-Specific Add-ins
      1. The Four Stages of an Application
        1. Development/Maintenance
        2. Startup
        3. Runtime
        4. Shutdown
      2. Function Library Add-ins
        1. An Example UDF
        2. UDF Naming Conventions
        3. Making Your UDF Appear Native
        4. Creating a Friendly Name and Description for Your Function Library Add-in
        5. Critical UDF Details
        6. VBA UDF Problems
      3. General Add-ins
      4. Application-Specific Add-ins
        1. A Table-Driven Approach to UI Worksheet Management
          1. Table-Driven Methodology Defined
          2. Typical Worksheet User Interface Settings
          3. The Settings Table
          4. The Utility Code
        2. Using VBA to Dynamically Modify Your Worksheet User Interface
      5. Practical Example
        1. Features
          1. Open and Initialize the Application
          2. Build a Toolbar That Gives the User Access to Each Feature
          3. Open and Initialize the Time-Entry Workbook
          4. Save a Copy of the Time-Entry Workbook to a Predefined Consolidation Location
          5. Allow the User to Add More Data-Entry Rows to the Time-Entry Worksheet
          6. Allow the User to Clear the Data-Entry Area So the Timesheet Can Be Reused
          7. Allow the User to Close the PETRAS Application
          8. Add a Custom Property to Allow the Consolidation Application to Locate All Instances of Our Time-Entry Workbook
        2. Application Organization
      6. Conclusion
    10. 6. Dictator Applications
      1. Structure of a Dictator Application
        1. Startup and Shutdown
          1. Version and Dependency Checks
          2. Storing and Restoring Excel Settings
            1. Handling Crashes
          3. Configuring the Excel Environment
            1. Supporting a Debug Mode
        2. Customizing the User Interface
          1. Preparing a Backdrop Graphic
          2. Sheet-Based vs. Form-Based User Interfaces
            1. Handling Cut, Copy and Paste
          3. Custom Command Bars
        3. Processing and Analysis
        4. Presenting Results
      2. Practical Example
        1. PETRAS Timesheet
        2. PETRAS Reporting
          1. Identifying Workbooks
          2. Using the PETRAS Reporting Application
      3. Conclusion
    11. 7. Using Class Modules to Create Objects
      1. Creating Objects
        1. Class Module Structure
          1. Property Procedures
          2. Methods
      2. Creating a Collection
        1. Creating a Collection Object
        2. Addressing Class Collection Shortcomings
          1. Using Visual Basic
          2. Using a Text Editor
      3. Trapping Events
      4. Raising Events
        1. A Family Relationship Problem
        2. Creating a Trigger Class
      5. Practical Example
        1. PETRAS Timesheet
          1. The Template
          2. The Application-Level Event Handler
        2. PETRAS Reporting
      6. Conclusion
    12. 8. Advanced Command Bar Handling
      1. Command Bar Design
      2. Table-Driven Command Bars
        1. Introducing the Table-Driven Command Bar Builder
        2. The Command Bar Definition Table
          1. Command Bar Name
          2. Control Caption
          3. Position
          4. IsMenubar
          5. Visible
          6. Width
          7. Protection
          8. IsTemporary
          9. IsEnabled
          10. OnAction
          11. Control ID
          12. Control Type
          13. Control Style
          14. Face ID
          15. Begin Group
          16. Before
          17. Tooltip
          18. Shortcut Text
          19. Tag
          20. Parameter
          21. State
          22. ListRange and Lists
        3. Post Mortem
      3. Putting It All Together
        1. Adding a Custom Menu with Submenus to the Worksheet Menu Bar
        2. Adding a Custom Toolbar
        3. Adding a Custom Right-Click Command Bar
      4. Loading Custom Icons from Files
        1. Creating Bitmap Files for Icons and Masks
        2. Using Bitmap Files as CommandBarButton Icons
      5. Hooking Command Bar Control Events
        1. Why Use an Event Hook
        2. What Can an Event Hook Do
        3. The Importance of the Tag Property
        4. The Paste Special Command Bar
          1. The Paste Special Toolbar Definition
      6. Practical Example
        1. PETRAS Timesheet
        2. PETRAS Reporting
          1. Application Contexts
      7. Conclusion
    13. 9. Understanding and Using Windows API Calls
      1. Overview
        1. Finding Documentation
        2. Finding Declarations
        3. Finding the Values of Constants
        4. Understanding Handles
        5. Encapsulating API Calls
      2. Working with the Screen
        1. Reading the Screen Resolution
        2. Finding the Size of a Pixel
          1. Device Contexts
      3. Working with Windows
        1. Window Classes
        2. Finding Windows
          1. ANSI vs. Unicode and the Alias Clause
        3. Finding Related Windows
        4. Windows Messages
        5. Changing the Window Icon
        6. Changing Windows Styles
      4. Working with the Keyboard
        1. Checking for Shift, Ctrl, Alt, Caps Lock, Num Lock and Scroll Lock
          1. Bit Masks
        2. Testing for a Key Press
          1. Structures
      5. Working with the File System and Network
        1. Finding the User ID
          1. Buffers
        2. Changing to a UNC Path
        3. Locating Special Folders
        4. Deleting a File to the Recycle Bin
        5. Browsing for a Folder
          1. Callbacks
      6. Practical Examples
        1. PETRAS Timesheet
        2. PETRAS Reporting
      7. Conclusion
    14. 10. Userform Design and Best Practices
      1. Principles
        1. Keep It Simple
        2. Display Canvas, Not Business Rules
        3. Use Classes, Not the Default Instance
        4. Expose Properties and Methods, Not Controls
      2. Control Fundamentals
        1. Naming
        2. Layering
        3. Positioning
        4. Tab Orders and Accelerator Keys
        5. Data Binding
        6. Event Handling
        7. Validation
      3. Visual Effects
        1. Userform Window Styles
        2. Disabling the Close Button
        3. Displaying Graphics, Charts, WordArt and So Forth on Userforms
        4. Locking vs. Disabling Controls
        5. Popup Menus
      4. Userform Positioning and Sizing
        1. Positioning Next to a Cell
        2. Responding to Different Resolutions
        3. Resizable Userforms
        4. Splitter Bars
      5. Wizards
        1. Design Rules for Wizard Dialogs
        2. Creating a Wizard Dialog
      6. Dynamic Userforms
        1. Subset Userforms
        2. Code-Created and Table-Driven Userforms
        3. Scroll Regions
        4. Dynamic Control Event Handling and Control Arrays
      7. Modeless Userforms
        1. Splash Screens
        2. Progress Bars
        3. Combining with Menu Items
      8. Control Specifics
        1. ComboBox
          1. Drop-Down List/Combo
          2. Text Box
          3. Filename Box
          4. Drop-Down Panes
        2. Windows Common Controls
          1. Drag and Drop
      9. Practical Examples
        1. PETRAS Timesheet
        2. PETRAS Reporting
      10. Conclusion
    15. 11. Interfaces
      1. What Is an Interface?
      2. Code Reuse
      3. Defining a Custom Interface
      4. Implementing a Custom Interface
      5. Using a Custom Interface
      6. Polymorphic Classes
      7. Improving Robustness
      8. Simplifying Development
        1. A Progress Bar
          1. The IProgressBar Interface
          2. The FProgressBar Form
          3. The CProgressBar Class
      9. A Plug-in Architecture
      10. Practical Example
        1. PETRAS Timesheet
        2. PETRAS Reporting
      11. Conclusion
    16. 12. VBA Error Handling
      1. Error-Handling Concepts
        1. Unhandled vs. Handled Errors
        2. The Err Object
        3. What Is an Error Handler
        4. Error Handler Scope
        5. The On Error Statement
          1. On Error GoTo <Label>
          2. On Error Resume Next
          3. On Error GoTo 0
        6. The Resume Statement
          1. Resume
          2. Resume Next
          3. Resume <Label>
        7. Raising Custom Errors
      2. The Single Exit Point Principle
      3. Simple Error Handling
      4. Complex Project Error Handler Organization
        1. Procedure Error Handlers
        2. Trivial Procedures
      5. The Central Error Handler
      6. Error Handling in Classes and Userforms
        1. Initialize and Activate Events
        2. Terminate Events
      7. Putting It All Together
      8. Practical Example
        1. PETRAS Timesheet
        2. PETRAS Reporting
      9. Conclusion
    17. 13. Programming with Databases
      1. An Introduction to Databases
        1. Why Use a Database
        2. Relational Databases
        3. File-Based Databases vs. Client-Server Databases
        4. Normalization
          1. First Normal Form
          2. Second Normal Form
          3. Third Normal Form
        5. When Not to Normalize
        6. Relationships and Referential Integrity
          1. Foreign Keys
          2. Types of Relationships
            1. One to One
            2. One to Many
            3. Many to Many
          3. Referential Integrity
        7. Natural vs. Artificial Primary Keys
      2. Designing the Data Access Tier
      3. Data Access with SQL and ADO
        1. An Introduction to ActiveX Data Objects (ADO)
          1. Data Access Technology Defined
        2. ADO Objects
          1. Top-Level Objects
          2. Collections
        3. Connecting to Data Sources
          1. Access
          2. SQL Server
          3. Excel
          4. Using the Connection String
          5. Error Handling Connections
        4. Data Access Techniques
          1. Retrieving Data
          2. Inserting Data
          3. Updating Data
          4. Deleting Data
      4. Further Reading
        1. Professional SQL Server 2000 Database Design
        2. ADO 2.6 Programmer's Reference
        3. Professional ADO 2.5 Programming
        4. Professional SQL Server 2000 Programming
        5. Access 2002 Developer's Handbook Set
      5. Practical Example
        1. PETRAS Timesheet
          1. Modifying the Application to Load Data-Validation Lists from the Database
          2. Modifying the Application to Save Time Entries to the Database
        2. PETRAS Reporting
      6. Conclusion
    18. 14. Data Manipulation Techniques
      1. Excel's Data Structures
        1. Unstructured Ranges
        2. Structured Ranges
        3. Excel 2003's Lists
        4. Query Tables
      2. Data Processing Features
        1. It Doesn't Have to Be Data
        2. Pivot Caches
        3. Pivot Tables
          1. Calculated Pivot Fields
        4. Data Consolidation
        5. Advanced Filtering
          1. Criteria Ranges
      3. Advanced Functions
        1. The Database Functions
        2. Array Formulas
        3. Circular References
      4. Conclusion
    19. 15. Advanced Charting Techniques
      1. Fundamental Techniques
        1. Combining Chart Types
        2. Using Multiple Axes
        3. Using Defined Names to Link Charts to Data
          1. Setting Up the Defined Name Links
          2. Auto-Expanding Charts
          3. Scrolling and Zooming a Time Series
          4. Transforming Coordinate Systems
          5. Charting a Function
        4. Faking It
          1. Error Bars
          2. Dummy XY Series
      2. VBA Techniques
        1. Converting Between Chart Coordinate Systems
        2. Locating Chart Items
        3. Calculating Reasonable Axis Scales
      3. Conclusion
    20. 16. VBA Debugging
      1. Basic VBA Debugging Techniques
        1. Run Mode vs. Break Mode
          1. Break on All Errors
          2. Break in Class Module
          3. Break on Unhandled Errors
        2. Debug Mode
          1. User-Defined Debug Mode
          2. The Stop Statement
          3. Conditional Compilation Constants
        3. Using Break Points (F9)
        4. Stepping Through Code
          1. Step Into (F8)
          2. Step Over (Shift+F8)
          3. Step Out (Ctrl+Shift+F8)
          4. Step to Cursor (Ctrl+F8)
        5. Changing the Execution Point, or Set Next Statement (Ctrl+F9)
      2. The Immediate Window (Ctrl+G)
        1. Debug.Print
        2. Making the Best Use of the Immediate Window
      3. The Call Stack (Ctrl+L)
      4. The Watch Window
        1. Setting a Basic Watch
        2. Using a Basic Watch
        3. Watch Types
          1. Watch Context
            1. Module
            2. Procedure
          2. Watch Type
            1. Watch Expression
            2. Break When Value Is True
            3. Break When Value Changes
        4. Arrays, UDTs and Classes in the Watch Window
        5. Quick Watch (Shift+F9)
      5. The Locals Window
      6. The Object Browser (F2)
        1. Basic Features
        2. Advanced Features
      7. Creating and Running a Test Harness
      8. Using Assertions
      9. Debugging Shortcut Keys that Every Developer Should Know
        1. General
        2. Debug Mode Code Execution
        3. Navigation
        4. Information
      10. Conclusion
    21. 17. Optimizing VBA Performance
      1. Measuring Performance
      2. The PerfMon Utility
      3. Creative Thinking
        1. Do a Jigsaw
        2. Identify the Steps
        3. Think Outside the Box
        4. Break the Rules!
        5. Know the Data
        6. Ask Questions
        7. Know the Tool
      4. Macro-Optimization
        1. Preprocess
        2. Check the Order
        3. Tighten the Loop
        4. Fast VBA Algorithms
          1. QuickSort
          2. Binary Search
          3. Sort and Scan
          4. The SORTSEARCH_INDEX udt
      5. Micro-Optimization
        1. VBA
          1. Use Matching Data Types
          2. Perform Explicit Conversions Instead of Implicit Ones
          3. Use Len(string)=0 Instead of string=””
          4. Use Left$, Right$, Mid$ and So Forth Instead of Left, Right and Mid
          5. Pass Strings and Variant Arrays ByRef Instead of ByVal
          6. Don't Use Option Compare Text
          7. Use Early Binding Wherever Possible
          8. Use Integer Arithmetic Where Possible
          9. Use For Each to Iterate Collections (Not by Index)
          10. Use For … Next to Iterate Arrays (Not For Each)
          11. Use Dictionaries Instead of Collections (If Order Isn't Important)
          12. Don't Use If bVariable = True Then, Just Use If bVariable Then
          13. Don't Use IIf()
          14. Use Multiple If…ElseIf…End If Instead of Select Case
          15. Use With blocks and Object Variables to Reduce the Dots
        2. Excel
          1. Turn Off ScreenUpdating and Automatic Calculation
          2. Don't Select Things
          3. Use Variant Arrays
          4. Don't Use ActiveSheet, Selection or Worksheets() Repeatedly
          5. Test a Property Before Setting It
          6. Use Doubles to Talk to Excel
          7. Use the PAGE.SETUP XLM Function Instead of the PageSetup Object
      6. Conclusion
    22. 18. Controlling Other Office Applications
      1. Fundamentals
        1. Automation
        2. Referencing
        3. Development Best Practices
          1. Always Include the Object Library in Variable Declarations
          2. Always Fully Qualify Property and Method Calls
          3. Develop Using the Earliest Version You'll Support
          4. Group Routines in Application-Specific Modules
        4. The vTable and Early vs. Late Binding
        5. Handling Instances
          1. Create a New Instance
          2. Properly Tidying Up
          3. Reference an Existing Instance
          4. Multiversion Support
          5. Determining the Availability of an Application
        6. Performance
      2. The Primary Office Application Object Models
        1. Access and Data Access Objects
          1. Application
          2. DAO.Database
          3. DoCmd
          4. Example
        2. Word
          1. Application
          2. Document
          3. Bookmark
          4. Range
          5. Example
        3. PowerPoint and MSGraph
          1. Application
          2. Presentation
          3. Slide
          4. Shape
          5. Charts
          6. Example
        4. Outlook
          1. Application
          2. Namespace
          3. MAPIFolder
          4. AppointmentItem, ContactItem, DistributionListItem, JournalItem, MailItem, NoteItem, PostItItem and TaskItem
          5. Example
        5. Further Reading
      3. Practical Example
      4. Conclusion
    23. 19. XLLs and the C API
      1. Why Create an XLL-Based Worksheet Function
      2. Creating an XLL Project in Visual Studio
      3. The Structure of an XLL
        1. The Function Table
        2. The DLLMain Function
        3. Standard XLL Callback Functions
          1. xlAutoOpen
          2. xlAutoClose
          3. xlAddInManagerInfo
        4. Additional XLL Callback Functions
          1. xlAutoRegister
          2. xlAutoAdd
          3. xlAutoRemove
          4. xlAutoFree
      4. The XLOPER and OPER Data Types
      5. The Excel4 Function
      6. Commonly Used C API Functions
        1. xlFree
        2. xlCoerce
        3. xlGetName
      7. XLOPERs and Memory Management
      8. Registering and Unregistering Custom Worksheet Functions
      9. Sample Application Function
      10. Debugging the Worksheet Functions
      11. Miscellaneous Topics
        1. A Caution for Users of COM Automation
        2. C++ Keyword Clash with the XLOPER Definition
      12. Additional Resources
        1. The Excel 97 SDK on MSDN
        2. Excel Add-in Development in C/C++: Applications in Finance
        3. William Hooper's Web Site
        4. Laurent Longre's Web Site (French Only)
        5. The Microsoft Excel Public Newsgroups
        6. Planatech XLL+
        7. Keith Lewis' Freeware Object-Oriented C++ Wrapper for the Excel C API
        8. ManagedXLL
      13. Conclusion
    24. 20. Combining Excel and Visual Basic 6
      1. A Hello World ActiveX DLL
        1. Creating an ActiveX DLL Project
        2. The Simplest Case—One-Way Communication
        3. The More Complex Case—Two-Way Communication
        4. Displaying a VB6 Form in Excel
      2. Why Use VB6 ActiveX DLLs in Excel VBA Projects
        1. Code Protection
        2. Taking Advantage of VB6 Forms
          1. Better ActiveX Control Support
          2. Control Arrays
        3. Better Support for Object-Oriented Programming
          1. More Class Instancing Types
            1. MultiUse
            2. GlobalMultiUse
          2. Better Support for Custom Collections through Direct Support of NewEnum
        4. Resource Files
        5. Other VB6 Features
      3. In-Process versus Out-of-Process
        1. In-Process Communication
        2. Out-of-Process Communication
      4. Automating Excel From a VB6 EXE
        1. An Excel Automation Primer
        2. Using a VB6 EXE Front Loader for Your Excel Application
      5. Practical Examples
        1. ActiveX DLL—Using a Resource File to Load Icons
          1. Adding a Resource File to Your Project
          2. Adding Bitmaps to the Resource File
          3. Using Bitmaps Located in the Resource File
        2. Standard EXE—Creating a Front Loader for Your Excel Application
      6. Conclusion
    25. 21. Writing Add-ins with Visual Basic 6
      1. A Hello World Add-in
      2. The Add-in Designer
        1. General Tab
          1. Add-in Display Name
          2. Add-in Description
          3. Application
          4. Application Version
          5. Initial Load Behavior
        2. Advanced Tab
          1. Satellite DLL Name
          2. Registry Key for Additional Add-in Data and Add-in-Specific Data
      3. Installation Considerations
      4. The AddinInstance Events
        1. Initialize
        2. OnConnection
        3. OnStartupComplete
        4. OnAddInsUpdate
        5. OnBeginShutdown
        6. OnDisconnection
        7. Terminate
      5. Command Bar Handling
        1. Using Command Bar Event Hooks
        2. Permanent vs. Temporary Menu Items
          1. A Permanent-Menu Architecture
          2. A Temporary-Menu Architecture
        3. Custom Toolbar Faces
        4. The Paste Special Bar COM Add-in
      6. Why Use a COM Add-in?
        1. Improved Code Security
        2. Multi-Application Add-ins
        3. Exploiting Separate Threading
      7. Automation Add-ins
        1. Creating the IfError Automation Add-in
        2. Using the IfError Automation Add-in
        3. Accessing the Excel Application Object from an Automation Add-in
      8. Practical Example
      9. Conclusion
    26. 22. Using VB.NET and the Visual Studio Tools for Office
      1. Overview
        1. Target Audience
        2. What Is VSTO?
        3. How Does .NET Interact with Office?
        4. Is This the End for VBA?
        5. Terminology
      2. How to Leverage the .NET Framework
      3. Managed Workbooks
        1. Concept
        2. A Hello World Managed Workbook
        3. The Default VSTO Template
        4. The ProExcel VSTO Template
          1. MGlobals
          2. VSTOHooks
          3. CExcelApp
          4. CThisWorkbook
          5. CSheet1
          6. MStandardCode
        5. Sharing Command Bars
      4. Managed Excel Add-ins
        1. The Paste Special Bar VSTO Add-in
      5. Hybrid VBA/VSTO Solutions
      6. The VSTO Security Model
        1. Strong Names
        2. Strong Name Risks
        3. Creating and Using Strong Names
        4. Trusting a Strong Name
        5. Caspol
      7. The Big Issues
        1. Functional Gaps
        2. Application Links
        3. Global Solutions
        4. Security and Sharing Managed Workbooks
        5. Migrating from VBA
        6. Office Versions
      8. Further Reading
      9. Practical Example
        1. PETRAS Timesheet Add-in
        2. PETRAS Reporting Application
      10. Conclusion
    27. 23. Excel, XML and Web Services
      1. XML
        1. An Example XML File
        2. An Example XSD file
        3. Overview of Excel 2003's XML Features
        4. A Simple Financial Model
        5. Creating an XML Schema Definition
        6. XML Maps
        7. Exporting and Importing XML Data
        8. The XML Object Model and Events
        9. XML Support in Earlier Versions
        10. Using Namespaces
      2. Web Services
        1. Creating a Web Service with VB.NET
        2. Using a Web Service
      3. Practical Example
        1. PETRAS Web Service
        2. PETRAS Timesheet
        3. PETRAS Reporting
      4. Conclusion
    28. 24. Providing Help, Securing, Packaging and Distributing
      1. Providing Help
        1. Overview
        2. Getting Started
          1. Create a Help Project File
          2. Update the Project Options
          3. Create an Introductory HTML File
          4. Create a “No Help Available” Topic File
          5. Create a List of Topics
          6. Give Each Topic a Numeric ID
          7. Compile the Project
        3. Writing Content
          1. Table of Contents
          2. Index
        4. Displaying Help from VBA
      2. Securing
        1. Excel Security
        2. Checking Network Groups
        3. Macro Security and Digital Signatures
          1. Alternatives to Digital Signatures
      3. Packaging
        1. Installation Location
        2. Installation Requirements
          1. Templates
          2. Add-ins
          3. COM Add-ins
          4. Automation Add-ins
        3. Installation Mechanisms
          1. Manual
          2. An Installation Workbook
          3. Windows Installer
      4. Distributing
        1. Originals
        2. Updates
        3. Phone Home
      5. Conclusion
    29. Where are the Companion Content Files?

    Product information

    • Title: Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®
    • Author(s): Stephen Bullen, Rob Bovey, John Green
    • Release date: February 2005
    • Publisher(s): Addison-Wesley Professional
    • ISBN: 0321262506