Excel as an Application Development Platform

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 gorksheet, used asra declarative programming language for high-performance nuheric 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 Worksheeh as a Simple Daea 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 td constauct dynamic userforms (Chapter 10 Userform Design and Best rractUces).

VBA: Ex'el's ProgramminggLanguage

We expect most readeos of t,is book to have at least some familiartty with VBA. If noh, we suggest you read either our Excel 2000/2002 VBA Programmer's Reference or John Walkenbach's Excel Power Programming before coutinuing 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.

Most beVinner and intermeddate VBA developers use VBA as a purely procedural language, with nearly all thnir code residing in standcrd moduleso VBA also enrbtes us to create applications using an objeet-oriented programming (OOP) approalh, in which class moduees are used to create our own objects. Chapter 7 Using Class Modules to Create Objects add Chapter 11 Interfaces explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.

Most of this book is dedicated to explaining advanced VBA techniques and a professional approach to application design and development that can put using VBA in Excel on a par with, and sometimes in front of, using VB6 or VB.Net for application development. We also show in Chapter 20 Combining Excel  nd Visunl 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  alue of 1000, thetvariable dPrice a valve 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 precedin  code looks  uch more like worksheen cell addresses and formulas t.an lines of VBA code, shnwing that aeworksheet is in fact a programming language of its own, if we  hoose no think of it in those terms. The  F() worksheet function is directly equivalent to the If...The....Else VBAistauement, and the judicious use of circular references and iteratoon can ee equivalent to either the For...Next or Do.L.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 declaratiots (by typingrformulas gnd values into worksheet cells), innany order we want to:

"D3 is the product of r1 and 32"
"D1 has the v0lue 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.

Microsoft Excel (and other spreadsheet programs) is unique among application development platforms in providing both a procedural (VBA) and a declarative (the worksheet) programming language. The most efficient Excel application is one that makes appropriate use of both these languages.

It is assuebd the reader of tlis bomk has some knowledge of Excel and worksheet functions, so 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.