9780321262509

Direct from the most respected authorities on Excel, this book will be the definitive guide to developing applications with Microsoft Excel.

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).

Acknowledgments

xv

About the Authors

xvii

Chapter 1: Introduction

1

(12)

About This Book

1

(1)

The Excel Developer

2

(1)

Excel as an Application Development Platform

3

(4)

Structure

7

(1)

Examples

8

(1)

Supported Versions

9

(1)

Typefaces

9

(1)

On the CD

10

(1)

Help and Support

11

(1)

Feedback

12

(1)

Chapter 2: Application Architectures

13

(16)

Concepts

13

(12)

Conclusion

25

(4)

Chapter 3: Excel and VBA Development Best Practices

29

(40)

Naming Conventions

29

(13)

Best Practices for Application Structure and Organization

42

(5)

General Application Development Best Practices

47

(21)

Conclusion

68

(1)

Chapter 4: Worksheet Design

69

(40)

Principles of Good Worksheet UI Design

69

(1)

Program Rows and Columns: The Fundamental UI Design Technique

70

(1)

Defined Names

71

(7)

Styles

78

(6)

User Interface Drawing Techniques

84

(5)

Data Validation

89

(3)

Conditional Formatting

92

(7)

Using Controls on Worksheets

99

(2)

Practical Example

101

(6)

Conclusion

107

(2)

Chapter 5: Function, General and Application-Specific Add-ins

109

(34)

The Four Stages of an Application

109

(3)

Function Library Add-ins

112

(8)

General Add-ins

120

(1)

Application-Specific Add-ins

121

(7)

Practical Example

128

(14)

Conclusion

142

(1)

Chapter 6: Dictator Applications

143

(24)

Structure of a Dictator Application

143

(16)

Practical Example

159

(7)

Conclusion

166

(1)

Chapter 7: Using Class Modules to Create Objects

167

(32)

Creating Objects

167

(5)

Creating a Collection

172

(7)

Trapping Events

179

(3)

Raising Events

182

(8)

Practical Example

190

(7)

Conclusion

197

(2)

Chapter 8: Advanced Command Bar Handling

199

(56)

Command Bar Design

199

(2)

Table-Driven Command Bars

201

(21)

Putting It All Together

222

(10)

Loading Custom Icons from Files

232

(5)

Hooking Command Bar Control Events

237

(9)

Practical Example

246

(7)

Conclusion

253

(2)

Chapter 9: Understanding and Using Windows API Calls

255

(44)

Overview

255

(6)

Working with the Screen

261

(3)

Working with Windows

264

(10)

Working with the Keyboard

274

(5)

Working with the File System and Network

279

(15)

Practical Examples

294

(3)

Conclusion

297

(2)

Chapter 10: Userform Design and Best Practices

299

(60)

Principles

299

(10)

Control Fundamentals

309

(7)

Visual Effects

316

(9)

Userform Positioning and Sizing

325

(7)

Wizards

332

(4)

Dynamic Userforms

336

(8)

Modeless Userforms

344

(6)

Control Specifics

350

(7)

Practical Examples

357

(1)

Conclusion

358

(1)

Chapter 11: Interfaces

359

(32)

What Is an Interface?

359

(2)

Code Reuse

361

(2)

Defining a Custom Interface

363

(1)

Implementing a Custom Interface

364

(2)

Using a Custom Interface

366

(2)

Polymorphic Classes

368

(5)

Improving Robustness

373

(1)

Simplifying Development

374

(12)

A Plug-in Architecture

386

(2)

Practical Example

388

(1)

Conclusion

389

(2)

Chapter 12: VBA Error Handling

391

(44)

Error-Handling Concepts

391

(10)

The Single Exit Point Principle

401

(1)

Simple Error Handling

402

(1)

Complex Project Error Handler Organization

403

(5)

The Central Error Handler

408

(7)

Error Handling in Classes and Userforms

415

(1)

Putting It All Together

416

(7)

Practical Example

423

(10)

Conclusion

433

(2)

Chapter 13: Programming with Databases

435

(56)

An Introduction to Databases

435

(18)

Designing the Data Access Tier

453

(1)

Data Access with SQL and ADO

454

(21)

Further Reading

812

(1)

Practical Example

812

(4)

Conclusion

816

(3)

Chapter 23: Excel, XML and Web Services

819

(44)

XML

819

(24)

Web Services

843

(7)

Practical Example

850

(11)

Conclusion

861

(2)

Chapter 24: Providing Help, Securing, Packaging and Distributing

863

(22)

Providing Help

863

(9)

Securing

872

(5)

Packaging

877

(6)

Distributing

883

(1)

Conclusion

884

(1)

Index

885

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

= 0) {slash = '\\';} else {slash = '/';}openLoc = figLoc.substring(0, figLoc.lastIndexOf(slash) + 1);while (pPage.substring(0,3) == '../') {openLoc = openLoc.substring(0, openLoc.lastIndexOf(slash, openLoc.length - 2)+ 1);pPage = pPage.substring(3, pPage.length + 1);}popUpWin =window.open('','popWin','resizable=1,scrollbars=1,location=0,toolbar=0,width=525,height=394');figDoc = popUpWin.document;zhtm= ' ' + pPage + ' ';zhtm += ' ';zhtm += ' ';zhtm += ' ';zhtm += '' + pPage.substring(pPage.lastIndexOf('/') + 1, pPage.length) + '';zhtm += ' ';figDoc.write(zhtm);figDoc.close();}// modified 3.1.99 RWE v4.1 --> Ch1 - Introduction Chapter 1: Introduction About This BookMicrosoft 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 ourExcel 2000/2002 VBA Programmer's Reference, John Walkenbach'sExcel N Power Programmingor similar titles. The Excel DeveloperExcel developers can be divided into five general categories, based on their experience and knowled

Amazon no longer offers textbook rentals. We do!

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

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

0321262506

Summary

Author Biography

Table of Contents

Supplemental Materials

Excerpts