I have one special application in Excel that I am especially proud of . This app has functioned for 4 years pulling data from a Data Warehouse using OLEDB and merging multiple data sources and using my work as the core of a major monthly report.
Unfortunately every two or three months I have recieved an emergency call that there is a fundamental problem in the app producing erroneous results. In EVERY case the problem gets tracked back to a user error on the part of the principal user of the final application. See – the one strange thing about this application is that while it is VERY complex and integrates multiple data sources and uses a complex system of queries and custom formulae, the client INSISTS that he be able to make changes whenever he feels necessary – and he has a habit of making changes that break things – not errors on his part perse, but he doesn’t understand the implications of changes on the rest of the app.
I then get a call in a big panic as if it’s a problem with my app. Then he apologizes when it becomes apparent it was his fault (at least he DOES apologize).
Why do I allow this? It is because when we started this application I was contracted to fix or replace a process with two failed previous attempts by others. It was in a big panic, and I was told the goal was to produce a “locked-down” application. In the end though the client is the client, and if they choose after everything NOT to have it locked down – what could I say (??)
My comments to this guy have always the same “Your business process really isn’t changing at all, so these changes are really mostly impulsive on the part of senior management than real changes in the business requirement. Even if they are justified these requests could if necessary be accomodated outside of the application using some kind of Ad-Hoc capability that doesn’t threaten the reliability of thi core report. And if you don’t let us lock this down, in the end IT will insist on replacing it with something you’re simply not going to like and which you won’t be abe to change under any circumstances !!”. This doesn’t work 😦 ….. no change.
Stop the Presses !!….
As I was writing this article I JUST found out that this guy is GONE (as is the way of the world today). This was not staged – this is what really happened today …. Too bad – he’s a great guy.
Now there are a lot of new questions about this application. I know this is a critical report that gets rolled in with world-wide reporting. Who will inherit it? How do we find out where the bones are buried in this app now? Is there any money available for me to swoop in and make it work? If not – then what?? Will it be supported going forward from India? Is the s**t going to hit the fan? ….. Will I get blamed for the fact that the application is so “fragile” at this point with changes that I do not know about?
So in the end my argument against user-maintenance wins out. My idea is to take well defined, mature business processes and automate the reporting for them in such a way that even if I win the lottery (as opposed to getting hit by a truck), the application will be able to tick along as long as necessary. The client will get their money’s worth from my efforts and thus hopefully will be inclined to reuse me for other things. My goal is to make apps that do NOT rely on my support.
So once again my worst nightmares come to pass. My biggest fear is always that my client leaves or is fired and then – who comes in to replace them and what happens from there to this app that is so important to their business processes? And my failure to win the User-Maintainability battle is leaving me exposed.
Here we go again ….
Biggus
p.s. Simon Murphy has a GREAT reference to this article at:
http://smurfonspreadsheets.wordpress.com/2009/07/30/is-change-control-the-critical-factor/
Thanx Simon. Looks like the Circular Reference indicator just lit up on my Browser 🙂
Dick Moffat's Spreadsheet and BI Blog 
Dick,
The expression we use is “get beertrucked” instead of “getting hit by a truck” – sounds like more fun, ya?
In polite company only we say “win the lottery”.
In all seriousity…
Isn’t it part of the problem that your user doesn’t have any “qualified” resources to take ownership of the app?
Yes – there’s NEVER “qualified” resources as backups for this kind of scenario. That’s why any spreadsheets that act as an application and that integrate either as a consumer or as a producer of corporate data has to be “locked down” – even if the apps are done by an internal resource.
This is the kind of scenario that ends up giving spreadsheets a bad name through no fault of their own – actually because they ARE so capable and so flexible. It’s a lose-lose situation.
Biggus
Until I got to the “Stop the Presses” part, I was thinking this is a good example of a $250/hour maintenance contract. It’s a self inflicted emergency, so they should expect to pay an expert to extricate them from their own misdeeds.
But now it just sounds like you’re stuck.
“I was thinking this is a good example of a $250/hour maintenance contract. It’s a self inflicted emergency,”
Yes Jon, but that is the problem – corporations don’t look at spreadsheets the way we wish they should – as a professional pursuit. I like to think I’m a spreadsheet top-gun and it’s really hard to get the that kinda respect in my world.
Maybe I’m doing something wrong 🙂 (??)
Biggus
Not sure how relevant this is to this particular post, but over at Roger’s Access Blog there’s a link to an interesting article about using Access as part of a business strategy. Maybe it will help you make your case.
http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp
This is a great article from Luke. I agree with it 100%. I have been a believer that the solution for most “Departmental” applications is a mix of Access and Excel and that is why I have worked hard to become one of the few who seem to promote themselves as BOTH Excel and Access developers (for what it’s been worth :-)).
I have had this approach since 1992 when Access 1.0 shipped. Unfortunately all I can do is TRY to influence opinions about this technology. Sure when we talk here we are preaching to the choir – but selling this idea in the “REAL” world is nearly impossible. Another reason why I keep saying that the only people who can make a difference on this work for Microsoft – and their bosses just don’t want it to happen because SharePoint is going to cure cancer and find Osama don’t ya know ???
Biggus
@JP – That’s a great read, thanks.
Pingback: Is Change Control The Critical Factor? « Smurf on Spreadsheets
The economics of spreadsheets just don’t work when spreadsheets are treated as professional development platforms. If a company or it’s IT/IS department wants lock-down, spreadsheets are actually the worst possible choice of development platform. Spreadsheets are unnecessary and maybe more of a liability than a benefit for stable reporting systems.
Spreadsheets emerged into widespread use because the many could use them to achieve some automation. That’s still their main strength. Even though there’ve been a few who’ve figured out how to use spreadsheets to achieve a lot of automation, it’s an open question whether or not that was a good thing.
“The economics of spreadsheets just don’t work when spreadsheets are treated as professional development platforms.”
As you might expect I don’t agree with that. If not then why has so much effort been put into adding VBA to the product – just so some users can record their actions?
I believe and have prven that the Excel workspace (including VBA) can be used as a great development environment in its own right. By leveraging the functionality of Excel I can create true “Applications” for Mature business processes (read ones that don’t change every day) that are robust, reliable and extremely cost-effective and compete favourably wth any Web-based solution.
I hope you don’t “dis” Excel for development in your world because I think you are wrong on this and it kinda flies in the face of what I do for a living :-).
Dick
I kind agree with Harlan actually. Excel and Access are excellent for rapid development, and they offer great payback. But if you try and do a full SDLC project with change control, Dev, test and prod, gated reviews and all that other stuff, then actually they don’t shine so brightly. Part of their success is being able to bypass some of the ceremonial aspects of project management.
Thats no disrespect to the tools and us developers using them, I for one love opting out of all that ‘process’ misery. And I know plenty of devs in other tech who dream of our low faff development style.
On one level I agree in principle with Harlan and Simon. Then again, I’ve seen and even built some pretty powerful systems based on Excel automation, so I have to say that Dick’s point is also valid.
Harlan said about large Excel-based automation:
“it’s an open question whether or not that was a good thing.”
For my career, and Dick’s, and numerous others, it’s a very good thing.
I should elaborate. Provocative terminology: real software development must allow for many people working in parallel. Yes, there are problems coordinating multiple developer projects no matter what development platform they use, but there are projects that just don’t make sense as single developer projects.
In spreadsheet terms an example would be one person working on formulas while someone else designs layout/formatting and a 3rd writes and tests VBA code.
Ever tried to do that with Excel or any other spreadsheet? Even with different people working on separate copies of a workbook, merging the changes in their separate branches into the trunk version isn’t trivial. And NO ONE gets to insert or delete anything or redefine ANY names on their own.
Excel and Access are fine for single-developer projects, but they are much less useful for multiple developer projects.
Harlan –
I’m in agreement with your follow-up scenario. The most frustrating part of a project is when a client goes in and makes changes. Whenever possible, if the structure of the project allows, I let the client play with colors and layouts on the templates used for the human interface, and I lock down the important pieces. I know Excel isn’t secure, but when I tell someone I’ve protected it, they assume I know what I’m doing, and they don’t even try to get in.
“Excel and Access are fine for single-developer projects, but they are much less useful for multiple developer projects.”
I’m glad you elaborated…..because I agree totally that multi-developer Excel apps are hard to imagine.
I just think that for one-person development, a person with VBA AND Excel skills can get a lot done. And then locking it down to protect the app and the to protect the user from themselves is just a good idea… It is also a business for many of us – so let’s not discourage it 🙂 eh?
Dick
“but when I tell someone I’ve protected it, they assume I know what I’m doing, and they don’t even try to get in.” Surprisingly that works 99.9% of the time – it’s just a problem when the user fancies themselves an Excel “Expert” or when they are bosses and just don’t accept the premise that they can’t do whatever they want (and still always find someone else to blame) …
Dick
Cool site, love the info.
Bill:
Thanx. Glad to hear that. Hope I can keep thinking of things worth talking about… not too worried about that ..
Dick