BN.com Gift Guide

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

Paperback (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $11.27
Usually ships in 1-2 business days
(Save 83%)
Other sellers (Paperback)
  • All (13) from $11.27   
  • New (6) from $35.95   
  • Used (7) from $11.27   

Overview

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.

Read More Show Less

Editorial Reviews

Slashdot.org
Gives you the tools to build applications that are much more than automated spreadsheets. Almost any program your imagination can devise can be created using the techniques given, which is a testimony to the power of Excel. Bash Microsoft if you want, but they do sometimes come up with a winner, and Professional Excel Development allows you to take full advantage of its capabilities.
Read More Show Less

Product Details

  • ISBN-13: 9780321262509
  • Publisher: Addison-Wesley
  • Publication date: 2/1/2005
  • Series: Addison-Wesley Microsoft Technology Series
  • Edition description: BK&CD-ROM
  • Pages: 936
  • Product dimensions: 7.00 (w) x 9.20 (h) x 2.00 (d)

Meet the Author

Stephen Bullen is founder of Office Automation Ltd., a specialist in Excel, Access and Visual Basic development that serves many of the world's largest businesses. Rob Bovey is president of Application Professionals, a software developer specializing in Microsoft Office, Visual Basic and SQL Server applications. He has developed several add-ins shipped by Microsoft with Excel. John Green is founder of Execuplan Consulting, a specialist in Excel and Access business application development. The authors have each held Microsoft's coveted Excel MVP status for eight consecutive years, and are coauthors of Excel 2000/Excel 2002 VBA Programmer's Reference (Wrox Press).

Read More Show Less

Table of Contents

Contents

Acknowledgments.

About the Authors.

1. Introduction.

About This Book

The Excel Developer

Excel as an Application Development Platform

Structure

Examples

Supported Versions

Typefaces

On the CD

Help and Support

Feedback

2. Application Architectures.

Concepts

Conclusion

3. Excel and VBA Development Best Practices.

Naming Conventions

Best Practices for Application Structure and Organization

General Application Development Best Practices

Conclusion

4. Worksheet Design.

Principles of Good Worksheet UI Design

Program Rows and Columns: The Fundamental UI Design Technique

Defined Names

Styles

User Interface Drawing Techniques

Data Validation

Conditional Formatting

Using Controls on Worksheets

Practical Example

Conclusion

5. Function, General and Application-Specific Add-ins.

The Four Stages of an Application

Function Library Add-ins

General Add-ins

Application-Specific Add-ins

Practical Example

Conclusion

6. Dictator Applications.

Structure of a Dictator Application

Practical Example

Conclusion

7. Using Class Modules to Create Objects.

Creating Objects

Creating a Collection

Trapping Events

Raising Events

Practical Example

Conclusion

8. Advanced Command Bar Handling.

Command Bar Design

Table-Driven Command Bars

Putting It All Together

Loading Custom Icons from Files

Hooking Command Bar Control Events

Practical Example

Conclusion

9. Understanding and Using Windows API Calls.

Overview

Working with the Screen

Working with Windows

Working with the Keyboard

Working with the File System and Network

Practical Examples

Conclusion

10. Userform Design and Best Practices.

Principles

Control Fundamentals

Visual Effects

Userform Positioning and Sizing

Wizards

Dynamic Userforms

Modeless Userforms

Control Specifics

Practical Examples

Conclusion

11. Interfaces.

What Is an Interface?

Code Reuse

Defining a Custom Interface

Implementing a Custom Interface

Using a Custom Interface

Polymorphic Classes

Improving Robustness

Simplifying Development

A Plug-in Architecture

Practical Example

Conclusion

12. VBA Error Handling.

Error-Handling Concepts

The Single Exit Point Principle

Simple Error Handling

Complex Project Error Handler Organization

The Central Error Handler

Error Handling in Classes and Userforms

Putting It All Together

Practical Example

Conclusion

13. Programming with Databases.

An Introduction to Databases

Designing the Data Access Tier

Data Access with SQL and ADO

Further Reading

Practical Example

Conclusion

14. Data Manipulation Techniques.

Excel’s Data Structures

Data Processing Features

Advanced Functions

Conclusion

15. Advanced Charting Techniques.

Fundamental Techniques

VBA Techniques

Conclusion

16. VBA Debugging.

Basic VBA Debugging Techniques

The Immediate Window (Ctrl+G)

The Call Stack (Ctrl+L)

The Watch Window

The Locals Window

The Object Browser (F2)

Creating and Running a Test Harness

Using Assertions

Debugging Shortcut Keys that Every Developer Should Know

Conclusion

17. Optimizing VBA Performance.

Measuring Performance

The PerfMon Utility

Creative Thinking

Macro-Optimization

Micro-Optimization

Conclusion

18. Controlling Other Office Applications.

Fundamentals

The Primary Office Application Object Models

Practical Example

Conclusion

19. XLLs and the C API.

Why Create an XLL-Based Worksheet Function

Creating an XLL Project in Visual Studio

The Structure of an XLL

The XLOPER and OPER Data Types

The Excel4 Function

Commonly Used C API Functions

XLOPERs and Memory Management

Registering and Unregistering Custom Worksheet Functions

Sample Application Function

Debugging the Worksheet Functions

Miscellaneous Topics

Additional Resources

Conclusion

20. Combining Excel and Visual Basic 6.

A Hello World ActiveX DLL

Why Use VB6 ActiveX DLLs in Excel VBA Projects

In-Process versus Out-of-Process

Automating Excel From a VB6 EXE

Practical Examples

Conclusion

21. Writing Add-ins with Visual Basic 6.

A Hello World Add-in

The Add-in Designer

Installation Considerations

The AddinInstance Events

Command Bar Handling

Why Use a COM Add-in?

Automation Add-ins

Practical Example

Conclusion

22. Using VB.NET and the Visual Studio Tools for Office.

Overview

How to Leverage the .NET Framework

Managed Workbooks

Managed Excel Add-ins

Hybrid VBA/VSTO Solutions

The VSTO Security Model

The Big Issues

Further Reading

Practical Example

Conclusion

23. Excel, XML and Web Services.

XML

Web Services

Practical Example

Conclusion

24. Providing Help, Securing, Packaging and Distributing.

Providing Help

Securing

Packaging

Distributing

Conclusion

Index.

Read More Show Less

Preface

Chapter 1: Introduction

About This Book

Microsoft Excel is much, much more than just a spreadsheet. Since the introduction of the Visual Basic Editor in Excel 97 and the improved stability of Excel 2000, it has become a respected development platform in its own right. Applications written using Excel are now often found alongside those written using Visual Basic, C++, Java, .NET, etc, as part of many corporations' core suite of business-critical applications. Indeed, Excel is often used for the client end of web-based applications, made particularly easy with Excel 2003's XML import/export features.

Unfortunately, Excel is still all too often thought of as a hobbyist platform; that people develop spreadsheet-based applications in their spare time to help out with their day job. A brief look at the shelves of any bookstore seems to confirm that opinion. While there are myriad titles explaining how to use Excel and numerous titles about Excel and VBA, there are none that provide an overall explanation of how to develop professional-quality Excel-based applications. This is that book.

While all the other major languages seem to have a de-facto standard text that explains the commonly-agreed best practices for architecting, designing and developing applications in that language, Excel does not. This book aims to fill that gap.

All three authors are professional Excel developers who run our own companies developing Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book details the approaches we use when designing, developing, distributing and supporting the applications we write for our clients.

This is not a beginner-level book. We assume that the reader will have read and (mostly) understood our Excel 2000/2002 VBA Programmer's Reference, John Walkenbach's Excel N Power Programming or similar titles.

The Excel Developer

Excel developers can be divided into five general categories, based on their experience and knowledge of Excel and VBA. To varying degrees, this book has something to offer each of them, but with a focus on the more advanced topics. Putting yourself into one of these categories might help you decide whether this is the right book for you.

The basic Excel User probably doesn't think of themselves as a developer at all. To them, Excel is no more than a tool to help them get on with their job. They start off using Excel worksheets as a handy place to store lists or perform simple repetitive calculations. As they discover more of Excel's functionality, their workbooks become more complex and start to include lots of worksheet functions, pivot tables and charts. There is little in this book for these people, though Chapter 4 – Worksheet Design details the best practices to use when designing and laying out a worksheet for data entry, Chapter 14 – Data Manipulation Techniques explains how to structure a worksheet and which functions and features to use to manipulate their lists and Chapter 15 - Advanced Charting Techniques explains how to get the most from Excel's chart engine. Using the techniques suggested in these chapters should help the basic Excel user avoid some of the pitfalls often encountered as their experience and the complexity of their spreadsheets increases.

The Excel Power User has a wide understanding of Excel's functionality, knows which tool or function is best to use in a given situation, creates complex spreadsheets for their own use and is often called on to help develop their colleagues' spreadsheets or to identify why their colleagues' spreadsheets don't work as intended. Occasionally the Power Users includes small snippets of VBA they found on the internet or created using the macro recorder, but struggles to adapt the code to their needs. As a result, they produce code that is untidy, slow and hard to maintain. While this book is not a VBA tutorial, the Power User has much to gain from following the best practices we suggest for both worksheets and code modules. Most of the chapters in the book will be relevant to the Power User who has an interest in improving their Excel and VBA development skills.

The VBA Developer makes extensive use of VBA code in their workbooks – often too much. They are typically either Power Users who have started to learn VBA too early or Visual Basic 6 developers that have switched to Excel VBA development. While they may be very proficient at VBA, they believe every problem must have a VBA solution and lack sufficient knowledge of Excel to make the best use of its features. Their solutions are often cumbersome, slow and make poor use of Excel's object model. This book has much to offer the VBA Developer to improve their use of Excel itself, including explaining how to architect Excel-based applications, the best practices for designing worksheets and how to use Excel's features for their data entry, analysis and presentation. The book also seeks to improve their Excel VBA development skills by introducing advanced coding techniques, detailing VBA best practices and explaining how to improve their code's performance.

The Excel Developer has realized the most efficient and maintainable applications are those which make the most of Excel's own functionality, augmented by VBA when appropriate. They are confident developing Excel-based applications for their colleagues to use or as part of an in-house development team. While their undoubted knowledge of Excel is put to good use in their applications, it also constrains their designs and they are reluctant to use other languages and applications to augment their Excel solutions. They have probably read John Walkenbach's Excel 97/2000/2002/2003 Power Programming and/or our own Excel 2000/2002 VBA Programmer's Reference and need a book to take them to the highest level of Excel application development – that of the professional developer. This is that book.

The Professional Excel Developer designs and develops Excel-based applications and utilities for their clients or employer that are robust, fast, easy to use, maintainable and secure. While Excel forms the core of their solutions, they include any other applications and languages that are appropriate, such as third-party ActiveX controls, automating other applications, using Windows API calls, using ADO to connect to external databases, C/C++ for fast custom worksheet functions, VB6 or VB.Net for creating their own object models and securing their code and XML for sharing data over the internet. This book teaches all those skills. If you are already a Professional Excel Developer, you will know learning never stops and will appreciate the knowledge and best practices presented in this book by three of your peers.

Excel as an Application Development Platform

If we look at Excel as a development platform and not just a spreadsheet, we can break it down into five fundamental components we can use for our applications:

The worksheet, charts etc, used as a user interface and presentation layer for data entry and reporting

  • The worksheet, used as a simple data store for lists, tables and other information used by our application
  • VBA, Excel's programming language and forms engine
  • The worksheet, used as a declarative programming language for high-performance numerical processing

The Excel object model, allowing programmatic control of (nearly) all of Excel's functionality, from both within Excel and from outside it.

The Worksheet as a Presentation Layer for Data Entry and Reporting

When most people think about Excel, they think in terms of typing numbers into cells, having some calculations update and seeing a result displayed in a different cell or on a chart. Without necessarily thinking in such terms, they are using the worksheet as a user interface for their data entry and reporting and are generally comfortable with these tasks. The in-cell editing, validation and formatting features built in to Excel provide an extremely rich and compelling data-entry experience, while the charting, cell formatting and drawing tools provide a presentation-quality reporting mechanism. It is hard to imagine the code that would be required if we tried to reproduce the experience using the form design tools available in most other development environments, yet it's there waiting for us to use in our Excel-based applications. The biggest problem we face is how to add some structure to the free-form grid of the worksheet, in order to present a simple and easy to use interface, while leveraging the rich functionality Excel provides. Chapter 4 – Worksheet Design introduces some techniques and best practices for developing worksheet-based data entry forms, while Chapter 15 - Advanced Charting Techniques discusses using charting capabilities.

The Worksheet as a Simple Data Store

What is a worksheet when it's never intended to be shown to the end user? At its simplest, it's no more than a large grid of cells in which we can store just about anything we want to – numbers, text, lists, tables or pictures. Most applications use some amount of static data or textual or graphical resources; storing that information in a worksheet makes it both extremely easy to access using VBA and simple to maintain. Lists and tables in worksheets can directly feed Excel's data validation (as shown in Chapter 4 – Worksheet Design), greatly simplify the creation and maintenance of command bars (Chapter 8 – Advanced Command Bar Handling) and allow us to construct dynamic userforms (Chapter 10 – Userform Design and Best Practices).

VBA – Excel's Programming Language

We expect most readers of this book will have at least some familiarity with VBA. If not, we suggest you read either our Excel 2000/2002 VBA Programmer's Reference or John Walkenbach's Excel 97/2000/2002/2003 Power Programming before continuing much further. Many people see the 'A' in VBA as meaning the language is somehow less than Visual Basic itself. In fact, both VB6 and Office 2000 and above use exactly the same DLL to provide the keyword, syntax and statements we program with. The only differences are the objects provided by the run-times (the VB runtime vs the Excel objects), the forms packages (VB's 'Ruby' forms vs Office UserForms) and that VB6 includes a compiler to create EXEs and DLLs, while VBA is always interpreted at run-time. Indeed, the Office Developer Edition (pre-Excel 2003) includes the same compiler VB6 uses, allowing us to compile (simple) DLLs from within the Office Visual Basic Editor.

Most beginner and intermediate VBA developers use VBA as a purely procedural language, with nearly all their code residing in standard modules. VBA also allows us to create applications using an Object Oriented Programming (OOP) approach, in which class modules are used to create our own objects. Chapter 7 – Using Class Modules to Create Objects and Chapter 11 – Interfaces explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.

Most of this book is dedicated to explaining advanced VBA techniques and a professional approach to application design and development which can put using VBA in Excel on a par with, and sometimes in front of, using VB6 or VB.Net for application development. We also show in Chapter 20 – Combining Excel and Visual Basic 6 and Chapter 22 – Using VB.Net and VSTO that the Excel developer can use the best of both worlds, by combining Excel, VB6 and/or VB.Net in a seamless application.

The Worksheet as a Declarative Programming Language

Take the following code:

dSales = 1000dPrice = 10.99dRevenue = dSales
• dPrice

That could quite easily be a few lines of VBA. We give the variable dSales a value of 1000, the variable dPrice a value of 10.99, then calculate the revenue as sales times price. If we change the names of the variables and adjust the spacing, the same code could also be written as:

D1 =1000D2 =10.99D3 =D1*D2

which looks much more like worksheet cell addresses and formulas than lines of VBA code, showing that a worksheet is in fact a programming language of its own, if we choose to think of it in those terms. The IF() worksheet function is directly equivalent to the If...Then...Else VBA statement, while the judicious use of circular references and iteration can be equivalent to either the For...Next or Do...Loop structures.

Instead of stating a set of operations that are executed line-by-line, we 'program' in this language by stating a set of declarations (by typing formulae and values into worksheet cells), in any order we want to:

"D3 is the product of D1 and D2"

"D1 has the value 1000"

"D2 has the value 10.99"

To 'run' this program, Excel first examines all the declarations and builds a 'precedence tree' to identify which cells depend on the results of which other cells and thereby determine the most efficient order in which the cells must be calculated. The same precedence tree is also used to identify the minimum set of calculations that must be performed whenever the value in a cell is changed. The result is a calculation engine that is vastly more efficient than an equivalent VBA program, and one that should be used whenever complex numerical computations are required in your application.

Microsoft Excel (and other spreadsheet programs) are unique among application development platforms in providing both a procedural (VBA) and a declarative (the worksheet) programming language. The most efficient Excel application is one that makes appropriate use of both these languages.

It is assumed the reader of this book has some knowledge of Excel and worksheet functions, so Chapter 14 – Data Manipulation Techniques focuses on using advanced worksheet functions (including best-practice suggestions for handling circular references) and Excel's other data analysis features.

The Excel Object Model

While the other four components of the Excel Platform are invaluable in the development of applications, it is probably the richness of the Excel Object Model that provides the most compelling reason to base our application development on Excel. Almost everything that can be done through the user interface can also be done programmatically by using the objects in the Excel Object Model – accessing the list of number formats and applying a digital signature to a workbook are perhaps the most notable exceptions. The vast array of functionality exposed by these objects makes highly-complex applications fairly simple to develop – it becomes more an issue of when and how to efficiently plug the functionality together than to develop the functionality from scratch. This book does not attempt to explore and document all the back-waters of the object model, but instead makes continual use of the objects in our application development.

Structure

Through the course of this book, we will be both covering the concepts and details of each topic and applying those concepts to a time-sheet reporting and analysis application we'll be building. The chapters are therefore arranged approximately in the order in which we would design and develop an Excel application:

Chapter 2 discusses the different styles of application we may choose to create

Chapter 3 identifies some general best-practices for working with Excel and VBA, which will be followed throughout the book

Chapter 4 explains how to design and structure a worksheet for data entry and analysis

Chapters 5 and 6 introduce two specific types of application – the add-in and the dictator application, which will form the basis of our time-sheet reporting and analysis application

Chapters 7 to 13 discuss advanced techniques for a range of VBA topics

Chapters 14 and 15 explain how to efficiently utilise Excel's features within an application to analyse data and present results

Chapters 16 and 17 discuss techniques for debugging and optimising VBA code

Chapters 18 to 22 look outside of Excel, firstly by explaining how to automate other applications, then by explaining how to interact with Excel using C, Visual Basic and VB.Net

Chapter 23 focuses on how Excel applications can make use of the internet and XML

Chapter 24 completes the development by explaining how to provide help, secure, package and distribute the application.

Examples

Throughout the book, we will be illustrating the concepts and techniques we introduce by building a timesheet data-entry, consolidation, analysis and reporting application. This will be comprised of a data-entry template to be completed by each employee, with the data sent to a central location for consolidation, analysis and reporting. The end of each chapter will see a fully-working example of both parts of the application included on the CD, which will grow steadily more complex as the book progresses and thereby be applicable to different types of company.

In Chapter 4 – Worksheet Design, we will start with a very simple data-entry workbook and the assumption that each employee would email the completed file to a manager who would analyse the results manually – a typical situation for a company with maybe 10-20 employees.

By the end of the book, the data-entry workbook will use XML to upload the data to a web site, where it will be stored in a central database. The reporting application will extract the data from the database, perform various analyses and present the results as reports in Excel worksheets and charts.

Along the way, we will be re-writing some of the parts of the application in a number of different ways, to show how easy it can be to include other languages and delivery mechanisms in our Excel-based applications.

Each chapter may also include specific examples to illustrate key points that it would be too artificial to include in our main application.

Supported Versions

When developing an Excel application for a client, their upgrade policy will usually determine which version of Excel we must use; very few clients will agree to upgrade their desktops just so we can develop using the latest version, unless there is a compelling business requirement that can only be satisfied by using features the latest version introduces. There is so little difference between Excel 2000 and Excel 2003 that it is hard to imagine such a business requirement. An extremely unscientific poll (based on postings to the Microsoft support newsgroups) seems to indicate the following approximate usage for each version:

Excel 97 10%

Excel 2000 45%

Excel 2002 40%

Excel 2003 5%

There were a number of significant changes between Excel 97 and Excel 2000 for the application developer, including the switch from VBA5 to VBA6 and the introduction of modeless userforms, interfaces, COM Add-ins and support for ADO. We have therefore decided to use Excel 2000 as our lowest supported version and development platform, with our applications tested in the later versions. Most of the concepts detailed in this book apply equally to Excel 97, but our example timesheet application will use features Excel 97 does not support. Whenever we discuss a feature that is only supported in the later versions (such as XML import/export and VB.Net integration in Excel 2003), we will state which version(s) can be used.

Typefaces

The following text styles are used in this book:

Menu items and dialog text will be shown as Tools > Options > Calculation > Manual, where the '' indicates navigation to a sub-menu or dialog tab.

Sub SomeCode() 'Code listings are shown like this 'With new or changed lines highlighted like this End Sub

Code within a paragraph will be shown like Application.Calculation = xlManual.

References to other chapters in the book will be shown as Chapter 7 – Using Class Modules to Create Objects.

Paths on the CD will be shown as \Concepts\Ch11 - Interfaces

URLs will be shown as http:///www.oaltd.co.uk

Important points or emphasised words will be shown like this .

On the CD

Most of the code listings shown in the book are also included in example workbooks on the accompanying CD. For clarity, the code shown in the book uses shorter line lengths, a reduced indent setting, fewer in-code comments and less error handling than the corresponding code in the workbooks.

The CD has three main directories, containing the following files:

\Tools contains a number of tools and utilities developed by the authors which we have found to be invaluable during our application development. The MustHaveTools.htm file contains details about each of these tools and links to other third-party utilities.

  • \Concepts has separate subdirectories for each chapter, each one containing example files to support the text of the chapter. For best results, we suggest you have these workbooks open while reading through the corresponding chapter.
  • \Application has separate subdirectories for each chapter, each one containing a version of our timesheet example application suite. Each chapter ends with a Practical Example section, explaining the changes that have been made to the timesheet application to implement some of the concepts introduced in the chapter.

Help and Support

Questions about the book itself (such as missing CDs, typos, errata etc) should be directed to Addison Wesley, at http://www.awprofessional.com/contactus .

Any errata and corrections will be posted to the Addison Wesley web site, at http://www.awprofessional.com/title/0321262506 .

By far the best place to go for help with any of your Excel development questions – whether related to this book or not – are the Microsoft support newsgroups archives maintained by Google at http://groups.google.com . A quick search of the archives is almost certain to find a question similar to yours, already answered by one of the many professional developers who volunteer their time helping out in the newsgroups, including all three of this book's authors. On the rare occasions that the archives fail to answer your question, you're welcome to ask it directly in the newsgroups by connecting a news reader (such as Outlook Express) to msnews.microsoft.com and selecting an appropriate newsgroup, such as

microsoft.public.excel.programming for VBA-related questions

microsoft.public.excel.worksheet.functions for help with worksheet functions

microsoft.public.vsnet.vstools.office for help with Excel/VB.Net integration issues

microsoft.public.excel.misc for general Excel enquiries

There are a number of web sites that provide a great deal of information and free downloadable examples and utilities, targeted towards the Excel developer, including:

http://www.oaltd.co.uk

http://www.appspro.com

http://www.j-walk.com

http://www.cpearson.com

http://msdn.microsoft.com/office

Feedback

We have tried very hard to present the information in this book in a clear and concise manner, explaining both the concepts and details needed to get things working and providing working examples of everything we cover. We have tried to provide sufficient information to enable you to apply these techniques in your own applications, but without getting bogged down in line-by-line explanations of entire code listings. We'd like to think we've been successful in our attempt, but encourage you to let us know what you think. Constructive criticism is always welcomed, as are suggestions for topics you think we may have overlooked. Please send an email to one (or all) of the following:

Stephen Bullen: stephen@oaltd.co.uk

Rob Bovey: robbovey@appspro.com

John Green: greenj@bigpond.net.au

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Introduction

Chapter 1: Introduction

About This Book

Microsoft Excel is much, much more than just a spreadsheet. Since the introduction of the Visual Basic Editor in Excel 97 and the improved stability of Excel 2000, it has become a respected development platform in its own right. Applications written using Excel are now often found alongside those written using Visual Basic, C++, Java, .NET, etc, as part of many corporations' core suite of business-critical applications. Indeed, Excel is often used for the client end of web-based applications, made particularly easy with Excel 2003's XML import/export features.

Unfortunately, Excel is still all too often thought of as a hobbyist platform; that people develop spreadsheet-based applications in their spare time to help out with their day job. A brief look at the shelves of any bookstore seems to confirm that opinion. While there are myriad titles explaining how to use Excel and numerous titles about Excel and VBA, there are none that provide an overall explanation of how to develop professional-quality Excel-based applications. This is that book.

While all the other major languages seem to have a de-facto standard text that explains the commonly-agreed best practices for architecting, designing and developing applications in that language, Excel does not. This book aims to fill that gap.

All three authors are professional Excel developers who run our own companies developing Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book details the approaches we use when designing, developing, distributing and supporting the applications we write for our clients.

Thisis not a beginner-level book. We assume that the reader will have read and (mostly) understood our Excel 2000/2002 VBA Programmer's Reference, John Walkenbach's Excel N Power Programming or similar titles.

The Excel Developer

Excel developers can be divided into five general categories, based on their experience and knowledge of Excel and VBA. To varying degrees, this book has something to offer each of them, but with a focus on the more advanced topics. Putting yourself into one of these categories might help you decide whether this is the right book for you.

The basic Excel User probably doesn't think of themselves as a developer at all. To them, Excel is no more than a tool to help them get on with their job. They start off using Excel worksheets as a handy place to store lists or perform simple repetitive calculations. As they discover more of Excel's functionality, their workbooks become more complex and start to include lots of worksheet functions, pivot tables and charts. There is little in this book for these people, though Chapter 4 – Worksheet Design details the best practices to use when designing and laying out a worksheet for data entry, Chapter 14 – Data Manipulation Techniques explains how to structure a worksheet and which functions and features to use to manipulate their lists and Chapter 15 - Advanced Charting Techniques explains how to get the most from Excel's chart engine. Using the techniques suggested in these chapters should help the basic Excel user avoid some of the pitfalls often encountered as their experience and the complexity of their spreadsheets increases.

The Excel Power User has a wide understanding of Excel's functionality, knows which tool or function is best to use in a given situation, creates complex spreadsheets for their own use and is often called on to help develop their colleagues' spreadsheets or to identify why their colleagues' spreadsheets don't work as intended. Occasionally the Power Users includes small snippets of VBA they found on the internet or created using the macro recorder, but struggles to adapt the code to their needs. As a result, they produce code that is untidy, slow and hard to maintain. While this book is not a VBA tutorial, the Power User has much to gain from following the best practices we suggest for both worksheets and code modules. Most of the chapters in the book will be relevant to the Power User who has an interest in improving their Excel and VBA development skills.

The VBA Developer makes extensive use of VBA code in their workbooks – often too much. They are typically either Power Users who have started to learn VBA too early or Visual Basic 6 developers that have switched to Excel VBA development. While they may be very proficient at VBA, they believe every problem must have a VBA solution and lack sufficient knowledge of Excel to make the best use of its features. Their solutions are often cumbersome, slow and make poor use of Excel's object model. This book has much to offer the VBA Developer to improve their use of Excel itself, including explaining how to architect Excel-based applications, the best practices for designing worksheets and how to use Excel's features for their data entry, analysis and presentation. The book also seeks to improve their Excel VBA development skills by introducing advanced coding techniques, detailing VBA best practices and explaining how to improve their code's performance.

The Excel Developer has realized the most efficient and maintainable applications are those which make the most of Excel's own functionality, augmented by VBA when appropriate. They are confident developing Excel-based applications for their colleagues to use or as part of an in-house development team. While their undoubted knowledge of Excel is put to good use in their applications, it also constrains their designs and they are reluctant to use other languages and applications to augment their Excel solutions. They have probably read John Walkenbach's Excel 97/2000/2002/2003 Power Programming and/or our own Excel 2000/2002 VBA Programmer's Reference and need a book to take them to the highest level of Excel application development – that of the professional developer. This is that book.

The Professional Excel Developer designs and develops Excel-based applications and utilities for their clients or employer that are robust, fast, easy to use, maintainable and secure. While Excel forms the core of their solutions, they include any other applications and languages that are appropriate, such as third-party ActiveX controls, automating other applications, using Windows API calls, using ADO to connect to external databases, C/C++ for fast custom worksheet functions, VB6 or VB.Net for creating their own object models and securing their code and XML for sharing data over the internet. This book teaches all those skills. If you are already a Professional Excel Developer, you will know learning never stops and will appreciate the knowledge and best practices presented in this book by three of your peers.

Excel as an Application Development Platform

If we look at Excel as a development platform and not just a spreadsheet, we can break it down into five fundamental components we can use for our applications:

The worksheet, charts etc, used as a user interface and presentation layer for data entry and reporting

  • The worksheet, used as a simple data store for lists, tables and other information used by our application

  • VBA, Excel's programming language and forms engine

  • The worksheet, used as a declarative programming language for high-performance numerical processing

The Excel object model, allowing programmatic control of (nearly) all of Excel's functionality, from both within Excel and from outside it.

The Worksheet as a Presentation Layer for Data Entry and Reporting

When most people think about Excel, they think in terms of typing numbers into cells, having some calculations update and seeing a result displayed in a different cell or on a chart. Without necessarily thinking in such terms, they are using the worksheet as a user interface for their data entry and reporting and are generally comfortable with these tasks. The in-cell editing, validation and formatting features built in to Excel provide an extremely rich and compelling data-entry experience, while the charting, cell formatting and drawing tools provide a presentation-quality reporting mechanism. It is hard to imagine the code that would be required if we tried to reproduce the experience using the form design tools available in most other development environments, yet it's there waiting for us to use in our Excel-based applications. The biggest problem we face is how structure to the free-form grid of the worksheet, in order to present a simple and easy to use interface, while leveraging the rich functionality Excel provides. Chapter 4 – Worksheet Design introduces some techniques and best practices for developing worksheet-based data entry forms, while Chapter 15 - Advanced Charting Techniques discusses using charting capabilities.

The Worksheet as a Simple Data Store

What is a worksheet when it's never intended to be shown to the end user? At its simplest, it's no more than a large grid of cells in which we can store just about anything we want to – numbers, text, lists, tables or pictures. Most applications use some amount of static data or textual or graphical resources; storing that information in a worksheet makes it both extremely easy to access using VBA and simple to maintain. Lists and tables in worksheets can directly feed Excel's data validation (as shown in Chapter 4 – Worksheet Design), greatly simplify the creation and maintenance of command bars (Chapter 8 – Advanced Command Bar Handling) and allow us to construct dynamic userforms (Chapter 10 – Userform Design and Best Practices).

VBA – Excel's Programming Language

We expect most readers of this book will have at least some familiarity with VBA. If not, we suggest you read either our Excel 2000/2002 VBA Programmer's Reference or John Walkenbach's Excel 97/2000/2002/2003 Power Programming before continuing much further. Many people see the 'A' in VBA as meaning the language is somehow less than Visual Basic itself. In fact, both VB6 and Office 2000 and above use exactly the same DLL to provide the keyword, syntax and statements we program with. The only differences are the objects provided by the run-times (the VB runtime vs the Excel objects), the forms packages (VB's 'Ruby' forms vs Office UserForms) and that VB6 includes a compiler to create EXEs and DLLs, while VBA is always interpreted at run-time. Indeed, the Office Developer Edition (pre-Excel 2003) includes the same compiler VB6 uses, allowing us to compile (simple) DLLs from within the Office Visual Basic Editor.

Most beginner and intermediate VBA developers use VBA as a purely procedural language, with nearly all their code residing in standard modules. VBA also allows us to create applications using an Object Oriented Programming (OOP) approach, in which class modules are used to create our own objects. Chapter 7 – Using Class Modules to Create Objects and Chapter 11 – Interfaces explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.

Most of this book is dedicated to explaining advanced VBA techniques and a professional approach to application design and development which can put using VBA in Excel on a par with, and sometimes in front of, using VB6 or VB.Net for application development. We also show in Chapter 20 – Combining Excel and Visual Basic 6 and Chapter 22 – Using VB.Net and VSTO that the Excel developer can use the best of both worlds, by combining Excel, VB6 and/or VB.Net in a seamless application.

The Worksheet as a Declarative Programming Language

Take the following code:

dSales = 1000dPrice = 10.99dRevenue = dSales * dPrice

That could quite easily be a few lines of VBA. We give the variable dSales a value of 1000, the variable dPrice a value of 10.99, then calculate the revenue as sales times price. If we change the names of the variables and adjust the spacing, the same code could also be written as:

D1   =1000D2   =10.99D3   =D1*D2

which looks much more like worksheet cell addresses and formulas than lines of VBA code, showing that a worksheet is in fact a programming language of its own, if we choose to think of it in those terms. The IF() worksheet function is directly equivalent to the If...Then...Else VBA statement, while the judicious use of circular references and iteration can be equivalent to either the For...Next or Do...Loop structures.

Instead of stating a set of operations that are executed line-by-line, we 'program' in this language by stating a set of declarations (by typing formulae and values into worksheet cells), in any order we want to:

"D3 is the product of D1 and D2"

"D1 has the value 1000"

"D2 has the value 10.99"

To 'run' this program, Excel first examines all the declarations and builds a 'precedence tree' to identify which cells depend on the results of which other cells and thereby determine the most efficient order in which the cells must be calculated. The same precedence tree is also used to identify the minimum set of calculations that must be performed whenever the value in a cell is changed. The result is a calculation engine that is vastly more efficient than an equivalent VBA program, and one that should be used whenever complex numerical computations are required in your application.

Microsoft Excel (and other spreadsheet programs) are unique among application development platforms in providing both a procedural (VBA) and a declarative (the worksheet) programming language. The most efficient Excel application is one that makes appropriate use of both these languages.

It is assumed the reader of this book has some knowledge of Excel and worksheet functions, so Chapter 14 – Data Manipulation Techniques focuses on using advanced worksheet functions (including best-practice suggestions for handling circular references) and Excel's other data analysis features.

The Excel Object Model

While the other four components of the Excel Platform are invaluable in the development of applications, it is probably the richness of the Excel Object Model that provides the most compelling reason to base our application development on Excel. Almost everything that can be done through the user interface can also be done programmatically by using the objects in the Excel Object Model – accessing the list of number formats and applying a digital signature to a workbook are perhaps the most notable exceptions. The vast array of functionality exposed by these objects makes highly-complex applications fairly simple to develop – it becomes more an issue of when and how to efficiently plug the functionality together than to develop the functionality from scratch. This book does not attempt to explore and document all the back-waters of the object model, but instead makes continual use of the objects in our application development.

Structure

Through the course of this book, we will be both covering the concepts and details of each topic and applying those concepts to a time-sheet reporting and analysis application we'll be building. The chapters are therefore arranged approximately in the order in which we would design and develop an Excel application:

Chapter 2 discusses the different styles of application we may choose to create

Chapter 3 identifies some general best-practices for working with Excel and VBA, which will be followed throughout the book

Chapter 4 explains how to design and structure a worksheet for data entry and analysis

Chapters 5 and 6 introduce two specific types of application – the add-in and the dictator application, which will form the basis of our time-sheet reporting and analysis application

Chapters 7 to 13 discuss advanced techniques for a range of VBA topics

Chapters 14 and 15 explain how to efficiently utilise Excel's features within an application to analyse data and present results

Chapters 16 and 17 discuss techniques for debugging and optimising VBA code

Chapters 18 to 22 look outside of Excel, firstly by explaining how to automate other applications, then by explaining how to interact with Excel using C, Visual Basic and VB.Net

Chapter 23 focuses on how Excel applications can make use of the internet and XML

Chapter 24 completes the development by explaining how to provide help, secure, package and distribute the application.

Examples

Throughout the book, we will be illustrating the concepts and techniques we introduce by building a timesheet data-entry, consolidation, analysis and reporting application. This will be comprised of a data-entry template to be completed by each employee, with the data sent to a central location for consolidation, analysis and reporting. The end of each chapter will see a fully-working example of both parts of the application included on the CD, which will grow steadily more complex as the book progresses and thereby be applicable to different types of company.

In Chapter 4 – Worksheet Design, we will start with a very simple data-entry workbook and the assumption that each employee would email the completed file to a manager who would analyse the results manually – a typical situation for a company with maybe 10-20 employees.

By the end of the book, the data-entry workbook will use XML to upload the data to a web site, where it will be stored in a central database. The reporting application will extract the data from the database, perform various analyses and present the results as reports in Excel worksheets and charts.

Along the way, we will be re-writing some of the parts of the application in a number of different ways, to show how easy it can be to include other languages and delivery mechanisms in our Excel-based applications.

Each chapter may also include specific examples to illustrate key points that it would be too artificial to include in our main application.

Supported Versions

When developing an Excel application for a client, their upgrade policy will usually determine which version of Excel we must use; very few clients will agree to upgrade their desktops just so we can develop using the latest version, unless there is a compelling business requirement that can only be satisfied by using features the latest version introduces. There is so little difference between Excel 2000 and Excel 2003 that it is hard to imagine such a business requirement. An extremely unscientific poll (based on postings to the Microsoft support newsgroups) seems to indicate the following approximate usage for each version:

Excel 97 10%

Excel 2000 45%

Excel 2002 40%

Excel 2003 5%

There were a number of significant changes between Excel 97 and Excel 2000 for the application developer, including the switch from VBA5 to VBA6 and the introduction of modeless userforms, interfaces, COM Add-ins and support for ADO. We have therefore decided to use Excel 2000 as our lowest supported version and development platform, with our applications tested in the later versions. Most of the concepts detailed in this book apply equally to Excel 97, but our example timesheet application will use features Excel 97 does not support. Whenever we discuss a feature that is only supported in the later versions (such as XML import/export and VB.Net integration in Excel 2003), we will state which version(s) can be used.

Typefaces

The following text styles are used in this book:

Menu items and dialog text will be shown as Tools > Options > Calculation > Manual, where the '>' indicates navigation to a sub-menu or dialog tab.

Sub SomeCode()  'Code listings are shown like this    'With new or changed lines highlighted like this End Sub

Code within a paragraph will be shown like Application.Calculation = xlManual.

References to other chapters in the book will be shown as Chapter 7 – Using Class Modules to Create Objects.

Paths on the CD will be shown as \Concepts\Ch11 - Interfaces

URLs will be shown as /www.oaltd.co.uk

Important points or emphasised words will be shown like this.

On the CD</ also included in example workbooks on the accompanying CD. For clarity, the code shown in the book uses shorter line lengths, a reduced indent setting, fewer in-code comments and less error handling than the corresponding code in the workbooks.

The CD has three main directories, containing the following files:

\Tools contains a number of tools and utilities developed by the authors which we have found to be invaluable during our application development. The MustHaveTools.htm file contains details about each of these tools and links to other third-party utilities.

  • \Concepts has separate subdirectories for each chapter, each one containing example files to support the text of the chapter. For best results, we suggest you have these workbooks open while reading through the corresponding chapter.

  • \Application has separate subdirectories for each chapter, each one containing a version of our timesheet example application suite. Each chapter ends with a Practical Example section, explaining the changes that have been made to the timesheet application to implement some of the concepts introduced in the chapter.

Help and Support

Questions about the book itself (such as missing CDs, typos, errata etc) should be directed to Addison Wesley, at awprofessional.com/contactus.

Any errata and corrections will be posted to the Addison Wesley web site, at awprofessional.com/title/0321262506.

By far the best place to go for help with any of your Excel development questions – whether related to this book or not – are the Microsoft support newsgroups archives maintained by Google at groups.google.com. search of the archives is almost certain to find a question similar to yours, already answered by one of the many professional developers who volunteer their time helping out in the newsgroups, including all three of this book's authors. On the rare occasions that the archives fail to answer your question, you're welcome to ask it directly in the newsgroups by connecting a news reader (such as Outlook Express) to msnews.microsoft.com and selecting an appropriate newsgroup, such as

microsoft.public.excel.programming for VBA-related questions

microsoft.public.excel.worksheet.functions for help with worksheet functions

microsoft.public.vsnet.vstools.office for help with Excel/VB.Net integration issues

microsoft.public.excel.misc for general Excel enquiries

There are a number of web sites that provide a great deal of information and free downloadable examples and utilities, targeted towards the Excel developer, including:

oaltd.co.uk

appspro.com

j-walk.com

cpearson.com

msdn.microsoft.com/office

Feedback

We have tried very hard to present the information in this book in a clear and concise manner, explaining both the concepts and details needed to get things working and providing working examples of everything we cover. We have tried to provide sufficient information to enable you to apply these techniques in your own applications, but without getting bogged down in line-by-line explanations of entire code listings. We'd like to think we've been successful in our attempt, but encourage you to let us know what you think. Constructive criticism is always welcomed, as are suggestions for topics you think we may have overlooked. Please send an email to one (or all) of the following:

Stephen Bullen: stephen@oaltd.co.uk

Rob Bovey: robbovey@appspro.com

John Green: greenj@bigpond.net.au

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted April 13, 2005

    A remarkably detailed 'how-to' book on creating complete applications using Excel as a base.

    <p>Over the years, I've read a number of books on Excel programming. Each one seemed much like the previous one, generally talking about writing macros and creating data-entry forms. <i>Professional Excel Development</i> takes the concept quite a bit farther. Rather than giving you the same old tired lessons, this book goes into detail on exactly how to build professional level applications. It even explains how to make your Excel-based application look as though Excel had nothing to do with it. Suffice it to say, this ain't your daddy's Excel book. <p>The authors, Stephen Bullen, Rob Bovey, and John Green, show a level of sophistication well beyond the norm. They'd rather teach you the proper way to program instead of teaching you how to use Excel. In fact, the first thing they do is distinguish five different levels of usage: Excel users, Excel power users, VBA developers, Excel developers, and professional Excel developers. The book is written for the highest level, so expect a lot of depth. The entire structure of the book builds around a time-entry application that is developed from a simple spreadsheet to a full-blown, production quality program. A CD-ROM is also included with all of the source code and multiple examples that are scattered throughout the book. <p>Reading <i>Professional Excel Development</i> is not something to be taken lightly. The authors have done a fine job putting together a cohesive methodology for using Excel as an application development platform. I know of no other book that covers this platform in such depth. At times I found myself lost in the details, but I suspect a 'professional Excel developer' (which I am not) would be delighted in the depth of description and copious examples provided. <p><i>Professional Excel Development</i> is an extremely well-written book that covers the use of Excel to a depth few authors have dared to tread. The text gives you the tools to build applications that are much more than automated spreadsheets. Almost any program your imagination can devise can be created using the techniques given, which is a testimony to the power of Excel. Bash Microsoft if you want, but they do sometimes come up with a winner, and <i>Professional Excel Development</i> allows you to take full advantage of its capabilities. I highly recommend this book.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)