×

Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA / Edition 1
     

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA / Edition 1

5.0 1
by Stephen Bullen
 

ISBN-10: 0321262506

ISBN-13: 9780321262509

Pub. Date: 02/15/2005

Publisher: Addison-Wesley

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

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.

Product Details

ISBN-13:
9780321262509
Publisher:
Addison-Wesley
Publication date:
02/15/2005
Series:
Addison-Wesley Microsoft Technology Series
Edition description:
BK&CD-ROM
Pages:
936
Product dimensions:
7.00(w) x 9.10(h) x 1.80(d)

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.

Customer Reviews

Average Review:

Post to your social network

     

Most Helpful Customer Reviews

See all customer reviews

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA 5 out of 5 based on 0 ratings. 1 reviews.
Guest More than 1 year ago

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.