Product 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

---

Product Details

• Amazon Sales Rank: #15760 in Books
• Published on: 2009-05-16
• Original language: English
• Number of items: 1
• Binding: Paperback
• 1176 pages

From the Back Cover

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

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

• Building add-ins to provide new Excel functions

• Designing effective worksheets, userforms and other user interface elements

• Leveraging Excel's powerful data analysis features

• Creating sophisticated custom charts

• Handling errors, debugging applications and optimizing performance

• Using class modules and interfaces to create custom objects

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

• Adding worksheet functions with

• C-based XLLs

• Programming with databases

• Controlling external applications from Excel

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

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

• Providing help, securing, packaging and distributing

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

© Copyright Pearson Education. All rights reserved.

About the Author

Rob Bovey, President of Application Professionals, has developed several Excel add-ins shipped by Microsoft. He coauthored the Microsoft Excel 97 Developers Kit and Excel 2007 VBA Programmer’s Reference.

Dennis Wallentin has developed Excel solutions since the 1980s through his firm, XL-Dennis, based in Östersund, Sweden.

Stephen Bullen, coauthor of The Excel 2007 VBA Programmer’s Reference, owns Office Automation, Ltd., based in Essex, Ireland.

John Green owns Execuplan Consulting, a Sydney, Australia-based consultancy specializing in Excel and Access development.

Bovey, Bullen, and Green hold Microsoft’s prestigious Most Valuable Professional (MVP) honor.

 

Excerpt. © Reprinted by permission. All rights reserved.

Introduction

About This Book

Microsoft Excel is much more than just a spreadsheet. With the introduction of the Visual Basic Editor in Excel 97, followed by the significantly improved stability of Excel 2000, Excel became a respected development platform in its own right. Excel applications are now found alongside those based on C++, Java, and the .NET development platform, as part of the core suite of mission-critical corporate applications.

Unfortunately, Excel is still too often thought of as a hobbyist platform, that people only develop Excel applications in their spare time to automate minor tasks. A brief look at many Excel VBA books seems to confirm this opinion. These books focus on the basics of automating Excel tasks using VBA. This book is the first of its kind in providing a detailed explanation of how to use Excel as the platform for developing professional quality applications.

While most other major development platforms seem to have a de facto standard text that explains the commonly agreed best practices for architecting, designing, and developing applications using that platform, until now Excel has not. This book attempts to fill that gap. The authors are professional Excel developers who create Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book explains the approaches we use when designing, developing, distributing, and supporting the applications we write for our clients.

Who Should Read This Book

This is not a beginner-level book. If you do not already have a clear understanding of the core Excel object model and a basic understanding of Excel VBA development this is not the place to start. We assume that readers of this book have already read and (mostly) understood our Excel 2002 or 2007 VBA Programmer’s Reference, John Walkenbach’s Excel Power Programming, or similar titles. This book begins where other Excel VBA books end.

Owners of the first edition of Professional Excel Development have a different decision to make. Should you purchase the second edition? We have made numerous corrections and improvements throughout this edition as well as expanding it with over 300 pages of new material that you simply will not find anywhere else.

In the interest of full disclosure, however, we want to be very clear that the bulk of the new material is aimed at Excel developers who are working with Excel 2007 and Visual Studio 2008. If you own the first edition of this book and your primary focus is developing VBA applications in Excel 2003 and earlier, you will see incremental rather than revolutionary improvements in this edition. We don’t want to discourage you from upgrading to the second edition and would welcome it if you choose to do so. But most of all we want you to be satisfied with our work, so we state the pros and cons of upgrading honestly to help you make an informed decision.

Excel Developer Categories

Excel developers can be divided into five general categories based on their experience and knowledge of Excel and VBA. 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.

Basic Excel users probably don’t think of themselves as developers at all. 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 Excel features their workbooks may begin to include more complex worksheet functions, pivot tables, and charts. There is little in this book for basic Excel users, although Chapter 4, “Worksheet Design,” details the best practices to use when designing and laying out a worksheet for data entry; Chapter 20, “Data Manipulation Techniques,” explains how to structure a worksheet and which functions and features to use to manipulate their lists; and Chapter 21, “Advanced Charting Techniques,” explains how to get the most from Excel’s chart engine. 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 worksheets increase.

Excel power users have a broad understanding of Excel’s functionality and they know which tool or function is best used in a given situation. Power users create complex workbooks for their own use and are often called on to help develop workbooks for their colleagues, or to identify why their colleagues’ workbooks don’t work as intended. Power users occasionally use snippets of VBA, either found on the Internet or created with the macro recorder, but struggle to adapt the code to their needs. As a result, their code tends to be messy, slow, and hard to maintain. While this book is not a VBA tutorial, power users have much to gain from following the best practices we suggest for both worksheets and code modules. Most of the chapters in the book are relevant to power users who have an interest in improving their Excel and VBA development skills.

VBA developers make extensive use of VBA code in their workbooks—often too much. They are typically either power users who started to learn VBA too early or Visual Basic developers who switched to Excel VBA development. While they may be proficient with VBA they believe every problem must have a VBA solution. They tend to lack the experience required to know when a problem is best solved using Excel, when a problem is best solved using VBA, and when the best solution is a combination of the two. Their solutions are often cumbersome, slow, and make poor use of the Excel object model. This book has much to offer VBA developers to improve their use of Excel itself, including best practices for designing worksheets and how to use Excel’s features for 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 VBA code performance.

Excel developers realize that the most efficient and maintainable applications are those that make the most of Excel’s built-in functionality, augmented by VBA where appropriate. They are confident in developing Excel-based applications for their colleagues or as part of an in-house development team. While their knowledge of Excel is put to good use in their applications, their design techniques tend to be limited, and they are reluctant to use other languages and applications to augment their Excel solutions. They have probably read John Walkenbach’s Excel 2003 or 2007 Power Programming and/or our own Excel 2002 or 2007 VBA Programmer’s Reference. Now they need a book to take them to the highest level of Excel application development—that of the professional developer. This is the book to do that.

Professional Excel developers design and develop for their clients or employer Excel-based applications and utilities that are robust, fast, easy to use, maintainable, and secure. While Excel forms the core of their solutions, they use other applications and languages where appropriate, including third-party ActiveX controls, Office automation, Windows API calls, external databases, various standalone programming languages, and XML. This book teaches all of those skills. If you are already a professional Excel developer, you will know that learning never stops and will appreciate the knowledge and best practices presented in this book by four of your peers.

Excel as an Application Development Platform

If we look at Excel as a development platform rather than just a spreadsheet, we find that it provides five fundamental components we can use in our applications:

• The worksheets, charts, and other objects used to create a user interface and presentation layer for data entry and reporting
• The worksheets used as simple data stores for lists, tables, and other information required by our application
• VBA code and UserForms for creating business logic and advanced user interfaces
• Worksheet formulas 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, both from within Excel and from outside it

The Worksheet as a Presentation Layer for Data Entry and Reporting

Most people think about Excel 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 a 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 this experience using the tools available in most other development environments, yet Excel provides these features right out of the box for use in our Excel-based...

---

Customer Reviews

A remarkable book on creating complete Excel-based apps
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. Professional Excel Development 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.
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.
Reading Professional Excel Development 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.
Professional Excel Development 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 Professional Excel Development allows you to take full advantage of its capabilities. I highly recommend this book.

Wisdom Packed into Every Page! Brilliantly Written
Even if this book was not as well-written and insightful as it is, there is simply no other VBA book that comes close, in content and quality, on building professional-grade Excel applications. If that is your field, you have a lot to lose by not getting it. (I've stalked several large bookstores here in New York City and Amazon, of course.)

THE BOOK:
This book is a goldmine of application-design wisdom for developers seeking to write professional, VBA applications that stand the test of time. Even for none-professionals, like myself, the book contains a plethora of VBA "best practices" that can be put to immediate use. The authors are clearly sharing with you years of personal, professional experience, and top-notch acumen.
Because I am not the most attentive reader, the first thing I find myself looking at is how well-written a book is (a quality which makes me a Walkenbach fan). I am impressed by how "thorough", "well put", and "to the point" most sentences in this book are - from the first to last word!

A FEW HIGHLIGHTS FOR ME:
> Imagine building almost bullet-proof applications. The user never sees an ugly VBA error message, and, if an error occurs, the developer usually knows very quickly what the cause is. I do this now...courtesy of the book's excellent chapter 12 on Error Handling and a thorough chapter on Debugging (Chapter 16).
> Chapter 17 - Optimizing VBA Performance (and a little digression on creative thinking) is a must read. This alone is worth the book price!
> Imagine a progress-bar display that you can easily "plug in" to any VBA program you write. Now you can...this book shows you how, in Chapter 11.
> Do you understand how to use API functions, and which ones are most useful for Excel. Read Chapter 9. I first found this chapter online (informit.com), which led me to this book, in the first place. Knowing API, will allow you to tap into the entire windows operating system from VBA.
> Do you really understand classes...do you understand Interfaces and how you can put them to use in VBA?
Chapter 7 on Classes is worth a solid read. (Even if you've read chapter 5 & 6, in Ken Getz's VBA Developer's handbook). Chapter 11, demonstrates the usefulness of Interfaces, with two great examples (Sorting and Progress-Display).
> What if you wish to write code in .Net, C, (or VB6) yet still work with EXCEL or connect with your VBA code? This book shines on this topic with three chapters (20 - 22). I have not yet seen a discussion as thorough in another VBA book. (If I've missed one, please add me to your Amazon buddy list and email me. Thanks.)
> If you are not a database expert but want a good overview of using Excel to work with Databases, I suggest your read Chapter 13.
> How do "you" currently go about building your Application's Menubars and ToolBars? Most books recommend a table-driven system of some sort or another, which is what I was using, and, indeed, this book does, too...but wait until you see the authors' version in Chapter 8. In fact, I made the painful decision to give up my habit of doing things after spending time with this chapter, and the authors' wisdom is paying off!
As a reviewer from California put it, "Finally, The sequel to Walkenbach for developers is here!", and another from New Delhi, "Every line that I have read so far has a meaning. The book is written in a simple fluent language and brings out the point very clearly. It will take me to the next level of programming."
I could not agree more!

ACCESSORIES:
As you would expect, all code examples for the book (and more) are found on an accompanying CD. The excellent commenting of code, aids learning.
I do have one peeve: The book is not available in searchable PDF format. To be fair to the authors...if they did...your grandmother would probably have it by now ;-) (For a fee you can download chapters in html from http://safari.oreilly.com/)
I also use two, must-have, VBA Add-Ins produced by Stephen Bullen and Rob Bovey respectively, free of charge from their respective websites.
Stephen Bullen's Auto-Indenter: with a simple "right click", your VBA code is automatically indented at all the right places (with options to suit your personal preferences). Think of how much time you spend tabbing and moving lines about!
Rob Bovey's code-cleaner: which cleans up your VBA file and shrinks its size significantly. (It also gets rid of a mysterious VBA error, the name of which escapes me now).

Finally! The sequel to Walkenbach for developers is here!
If you cherished the Walkenbach's book for years and were longing for the advanced part to follow some day, you are there. So what if it is by different author.

Once I got the book and browsed through for couple of hours I already was eager to share my excitement. But... decided to do some more reading to make sure that the quality holds throughout the book. And I am still impressed immensely. This is an outstanding book

Buy the book even just for Chapter 20. Combining Excel and Visual Basic 6.

The rumors about the death of VB6 are highly exaggerated. Even if you believe otherwise, the techniques described for using VB forms in VBA will give you a good grasp how to achieve the same thing with other languages.

Basically, if you are a developer and were looking for a book to let you outgrow the limitations of VBA, this is THE BOOK.

If you are a power user and would like to stay in confines of Excel and VBA, then most probably Walkenbach's book will be sufficient, although reading this book will give you an incentive to do more.