Software Development with spreadsheets, continued.

Let’s continue on with yesterday’s theme. I should amend the topic to be spreadsheet development, instead of assuming Excel. I haven’t used Open Office much, but I keep hearing that their macro language is progressing. I hope so; it doesn’t stand a chance of seriously challenging Excel without it.

There are a few serious issues why spreadsheet development gets a bad name: maintainability and scalability.

Let’s start with maintainability, which is essentially the ease with which you can read, understand, and then modify a program, yours or someone else’s. If you’ve done anything serious with spreadsheets you’ve probably seen some pretty messed up stuff; deeply nested formulas which refer to deeply nested formulas scattered around pages of a spreadsheet, with nary a comment to tell you what’s going on. In a word, spaghetti.

No question about it, spreadsheets lend themselves to undisciplined design. I would argue that’s actually a strength, because it lowers the barrier to entry. But yes, I agree, it’s easy, very easy, to write messy spreadsheet code. I’ve done it plenty of times. And yet, I’ve been around enough to have seen (and written) near-undecipherable code in quite a few languages, so that’s nothing unique to spreadsheets. And just like with other languages, you meet this head-on by maintaining coding discipline, thinking about the long-term, and finding out how the experts do it. With Excel in particular you can take advantage of Visual Basic, naming cells, adding comments, and similar techniques. For example, you could write my sudoku spreadsheet using only spreadsheet formulas, but it would be nasty. A judicious use of macros can go a long way.

The other issue I want to address is scalability – if you keep enhancing a spreadsheet, adding functionality, oftentimes you will outgrow Excel, and start trying to push it in directions that it’s not really designed to go in. At this point your choices are usually either to suffer through it and forge ahead, or redo the whole thing using a more appropriate language. Both choices have significant disadvantages. This raises the question of whether you should have picked a different language to begin with.

And that’s a question I’ve struggled with, after some of my spreadsheets have grown to outlandish proportions. But I think in those cases, my conclusion is it was a good problem to have, meaning that by then we had a big, successful program, and the only issue was figuring out the best way to carry it forward. It’s a relatively easy sell to management if you spend a few days hacking up a spreadsheet that a customer can use, and then grow it organically with the customer, than to tell your manager that you need to go away for a few months to develop a heavier-duty program from the get-go. The latter approach is risky, given the percentage of software projects that fail outright, don’t meet the customer’s needs, or are overdue and over budget.