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

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

by Stephen Bullen, Rob Bovey, John Green
     
 

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

See more details below

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

Product Details

ISBN-13:
9780321262509
Publisher:
Addison-Wesley
Publication date:
02/01/2005
Series:
Addison-Wesley Microsoft Technology Series
Edition description:
BK&CD-ROM
Pages:
936
Sales rank:
1,228,672
Product dimensions:
7.00(w) x 9.20(h) x 2.10(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.

Read More

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >