Spreadsheet development feature?

This is the first of a series of posts I’m planning about trying to apply some of the generally accepted ideas from mainstream software development to spreadsheeting. I’m a little torn about this because I only really see 2 valid uses for spreadsheets in responsible organisations:

1. For prototyping complex systems, to gain enough understanding to build in something more production focused.
2. As a thin layer between managed reporting repositories (eg Essbase and Analysis Services) and the analyst. 

Spreadsheets are superb at these two things, and that makes them inappropriate for most production systems. My Eusprig paper from 2005 covered this so I’m not going to rehash it here. But I would hate to see any new feature introduced that reduced spreadsheets ability to deliver in these 2 areas in the future.

Data hiding

It is generally accepted in mainstream software development that the concept of data hiding (David Parnas), is fundamental for any significant project.

This isn’t trying to be sneaky or patronising, its all about reducing system fragility.

If a developer locks clients out of certain parts of their code they are then free to change those bits without breaking all clients. This is a good thing. If one of your colleagues has ever changed a workbook that you linked to, breaking your model, you’ll know just what I mean.

If you’ve ever moved VBA between Excel versions you’ll know about this. It just works, even though the Excel team have clearly reworked stuff in the background. (Any upgrade issues I have had have always been down to poor VBA code).

The key issue is to separate the interface from the implementation. In simple terms that means in VBA that any public subs or functions should not change their return type or parameters (number or type) once they have been called elsewhere. You are of course free to change the internals of the procedure, for example if you find a better way. Classic example replacing VBA based finds with range.find which is massively faster.

In the actual spreadsheet grid there is no way to protect your intermediate workings. Anyone anywhere can link to any cell in your workbook. Unless you password protect it, then they can’t get in at all. What if you wanted to let people get at the results but know you wanted to change the intermediate calculations. There is no way currently, names go some of the way.

What about a new formatting or protection option – No Link. This would allow developers to prevent people linking to unstable, or inappropriate values.

You could define a section of the worksheet as ‘Exportable’ or something, or define other bits as ‘Private’ or ‘no link’ or something. Maybe 2 levels, exclude external workbook links, and exclude links from other sheets in the same workbooks. Net result, you steer clients away from bad stuff towards good stuff. Here is an example, the grey area is ‘Private’. What do you think?

[Note the neat customised toolbar btw – try that in Excel 2007!] (actually looking at it, I have never used the undo button (ctrl z), or the sum(alt =), or the help(F1), or that funny auditing one(?) – room for further improvements)

Back to the post

I haven’t built anything to do this, I’m not even sure I could, not without wrapping each formula to check its parameters.

I’m going to run a series of posts on possible developer enhancements, and the most popular (that are possible) may make it into a free community toolset, that is in the pipeline.

Other features I want to cover are:

• defining formulas (UDFs)
• structured worksheets
• some new sheet types
• automated build.

What do you think of spreadsheet based data hiding? would you use it? does it go against the grain of spreadsheet development? Are there other things you think would be much more useful?

Cheers

Simon