Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET, Second Edition

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET, Second Edition

by Rob Bovey, Dennis Wallentin, Stephen Bullen, John Green
Released May 2009
Publisher(s): Addison-Wesley Professional
ISBN: 9780321579126

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

“As Excel applications become more complex and the Windows development platform more powerful, Excel developers need books like this to help them evolve their solutions to the next level of sophistication. Professional Excel Development is a book for developers who want to build powerful, state-of-the-art Excel applications using the latest Microsoft technologies.”
–Gabhan Berry, Program Manager, Excel Programmability, Microsoft

“The first edition of Professional Excel Development is my most-consulted and most-recommended book on Office development. The second edition expands both the depth and range. It shines because it takes every issue one step further than you expect. The book relies on the authors’ current, real-world experience to cover not only how a feature works, but also the practical implications of using it in professional work.”
–Shauna Kelly, Director, Thendara Green

“This book illustrates techniques that will result in well-designed, robust, and maintainable Excel-based applications. The authors’ advice comes from decades of solid experience of designing and building applications. The practicality of the methods is well illustrated by the example timesheet application that is developed step-by-step through the book. Every serious Excel developer should read this and learn from it. I did.”
–Bill Manville, Application Developer, Bill Manville Associates

The Start-to-Finish Guide to Building State-of-the-Art Solutions with Excel 2007

In this book, four world-class Microsoft® Excel developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors—three of whom have been honored by Microsoft as Excel Most Valuable Professionals (MVPs)—show how to consistently make the right design decisions and make the most of Excel’s most powerful new features. Using their techniques, you can reduce development costs, time to market, and hassle—and build more effective, successful solutions.

Fully updated for Excel 2007, this book starts where other books on Excel programming leave off. Through a hands-on case study project, you’ll discover best practices for planning, architecting, and building Excel applications that are robust, secure, easy to maintain, and highly usable. If you’re a working developer, no other book on Excel programming offers you this much depth, insight, or value.

  • Design worksheets that will be more useful and reliable
  • Leverage built-in and application-specific add-ins
  • Construct applications that behave like independent Windows programs
  • Make the most of the new Ribbon user interface
  • Create cross-version applications that work with legacy versions of Excel
  • Utilize XML within Excel applications
  • Understand and use Windows API calls
  • Master VBA error handling, debugging, and performance optimization
  • Develop applications based on data stored in Access, SQL Server, and other databases
  • Build powerful visualization solutions with Excel charting engine
  • Learn how to work with VB.NET and leverage its IDE
  • Automate Microsoft Excel with VB.NET
  • Create managed COM add-ins for Microsoft Excel with VB.NET
  • Develop Excel solutions with Visual Studio Tools for Office (VSTO)
  • Integrate Excel with Web Services
  • Deploy applications more securely and efficiently

Table of contents

  1. Copyright
  2. Praise for Professional Excel Development, Second Edition
  3. Acknowledgments
  4. About the Authors
  5. 1. Introduction
    1. About This Book
    2. Who Should Read This Book
    3. Excel Developer Categories
    4. 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 Code and UserForms
      4. The Worksheet as a Declarative Programming Language
      5. The Excel Object Model
    5. Structure
    6. Examples
    7. Supported Versions of Excel
    8. Typefaces
    9. On the CD
    10. Help and Support
    11. The Professional Excel Development Web Site
    12. Feedback
  6. 2. Application Architectures
    1. Concepts
      1. Codeless Applications
      2. Self-Automated Workbooks
      3. General Purpose Add-ins
        1. Structure of a 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. Summary
  7. 3. Excel and VBA Development Best Practices
    1. Naming Conventions
      1. 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 Enumeration Types
      2. Naming Convention Examples
        1. Variables
        2. Constants
        3. User-Defined Types
        4. Enumeration Types
      3. Procedures
      4. Modules, Classes, and UserForms
      5. Worksheets and Chart Sheets
      6. The Visual Basic Project
      7. Excel UI Naming Conventions
        1. Shapes
        2. Embedded Objects
        3. Defined Names
      8. Exceptions—When Not to Apply the Naming Convention
    2. Best Practices for Application Structure and Organization
      1. Application Structure
        1. Separation of Logical Tiers
        2. 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. General VBA Programming Best Practices
        1. Use of Module Directives
        2. Best Practices for 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
          9. Early Binding Versus Late Binding
        3. 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. Summary
  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. Creating and Using Styles
      2. Modifying Styles
      3. 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. Summary
  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 Time Sheet 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. Summary
  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 Versus 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 Reporting
        1. Identifying Workbooks
        2. Using the PETRAS Reporting Application
    3. Summary
  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 6
        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 Time Sheet
        1. The Template
        2. The Application-Level Event Handler
      2. PETRAS Reporting
    6. Summary
  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 an Event Hook Can 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 Time Sheet
      2. PETRAS Reporting
      3. Application Contexts
    7. Summary
  13. 9. Introduction to XML
    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. Summary
  14. 10. The Office 2007 Ribbon User Interface
    1. The RibbonX Paradigm
    2. An Introduction to the Office 2007 Open XML File Format
      1. The Structure of the Open XML File Format
    3. Ribbon Design and Coding Best Practices
      1. Design to Support the Work Processes
      2. Using the Add-Ins Tab
      3. Sharing Custom Tabs and Groups among Multiple Add-ins
      4. The keytip Attribute
      5. Managing Control Custom Images
      6. Using Global Callback Handlers
      7. Invalidating
    4. Table-Driven Ribbon UI Customization
      1. Get Access to the customUI XML Part
    5. Advanced Problem Solving
      1. Creating Ribbon UI for Dictator Applications
      2. Hide the Ribbon UI
      3. Determine the Size of comboBox, dropDown, and editBox Controls
      4. Sheet Navigation
      5. Using Templates
    6. Further Reading
      1. RibbonX: Customizing the Office 2007 Ribbon
    7. Related Portals
      1. XML in Office Developer Portal
      2. OpenXMLDeveloper.org
      3. The Office Fluent User Interface Developer Portal
    8. Summary
  15. 11. Creating Cross-Version Applications
    1. Command Bar and Ribbon User Interfaces in a Single Application
      1. Light Weight
      2. Heavy Weight
        1. Extracting the Command Bars Logic
        2. Creating a Ribbon User Interface Loader
        3. Other Considerations
    2. Other Excel 2007 Development Issues
      1. The Inability to Add Code to Macro-Free Excel Files
      2. Working with the File System
    3. Windows Vista Security and Folder Structure
      1. User Account Control (UAC)
      2. Standard User Accounts
      3. Public Profile
      4. Targeting Windows XP and Windows Vista
    4. Summary
  16. 12. 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 Versus 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 Time Sheet
      2. PETRAS Reporting
    7. Summary
  17. 13. UserForm Design and Best Practices
    1. Principles
      1. Keep It Simple
      2. Display Canvas, Not Business Rules
      3. Use Classes Instead of 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, and WordArt on UserForms
      4. Locking Versus Disabling Controls
      5. Pop-up 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. File Name Box
        4. Drop-down Panes
      2. Windows Common Controls
        1. Drag-and-Drop
    9. Practical Example
      1. PETRAS Time Sheet
      2. PETRAS Reporting
    10. Summary
  18. 14. 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 Time Sheet
      2. PETRAS Reporting
    11. Summary
  19. 15. VBA Error Handling
    1. Error Handling Concepts
      1. Unhandled Versus 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 Time Sheet
      2. PETRAS Reporting
    9. Summary
  20. 16. VBA Debugging
    1. Basic VBA Debugging Techniques
      1. Run Mode Versus Break Mode
        1. Break on All Errors
        2. Break in Class Module
        3. Break on Unhandled Errors
      2. Debug Mode
      3. User-Defined Debug Mode
        1. The Stop Statement
        2. Conditional Compilation Constants
      4. Using Break Points (F9)
      5. Stepping Through Code
        1. Step Into (F8)
        2. Step Over (Shift+F8)
        3. Step Out (Ctrl+Shift+F8)
        4. Step to Cursor (Ctrl+F8)
      6. 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. 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. Summary
  21. 17. Optimizing VBA Performance
    1. Measuring Performance
    2. The PerfMon Utility
    3. Creative Thinking
      1. Doing a Jigsaw Puzzle
      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. Pre-Process
      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 Conversions
        3. Use Len(String)=0 Instead of String=“”
        4. Use Left$, Right$, and Mid$ 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. Iterate Arrays by Index (Not For...Each)
        11. Use Dictionaries Instead of Collections (If Order Is Not 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 Dot Operators
      2. Excel
        1. Turn Off ScreenUpdating and Automatic Calculation
        2. Don’t Select
        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. Summary
  22. 18. Introduction to Database Development
    1. An Introduction to Databases
      1. Why Use a Database
      2. Relational Databases
      3. File-Based Databases Versus 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 Versus Artificial Primary Keys
    2. An Introduction to SQL
      1. The SELECT Statement
      2. The INSERT Statement
      3. The UPDATE Statement
      4. The DELETE Statement
    3. Data Access with ADO
      1. An Introduction to ActiveX Data Objects (ADO)
        1. Data Access Technology Defined
        2. The ADO Object Model
        3. The Connection Object
          1. The ConnectionString Property
          2. The ConnectionTimeout Property
          3. The State Property
          4. The Close Method
          5. The Open Method
          6. The Execute Method
          7. Connection Object Events
          8. The Errors Collection
        4. The Command Object
          1. The ActiveConnection Property
          2. The CommandText Property
          3. The CommandType Property
          4. The CreateParameter Method
          5. The Execute Method
          6. The Parameters Collection
        5. The Recordset Object
          1. The BOF and EOF Properties
          2. The CursorLocation Property
          3. The Filter Property
          4. The Sort Property
          5. The Close Method
          6. The Move Methods
          7. The NextRecordset Method
          8. The Open Method
          9. The Fields Collection
          10. Recordset Object Events
    4. Further Reading
      1. Pro SQL Server 2005 Database Design and Optimization
      2. ADO 2.6 Programmer’s Reference
      3. Professional ADO 2.5 Programming
      4. Sams Teach Yourself SQL in 10 Minutes
    5. Summary
  23. 19. Programming with Access and SQL Server
    1. A Note on the Northwind Sample Database
    2. Designing the Data Access Tier
      1. Why Have a Separate Data Access Tier
      2. Physical Design of a Data Access Tier
    3. Working with Microsoft Access Databases
      1. Connecting to an Access Database
      2. Using the Connection String
      3. Data Access Techniques with Microsoft Access
        1. Retrieving Data
        2. Inserting Data
        3. Updating Data
        4. Deleting Data
    4. Working with Microsoft SQL Server Databases
      1. Connecting to a SQL Server Database
      2. Connection Pooling
      3. Error Handling Connections
      4. Data Access with SQL Server
      5. Parameter Refreshing
      6. Multiple Recordsets
      7. Disconnected Recordsets
    5. Upsizing from Access to SQL Server
    6. Further Reading
      1. Access 2002 Developer’s Handbook Set
      2. Professional SQL Server 2005 Programming
    7. Practical Example
      1. PETRAS Time Sheet
        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
    8. Summary
  24. 20. Data Manipulation Techniques
    1. Excel’s Data Structures
      1. Unstructured Ranges
      2. Structured Ranges
      3. Excel 2003 Lists
      4. QueryTables
    2. Data Processing Features
      1. It Doesn’t Have to Be Data
      2. PivotCaches
      3. PivotTables
        1. Calculated PivotFields
      4. Data Consolidation
      5. Advanced Filtering
        1. Criteria Ranges
    3. Advanced Functions
      1. The Database Functions
      2. Array Formulas
      3. Circular References
    4. Summary
  25. 21. 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. Summary
  26. 22. 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 Procedures in Application-Specific Modules
      4. The vTable and Early Versus Late Binding
      5. Handling Instances
        1. Create a New Instance
        2. Cleaning Up Properly
        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
    3. Further Reading
    4. Practical Example
    5. Summary
  27. 23. 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
      3. Better ActiveX Control Support
        1. Control Arrays
      4. 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
      5. Resource Files
      6. 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. COM Add-ins
    6. A “Hello World” COM Add-in
    7. 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
    8. Installation Considerations
    9. The AddinInstance Events
      1. Initialize
      2. OnConnection
      3. OnStartupComplete
      4. OnAddInsUpdate
      5. OnBeginShutdown
      6. OnDisconnection
      7. Terminate
    10. Command Bar Handling
      1. Using Command Bar Event Hooks
      2. CommandBar Architecture
      3. Custom Toolbar Faces
      4. The Paste Special Bar COM Add-in
    11. Why Use a COM Add-in?
      1. Improved Code Security
      2. Multi-Application Add-ins
      3. Exploiting Separate Threading
    12. 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
    13. 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
    14. Summary
  28. 24. Excel and VB.NET
    1. .NET Framework Fundamentals
    2. Visual Basic.NET
      1. The Visual Studio IDE
        1. General Configuration of the VS IDE
      2. Creating a VB.NET Solution
      3. Structured Exception Handling
      4. Modules and Methods, Scope and Visibility
      5. Declare Variables and Assign Values
      6. Creating New Instances of Objects
      7. Using ByVal or ByRef
      8. Using Wizards in VB.NET
      9. Data Types in VB.NET
        1. String Manipulation
      10. Using Arrays in VB.NET
    3. Debugging
      1. Set Keyboard Shortcuts
      2. Enable Unmanaged Code Debugging
      3. The Exception Assistant
      4. The Object Browser (F2)
      5. The Error List Window (Ctrl+W Ctrl+E)
      6. The Command Window (Ctrl+Alt+A) and Immediate Window (Ctrl+G)
      7. The Output Window (Ctrl+Alt+O)
      8. Break Points (Ctrl+Alt+B)
      9. The Call Stack (Ctrl+L)
      10. The Quick Watch and Watch Windows
      11. Exceptions (Ctrl+Alt+E)
      12. Conditional Compilation Constants
      13. Using Assertions
    4. Useful Development Tools
      1. Code Region
      2. The Code Snippets Manager (Ctrl+K Ctrl+B)
      3. Insert File as Text
      4. Task List (Ctrl+Alt+K)
    5. Automating Excel
      1. Primary Interop Assembly (PIA)
      2. Using Excel Objects in .NET Solutions
      3. Using Late Binding
    6. Resources in .NET Solutions
    7. Retrieving Data with ADO.NET
    8. Further Reading
      1. Programming Microsoft Visual Basic .NET Version 2003
      2. Visual Basic 2008 Programmer’s Reference
    9. Additional Development Tools
      1. MZ-Tools
      2. VSNETCodePrint
    10. Q&A Forums
    11. Practical Example—PETRAS Report Tool .NET
      1. The .NET Solution
      2. Windows Forms Extender Providers
      3. Threading
      4. Retrieving the Data
      5. Exporting Data
    12. Summary
  29. 25. Writing Managed COM Add-ins with VB.NET
    1. Choosing a Development Toolset
    2. Creating a Managed COM Add-in
      1. Anatomy of a Managed COM Add-in
        1. The Connection Class
        2. Auto-Generated References
        3. Entries in the Windows Registry
        4. The Setup Project
          1. Setup Prerequisites Packages
          2. Create a Launch Condition
          3. Create an Entry in the Windows Registry
          4. Build the Setup
    3. Building the User Interface
      1. Command Bar User Interface Handling
      2. Ribbon User Interface Handling
        1. Using Custom Icon Images
    4. Creating Managed Automation Add-ins
      1. Creating an Automation Add-in That Interacts with the Excel Application Object
      2. Why We Should Not Create Automation Add-ins with .NET
        1. CVErr Values Not Implemented in .NET
        2. Insert Function Wizard
        3. ProgId Limitations
        4. Selecting a Managed Automation Add-in in the Add-in Manager Generates an Error Message
    5. Manually Register and Unregister COM Add-ins
    6. Using Classes in VB.NET
      1. Creating Well-Designed Classes
      2. Properties
        1. Debugging Properties
    7. Using Classic ADO to Export Data to Excel
    8. Shimming COM Add-ins
      1. MSCoree.DLL Hell
      2. Isolation
      3. Security
        1. Authenticode Certificates
        2. Strong Names
      4. The COM Shim Wizard
        1. Creating a COM Shim DLL
        2. Digitally Signing Files
    9. Related Blogs
      1. XL-Dennis—VSTO & .NET & Excel
      2. Andrew Whitechapel
    10. Additional Development Tools
      1. Add-in Express for Microsoft Office and .NET
      2. AddinSpy
    11. Practical Example—PETRAS Report Tool.NET
      1. Introduction
      2. Load and Unload the COM Add-in
      3. The Custom Menu
      4. Display Windows Forms in Excel
    12. Summary
  30. 26. Developing Excel Solutions with Visual Studio Tools for Office System (VSTO)
    1. What Is VSTO?
      1. VSTO Project Templates
        1. Application-Centric Project Templates
        2. Document-Centric Project Templates
        3. Choosing Project Templates
        4. The Layers of a VSTO Solution
        5. Installing and Running VSTO Solutions
      2. Running Multiple Office Versions with VSTO
    2. When Should You Use VSTO?
    3. Working with VSTO Add-Ins
      1. Creating Our First VSTO Add-In
        1. Loading and Unloading XLAs
        2. Loading and Unloading VSTO and COM Add-Ins
        3. References in VSTO Add-ins
        4. Entries in the Windows Registry
        5. Running VSTO Add-Ins
      2. The Ribbon Visual Designer
      3. Custom Task Panes
      4. VSTO Automation Add-ins
    4. Working with VSTO Templates and Workbook Solutions
      1. Host Items and Host Controls
      2. Windows Forms Controls
      3. Server-Side VSTO Solutions
      4. Creating Our First VSTO Workbook Solution
      5. Working with a NamedRange Host Control
      6. Using the ListObject Host Control
    5. Deployment and Security
      1. An Introduction to Using ClickOnce Deployment with VSTO Solutions
        1. The VSTO Security Model
          1. VBA in VSTO Workbook Solutions
        2. Working with the Internet Security Zone
        3. Creating ClickOnce Deployment Solutions
        4. Installing VSTO Solutions
        5. Location of Installed VSTO Solutions
        6. Limitations of the ClickOnce Deployment Technology
    6. Further Reading
      1. Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
    7. Related Portal and Blogs
      1. Microsoft’s VSTO Portal
      2. Office Development with Visual Studio
    8. Additional Development Tools
      1. Microsoft Visual Studio Tools for the Office System Power Tools
    9. Summary
  31. 27. 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
      1. Numeric Data
      2. String Data
      3. Error Values
      4. Arrays
    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 2007 SDK on MSDN
      2. Financial Applications Using Excel Add-in Development in C/C++
      3. Laurent Longre’s Web Site (French-Only)
      4. The Microsoft Excel Public Newsgroups
      5. Planatech XLL+
      6. Keith Lewis’s Freeware Object Oriented C++ Wrapper for the Excel C API
      7. Managed XLL
    13. Summary
  32. 28. Excel and Web Services
    1. Web Services
      1. Creating a Web Service with VB.NET
      2. Using a Web Service from Excel
    2. Practical Example
      1. PETRAS Web Service
      2. PETRAS Time Sheet
    3. Summary
  33. 29. 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. Summary
  34. Where are the Companion Content Files?

Product information

  • Title: Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET, Second Edition
  • Author(s): Rob Bovey, Dennis Wallentin, Stephen Bullen, John Green
  • Release date: May 2009
  • Publisher(s): Addison-Wesley Professional
  • ISBN: 9780321579126