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

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

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.  

About the Author :
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). © Copyright Pearson Education. All rights reserved.