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?

no link screen

[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?



10 Responses to “Spreadsheet development feature?”

  1. Dermot Balson Says:

    I disagree strongly about the uses of spreadsheets. If you are building a payroll or budgeting system, sure, a spreadsheet is not the answer, but if you’ve ever worked in a real business, nearly all the jobs are much, much smaller than this, and there is no off the shelf software to do it. It’s totally impractical to suggest that you should hire in a programmer to write something for a one-off small project.

    I work in a large actuarial firm, and I’ve seen a huge variety of projects, hardly any of which could be handled with anything except Excel. Even if we do the same job over and over, each client has important differences, which would stymie any parameterised software.

    In addition, you should not overlook the important fact that if you employed an outsider to program a project “properly”, you introduce a number of problems, including
    * the software becomes a black box for the user, whereas a spreadsheet is more open and can be checked more openly and directly – one of the main objectives of our projects is that they can be checked as easily as possible
    * the programmer has no domain knowledge,which can lead to all sorts of confusion

    As a long term in-house Excel developer, I think spreadsheets have given business an astonishingly powerful all purpose tool. It could include more controls and validation tools, for sure, but education of users is the main problem – rather like young drivers, many of them have blind confidence and no appreciation of the need for defensive design.

  2. Sam Says:

    I am just gessing here….but in 2003 we have “Allow Users to Edit Ranges”……
    Never tried this in a network environment….but can this be used to prevent linking….??


  3. Simon Says:

    Fair enough, note nothing I said suggested involving any specific people, I’m not sure where you got that idea?
    We’ll have to agree to disagree on the application of spreadsheets in production systems, but regardless, do you think something like ‘No Link’ ranges would help with defensive designs?

    Sam – nice try, I’ve never even noticed that option – I’m not a protection fanboy. It didn’t seem to prevent linking locally, I may try proper permisson based test on a network one day (maybe!). If you had a no link option would you use it?

  4. MikeC Says:

    Simon – if there wa a “no link” feature, I would use it. Hell, I would pay extra, from my own pocket, to have that feature on my work pc. Not to be awkward, but to discourage people from linking to some of my work when it’s:

    a) a work in progress
    b) a “tool” which is constantly subject to change depending on circumstances
    c) a report output template where the population is constantly changing (and then people linking to it *complain* that their data keeps changing!!)
    d) data completely different to what they believed it was.

    What’s particularly annoying is that they generally don’t contact me and say “Mike – I’d like to present data x and y, you seem to produce it in report a, can I link to that?” – if they did, I’d be able to give them a more suitable solution. Instead, they just link to an existing sheet that may or may not do what they think it does, and then blame me when their data’s wrong.


    So I would lock pretty much everything to prevent the above and force them to come to me when they want data within my remit, rather than just randomly linking to whatever they can find.


  5. Simon Says:

    Sounds like we’ve worked in the same place, unless this happens in more than one company???
    Like you I’d see it as a safety thing, rather than some control issue.
    It might be nice too to know who/what is linking into your stuff too.

  6. MikeC Says:

    Well I AM in the UK Simon, so you never know… and my current company does have an office in Cardiff! =;-) But I’m sure that there’s at least one other company out there in the world with sloppy practices of this nature… surely there must be at LEAST one… Oh yeah – everywhere else I’ve worked since 1995 for a start!

    As an extra feature, prompted by what you say above, it would be fantastic to be able to check a sheet/workbook to see if any other document has active links to it – kinda like the “Trace Dependants” feature but one that can see other workbooks that link, even if they aren’t open (as Trace only shows other workbooks if they’re open at that time). Of course that’s completely impossible (as far as I know, anyway. Watch someone tell me how to do it now….) but it’s a nice thought, and it keeps me warm at night that one day it might be achievable…

  7. Simon Says:

    Yeah everywhere I’ve ever worked too Mike, funny old thing.
    Some of the audit tools (scanxls and Exchecker spring to mind) will scan a network drive and tell you which wbs link into your one. They’re not exactly instant though. Having something built in would be handy enough, dunno if its possible/realistic, probably not, especially to pick up people like me who prefer to use VBA to import rather than links.

  8. Ross Says:

    Yeah I dont think you can get around it with protection at workbook, sheet or file level – user group or not – you can just target a protected range from another book. Try setting up file level protection, then targeting the range from another wkb, you get asked for the password, you press cancel, and excel drops the data right into your wks!

    I would have to ask why you place such workbooks is a place where anyone but you can get at them?

    To the issue of having a workbook that you share with an area of cells that you want to “lock down” – yes that is a harder issus = not sure how you could do that Simon – I would guess the calling workbook makes some sort of inquire into the biff file, looks for the range then get the values stored there – my gut feeling is that you might have to fig aroud with the file format to “solve” this issue – could be tricky ;-)

  9. Simon Says:

    >>I would have to ask why you place such workbooks is a place where anyone but you can get at them?

    So its easier to link to other peoples stuff without asking permission of course ;-)

  10. BPS Says:

    this is a nice website on excel spreadsheets.I quite like this one but I have to get used to this online spreadsheet now.I have become a member of some and I think i did learn something from the same as how well spreadsheets are made.I learnt to make some good spreadsheets seeing the online way of functioning in spreadsheets.I saw the size of some of the well made spreadsheets on zohosheet as i did work with zohosheet inc.Now i am working with EditGrid and this is a good online spreadsheet based company just meant for making and then re-using those spreadsheets in your own work somewhere.I am a employee of EditGrid now as i am sending my spreadsheets to them to evaluate and re-evaluate and other stuff and they did put my sheets on their website this gives me end amount of satisfaction i need my hardwork payed off finally.I will be employed with this company now for the next few months.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: