Dick M has a great post here about the pain and fragility of user maintained spreadsheets/systems.
I think many of us have been in that situation. I have had to twist Excel to do things because clients do not want the maintenance hassle of VBA. (But they feel happy to maintain millions of cells of complex interrelated formulas).
But I wonder if the real issue is just crap, or non-existent change control? If the client had a test environment and tested before release would that make most of the user maintenance misery go away?
A test environment, or even a test mentality does not have to be difficult. But I sure don’t see it much in End User Computing. Do you?
cheers
Simon
Smurf on Spreadsheets 
Thursday, 30th July, 2009 at 11:36 am |
“If the client had a test environment and tested before release would that make most of the user maintenance misery go away?”
If the client had a test environment and tested before release, the client would be most of their way down the road to being a developer and that’s My Job, dagnabbit.
In a perfect world, users would not use their self-written spreadsheets to do much more than manage their phone lists. OK, they can do more at home, but anywhere they’re doing more than that in a way that affects their employer’s business, it’s a liability.
Of course, I don’t really want them to change – the continuing availability of Excel on the at-work desktop guarantees me employment (should I wish it) sorting out their messes until I drop dead. The great opportunity of Excel 2007 was the prospect of being called in to deal with the inevitable million-rows-and-full worksheet. What kind of daily rate could you charge for that? The mind boggles.
Thursday, 30th July, 2009 at 12:39 pm |
“Of course, I don’t really want them to change – the continuing availability of Excel on the at-work desktop guarantees me employment (should I wish it) sorting out their messes until I drop dead.”
Good point – the ubiquity of Excel is important. Too bad it is SO abused and misused. I personally don’t spend a lot of time fixing screwed up spreadsheets – more time writing Excel “Applications” but I can see the value (and opportunity) of doing so :-)
Dick
Thursday, 30th July, 2009 at 2:56 pm |
Can you have an effective testing environment without having provided some training both for Excel and for software development and maintenance more generally? Show me the company that provides more than a more experienced user within a department showing a new hire where to find the more important spreadsheets on the network and maybe walks them through using them.
Recentralization will happen, but in large companies it’s not going to involve outside developers unless those outside developers live and work in south Asia.
Formulas have the advantage of automatic recalculation. At least that’s an advantage as long as the number of volatile function calls remains modest. VBA approaches require either manual macro execution or event handlers, and I’ve seen enough poorly written and poorly conceived event handlers to have a very high level of skepticism that they’re a net benefit.
As for errors in final results, where is it written there could be no bugs in VBA code? If formula errors are had to detect, why would VBA-generated errors be any easier to spot?
Thursday, 30th July, 2009 at 2:59 pm |
[…] https://smurfonspreadsheets.wordpress.com/2009/07/30/is-change-control-the-critical-factor/ […]
Thursday, 30th July, 2009 at 3:13 pm |
“Recentralization will happen, but in large companies it’s not going to involve outside developers unless those outside developers live and work in south Asia.”
Funny :-) – but true.
Thursday, 30th July, 2009 at 4:45 pm |
Good point Mike on the job protection – what was I thinking??
Harlan I just did a load of Excel and VBA training for a company, but you are right it is rare.
In terms of VBA v formulas, I wasn’t actually thinking of that. I was thinking more of using a bit of VBA to set views etc, instead of replicating a million formulas. So 10 lines of VBA v 10k reformatting formulas – I’d rather check the code.
Friday, 31st July, 2009 at 8:39 am |
Simon, my view is that there’s unlikely to be a singe critical factor given that there are many, many different uses to which spreadsheets are put as well as many, many different levels of skills held by users
A fair amount of what I do involves replicating results from academic papers but with my own VBA code so there is a correct answer to work to (though occasionally I’ll find mistakes in the paper where the algebra and numerical answer disagree)
Where I’m doing more original work, one of my co-authors will check my spreadsheet
I also tend to first build the spreadsheet then write a VBA function to confirm the result – and hopefully the VBA code can be looked over by others relatively easily
And, of course, one easy remedy for any really crucial task is to get two individuals to solve the problem independently and see if their answers agree