Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Excel as an Application Development Platform

If we look at Excel as a development platform and not just a spreadsheet, we can break it down into five fundamental components we can use for our applications:

The worksheet, charts and so on, used as a user interface and presentation layer for data entry and reporting

The worksheet, used as a simple data store for lists, tables and other information used by our application

VBA, Excel's programming language and forms engine

The worksheet, used as a declarative programming language for high-performance numeric processing

The Excel object model, allowing programmatic control of (nearly) all of Excel's functionality, from both within Excel and from outside it

The Worksheet as a Presentation Layer for Data Entry and Reporting

When most people think about Excel, they think in terms of typing numbers into cells, having some calculations update and seeing a result displayed in a different cell or on a chart. Without necessarily thinking in such terms, they are using the worksheet as a user interface for their data entry and reporting and are generally comfortable with these tasks. The in-cell editing, validation and formatting features built in to Excel provide an extremely rich and compelling data-entry experience, while the charting, cell formatting and drawing tools provide a presentation-quality reporting mechanism. It is hard to imagine the code that would be required if we tried to reproduce the experience using the form design tools available in most other development environments, yet it's there waiting for us to use in our Excel-based applications. The biggest problem we face is how to add some structure to the free-form grid of the worksheet, to present a simple and easy-to-use interface, while leveraging the rich functionality Excel provides. Chapter 4 Worksheet Design introduces some techniques and best practices for developing worksheet-based data-entry forms, and Chapter 15 Advanced Charting Techniques discusses using Excel's charting capabilities.

The Worksheet as a Simple Data Store

What is a worksheet when it is never intended to be shown to the end user? At its simplest, it is no more than a large grid of cells in which we can store just about anything we want tonumbers, text, lists, tables or pictures. Most applications use some amount of static data or textual or graphical resources; storing that information in a worksheet makes it both extremely easy to access using VBA and simple to maintain. Lists and tables in worksheets can directly feed Excel's data validation (as shown in Chapter 4 Worksheet Design), greatly simplify the creation and maintenance of command bars (Chapter 8 Advanced Command Bar Handling), and enable us to construct dynamic userforms (Chapter 10 Userform Design and Best Practices).

VBA: Excel's Programming Language

We expect most readers of this book to have at least some familiarity with VBA. If not, we suggest you read either our Excel 2000/2002 VBA Programmer's Reference or John Walkenbach's Excel Power Programming before continuing much further. Many people see the A in VBA as meaning the language is somehow less than Visual Basic itself. In fact, both VB6 and Office 2000 and above use exactly the same DLL to provide the keywords, syntax and statements we program with. The only differences are the objects provided by the runtimes (the VB runtime vs. the Excel objects), the forms packages (VB's "Ruby" forms vs. Office UserForms) and that VB6 includes a compiler to create EXEs and DLLs, whereas VBA is always interpreted at runtime. Indeed, the Office Developer Edition (pre-Excel 2003) includes the same compiler VB6 uses, enabling us to compile (simple) DLLs from within the Office Visual Basic Editor.Chapter 7 Using Class Modules to Create Objects and Chapter 11 Interfaces explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.Chapter 20 Combining Excel and Visual Basic 6 and Chapter 22 Using VB.NET and the Visual Studio Tools for Office that the Excel developer can use the best of both worlds, by combining Excel, VB6 and/or VB.Net in a seamless application.

The Worksheet as a Declarative Programming Language

Consider the following code:

dSales = 1000
dPrice = 10.99
dRevenue = dSales * dPrice

This code could quite easily be a few lines of VBA. We give the variable dSales a value of 1000, the variable dPrice a value of 10.99 and then calculate the revenue as sales times price. If we change the names of the variables and adjust the spacing, the same code could also be written as follows:

D1   =1000
D2   =10.99
D3   =D1*D2

This preceding code looks much more like worksheet cell addresses and formulas than lines of VBA code, showing that a worksheet is in fact a programming language of its own, if we choose to think of it in those terms. The IF() worksheet function is directly equivalent to the If...Then...Else VBA statement, and the judicious use of circular references and iteration can be equivalent to either the For...Next or Do...Loop structures.

Instead of stating a set of operations that are executed line by line, we "program" in this language by stating a set of declarations (by typing formulas and values into worksheet cells), in any order we want to:

"D3 is the product of D1 and D2"
"D1 has the value 1000"
"D2 has the value 10.99"

To "run" this program, Excel first examines all the declarations and builds a "precedence tree" to identify which cells depend on the results of which other cells and thereby determine the most efficient order in which the cells must be calculated. The same precedence tree is also used to identify the minimum set of calculations that must be performed whenever the value in a cell is changed. The result is a calculation engine that is vastly more efficient than an equivalent VBA program, and one that should be used whenever complex numeric computations are required in our applications.Chapter 14 Data Manipulation Techniques focuses on using advanced worksheet functions (including best-practice suggestions for handling circular references) and Excel's other data-analysis features.

The Excel Object Model

Although the other four components of the Excel platform are invaluable in the development of applications, it is probably the richness of the Excel object model that provides the most compelling reason to base our application development on Excel. Almost everything that can be done through the user interface can also be done programmatically by using the objects in the Excel object modelaccessing the list of number formats and applying a digital signature to a workbook are perhaps the most notable exceptions. The vast array of functionality exposed by these objects makes highly complex applications fairly simple to developit becomes more an issue of when and how to efficiently plug the functionality together than to develop the functionality from scratch. This book does not attempt to explore and document all the backwaters of the object model, but instead makes continual use of the objects in our application development.