Professional Excel Development The Definitive

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)

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

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

Summary

Author Biography

Table of Contents

Supplemental Materials

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

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

Summary

Author Biography

Table of Contents

Supplemental Materials

Digital License