From the Back Cover

Enterprise-Class Microsoft® Excel Programming, from Start to Finish

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

This is not a book for beginners. Writing for professional developers and true Microsoft Excel experts, the authors share insider's knowledge they've acquired building Microsoft Excel applications for many of the world's largest companies—including Microsoft. Professional Excel Development demonstrates how to get the utmost from Microsoft 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 Microsoft Excel development, from building add-ins through interacting with XML Web services. Coverage includes

  • Building add-ins to provide new Microsoft Excel functions
  • Designing effective worksheets, userforms, and other user interface elements
  • Leveraging Microsoft 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 Microsoft 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 distribution
CD-ROM INCLUDED

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' Microsoft Excel development utilities.

Table of Contents

  Title Synopsis
1 Introduction Chapter one categorises the different types of Microsoft Excel developer and provides a very high-level look at Microsoft Excel as an application development platform. By relating your skill level to the categories we identify, you will be able to see which chapters in the book are most applicable to you.
2 Application Architectures Chapter two identifies the main types of Microsoft Excel application, from a simple code-less workbook to a dictator application, provides a high-level overview of the purpose and typical structure of each type and identifies the situations they are most applicable to.
3 Excel and VBA Development Best Practices Perhaps the most important skill for the professional Microsoft Excel developer is the ability to organise our applications and write our code in a way that will prove easy to understand and maintain long into the future. The best way to obtain that skill is to identify, document and stick rigidly to an easily-understood yet simple set of best practices. This chapter explains the practices we use in our projects and indeed in all the code samples throughout the book.
4 Worksheet Design The simplest application is one that doesn't have any VBA code behind it. This chapter explains how to design a professional worksheet-based user interface, using all the standard tools built in to Microsoft Excel, including defined names, styles, data validation, conditional formatting etc.
5 Function, General and Application-Specific Add-ins Chapter 5 takes a close look at the different types of Add-in we can create using VBA (later chapters look at creating add-ins using C++, VB6 and VB.NET). The section about function libraries explains how to integrate user defined functions into Microsoft Excel's Function Wizard, including providing descriptions and help text for the function and all its parameters.
6 Dictator Applications If you want to create an application which completely replaces Excel's standard menus, toolbars and other UI elements with your own, this chapter explains everything that needs to be done and provides a simple template application to get you started.
7 Using Class Modules to Create Objects When most of us start writing VBA code, we usually write procedural code that manipulates the objects provided by Microsoft Excel. This chapter explains how we can create our own objects using class modules, and shows how doing so can both simplify our code and improve its reliability and robustness.
8 Advanced Command Bar Handling Most non-trivial Microsoft Excel applications create their own menus or toolbars, often using vast amounts of code to create them. This chapter provides a table-driven command bar builder and explains how to use it to create your own complex menu structures. It goes on to explain how to hook command bar button events instead of setting the OnAction property and what can be gained by doing so - including making Microsoft Excel manage the enabling/disabling of our menu items.
9 Understanding and Using Windows API Calls Most Microsoft Excel developers use Windows API calls by going to Google, searching the web or the newsgroups for the answer to a problem and finding the solution requires them. We copy the solution into our application and hope it works, usually without really understanding what it does. This chapter shines a light on the most common of those solutions, explaining how they work, what they use the API calls for and how they can be modified to better fit our applications. Along the way, we fill in some of the conceptual framework of common Windows API techniques and terminology.
10 Userform Design and Best Practices Userforms are usually the most important part of our application for the user, and they will like or dislike our applications based primarily on how well the userforms are designed and function. From fundamental design principles to creating resizable forms and Wizards, this chapter explains how to get the utmost from userforms and provides some best practices for organizing the code that supports them.
11 Interfaces This chapter goes beyond basic class modules by explaining how one class can appear to be many different types of object, and how many different classes can appear to be the same type of object. We show that by using these techniques, we can improve the robustness of our application, simplify the development experience and reduce the amount of code we need to write.
12 VBA Error Handling Error handling is one of the most commonly omitted features in Microsoft Excel applications. A professional error handling system, such as the one explained in this chapter, will not prevent errors from occurring, but it will make the process much less distressing for your users and much easier for you to diagnose and correct.
13 Programming with Databases A large percentage of non-trivial Microsoft Excel applications require some sort of data store that is separate from the rest of the application, usually in the form of a database. In this chapter we cover the basics of database design and data access using SQL and ADO.
14 Data Manipulation Techniques In this chapter, we turn away slightly from VBA to examine how we can make the most of Microsoft Excel’s advanced data-manipulation features, including Query Tables, Pivot Tables, Data Consolidation and Advanced Filtering. We also explain how to use some of the more advanced worksheet functions, including the 'D' database functions, array formulas and intentional circular references.
15 Advanced Charting Techniques Chapter 15 explains how to get the utmost from Microsoft Excel's charting engine, including how to combine different types of chart, how and why to use defined names to link charts to their data and how to 'cheat' by using different chart elements to create the required visual effect.
16 VBA Debugging No one writes perfect code on the first try. Being able to efficiently locate and correct the mistakes we’ve made is a significant part of what separates a professional programmer from a skilled amateur. In this chapter we demonstrate how to use the built-in debugging features of the Visual Basic Editor to locate and correct bugs as well as provide tips and techniques that will help you become a better debugger.
17 Optimizing VBA Performance VBA makes it very easy to write code that works, but quite difficult to write code that works fast. As a general rule, the speed of a well-optimized routine can often be an order of magnitude faster than the original code, and improvements of two orders of magnitude are not uncommon. This chapter explains how to achieve those savings.
18 Controlling Other Office Applications In chapter 18, we start looking outside of Microsoft Excel, by explaining how to use Microsoft Excel to control the other Office applications and suggesting some best practices to use when controlling other applications. We go on to provide an introductory overview of the major objects in each application’s object model, from the perspective of an Microsoft Excel developer.
19 XLLs and the C API If we want to create very fast and efficient user-defined functions, we can't do it in VBA - they're just too slow. This chapter explains the fundamentals of creating worksheet functions using C++ and packaging them as an XLL addin.
20 Combining Excel and Visual Basic 6 VB6 has a number of capabilities that VBA does not, including the ability to generate truly compiled code in the form of DLLs or stand-alone executable files, a more powerful forms package, superior object oriented programming capabilities, support for resource libraries, and Clipboard, Printer and Screen objects among others. In this chapter we show how to combine VB6 and Microsoft Excel to easily and tightly integrate these features with our VBA projects.
21 Writing Add-ins with Visual Basic 6 Ever since VBA was added to Excel 5.0, we’ve been able to add features to Microsoft Excel by creating Add-in workbooks. In Office 2000, Microsoft created a new add-in architecture, called a COM Add-in, which is common to all the Office applications and the VBE. This chapter explains how to create COM add-ins using Visual Basic 6 and why we might want to.
22 Using VB.NET and VSTO Relatively late in the planning process for Office 2003, the irresistible force of .NET crashed into the immovable object of Office. The Visual Studio Tools for Office was crafted from the debris. This chapter explains the concepts behind VSTO, how it works, what it can do (and can’t do) and discusses other ways of leveraging the .NET framework within Microsoft Excel solutions.
23 Excel, XML and Web Services This chapter explains what XML is and why it's different from all the other text formats that have gone before it (from an Microsoft Excel developers point of view). It explains why we might want to use XML and web services within our Microsoft Excel applications, how to use them effectively in Microsoft Excel 2003 and how they can be used in versions prior to Microsoft Excel 2003.
24 Providing Help, Securing, Packaging and Distributing The final step in developing a Microsoft Excel-based application is preparing it for release to our users. This includes creating a Help file and adding code to display it from our application, securing the application to prevent accidental and/or malicious changes, limiting access to features by checking the user’s network group memberships and avoiding the display of the Macro Security warning dialogs. Finally, we explain some mechanisms for installing Microsoft Excel applications on our users’ computers.

Practical Examples

The book is more than just a theoretical tour through the various concepts and techniques employed by professional-level Microsoft Excel developers. The accompanying CD contains numerous working examples demonstrating the concepts introduced in each chapter. Almost every code snippet shown in the book is included in working and heavily-commented form in the example workbooks, ready for you to copy into your applications. Some of the more complex topics in the book are explained by re-implementing simple examples. For example, chapter 5 includes an IFERROR() VBA user-defined-function. It is re-implemented as a C++ XLL in chapter 19 and a VB6 automation add-in in chapter 21. Similarly, chapter 8 includes a toolbar for each of the Paste Special options, written as a general-purpose Microsoft Excel addin. It is re-implemented as a VB6 COM Addin in chapter 21 and a VB.NET VSTO addin in chapter 22. By keeping the functionality the same, we can focus on the minor changes needed to implement it using each mechanism.

In addition, most chapters end with a Practical Example section, in which we apply the concepts introduced in that chapter to a real-world Microsoft Excel application. The Professional Excel Timesheet Reporting and Analysis System (aka PETRAS) starts in chapter 4 as a simple worksheet for each employee to enter their hours worked, assigning them to clients, projects and types of activity. At this stage, we assume each timesheet is reviewed individually, as would typically be the case with up to, say, twenty employees. Chapter 5 adds an application-specific add-in to manage the timesheet templates, while chapter 6 introduces a centralised reporting workbook (implemented as a dictator application) to consolidate and report on multiple timesheet workbooks. Each further chapter adds extra features to the timesheet add-in and/or the reporting application, with each change being explained in the book and clearly highlighted in the code. The following chapters mark the more significant milestones in the development of the PETRAS application:

The CD contains a different version of the PETRAS application for each chapter, with each change clearly identified and heavily commented, allowing you to easily see exactly how the application evolves as the book progresses. It is interesting to note, though, that the worksheet template used to enter the timesheet data in chapter 23 is utterly unchanged from chapter 5.

Sample Chapters

Chapter 1 - Introduction is available as an html page
Chapter 9 - Understanding and Using Windows API Calls is available as a pdf file
Chapter 15 - Advanced Charting Techniques is available as a pdf file.

Reproduced with the permission of Addison-Wesley. All material copyright Pearson Education, 2005. No part of this material may be reproduced or transmitted in any form without the prior consent of the publisher.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries