When I started my new job 18 months ago, the spreadsheets I found were not exactly the kind of spreadsheets that I would make. They were, I would guess, pretty typical of spreadsheets in small (and large) business around the world. They were used to collect and track data and they did their job for the most part. Here are some of the problems I noted:
- Most of the data was screaming to be in a relational database. “Normalization” was a word that didn’t exist in anyone’s vocabulary. We had one customer whose name was spelled at least three different ways. The same data that existed in the accounting system was repeated in the spreadsheet and not always reconciled.
- The spreadsheets lived wherever the creator wanted them to live. Not everyone knew where this was and two people could be tracking similar information in two different places.
- Data was copied and pasted between spreadsheets. A change in a downstream spreadsheet would be manually input in the source.
- Some of the workbooks were down-right huge. I’ve seen enough corrupted workbooks to be worried about five-year-old workbooks with 30 worksheets.
- No VBA code. This, of course, is not a problem, but a characteristic. I only mention it because it’s the point of this post.
Let me say a few nice things about these spreadsheets. None of them were shared. They used “Read-Only Recommended” quite well so that users only opened them in write-mode when they intended on entering data. The spreadsheets were complex and hard to navigate. But the users were adept at using and navigating them. They got the job done, albeit less efficiently than they could have.
My idea of reconciliation is looking at two totals and noting that they match. In order to achieve this, I have to create systems that ensure perfect data entry. When data is entered completely and accurately once, there’s no need to figure out why one total doesn’t match another.
I’ve set out to mold these spreadsheets in my own image (you knew I would). It’s been a slow process, to say the least, but progress has been made. I’m nearing completion of one of the more complex projects and I started thinking about how I’ve simplified things. To simplify, I’ve shifted the complexity from the front-end to the back-end, which is to say I haven’t simplified it at all. It may be super easy to enter the correct data one time in one place, but the machinations that allow this are more complex than the non-normalized, scattered, huge spreadsheets they replaced.
Now instead of having a handful of people who can navigate a maze of spreadsheets, we have exactly one guy who can fix a problem in the code. Of course my code is perfectly structured, copiously commented, and bug-free. Yeah, right. There’s no question that I’ve improved day-to-day efficiency, but what happens when something goes wrong and I’m not there to fix it? Like most quandaries in life, this one comes down to the hit-by-bus scenario. If I get run over by a bus tomorrow, have I done this company a service? Am I the only who thinks about this stuff?
Funny, I was just thinking this same thought as I came to your site. I’ve nearly done building an application for a client with (I think) an elegant user interface – but reams of complex code, dynamic ranges, etc etc going on in the background that no matter how hard I try to document clearly would be really difficult for anyone else to understand. Right now the client is really appreciative, but I feel kinda guilty, because I know that if I were to be hit by that bus…
this one comes down to the hit-by-bus scenario. If I get run over by a bus tomorrow, have I done this company a service? Am I the only who thinks about this stuff?
Ha ha! I don’t know if you are, but I like the way you think.
I am often thinking of ways that other people can do things better, but I am (very, very slowly) learning to pick my fights carefully (I think the word ‘fight’ is quite apt w.r.t. these sort of spreadsheet solutions!). Suggesting how you could improve someone elses job, inevitably results in oneself volunteering to first understand how they do it, the working behind it, the bugs and the subtleties, what you see should be improved, implementing the changes and then providing instruction on how to use your new system. Then comes the maintenance phase. So what results is that what was once a self-contained, single person task is now a two-person job, reliant on you! Quandary indeed!
Dick, this is one I have big-time… Although I have provided ridiculous levels of user documentation for my “big projects” at work (50-page user guides with screen shots and “press this button” type instructions are usual), I know full well, that in the “meteorite lands on my house” scenario (I’ve already been hit by a bus, so I need to move up a grade…) they would work just fine – until something changes…
Unfortunately, despite the fact that I’ve been asking, nay, CLAMOURING, for some backup, either someone who knows what they’re doing with Excel/VBA*, or can learn to “on the job” within a reasonable length of time, I have got precisely nowhere. Serves me right for never taking time off sick, and always having my cellphone on, I guess!!
I suppose the point I’m making is that, whilst I have been concerned by this stuff, the company I work for isn’t. And if I have an accident, or leave for another job, that’s the precise moment they will begin to care, because in the meantime it’s far too much time, effort and money.
*note that “knows what they’re doing” is by my standards. Not yours. Because by the standards of most of the people on here, I just about qualify as an enthusiastic amateur…
Oops. I may have just done it again…
I suppose the key here is succession planning… Is there anyone you can train to be your understudy? If not, can someone be recruited?
I expect the answer here is no probably due to 3 reasons – Cost, Resource and Willingness.
Cost – not only direct monetary cost, but also time used to train, document and roll out any new solutions.
Resource – Assuming that cost rules out anyone being recruited, is there anyone capable of learning this (absorbing it into their existing workload)?
Willingness – Is the company willing to employ someone with the skills (see Cost…)? Are there any existing employess willing to take on a more technical role?
I have a succession plan in place to train members of my team to do my job – that includes technical Excel / VBA skills as well as providing the company knowledge and experience. It’s not an overnight process and does take a significant investment of my time but I think it is worth it in the long run.
I think the real key to obtaining buy-in here from the company is to highlight all of the risks. The old system had 4/5 risks highlighted in your post.
What are the risks of the new system? Are these any less if you were to be removed from the equation?
I’m not sure about being knocked down by a bus, but we did have an Earthquake on Saturday.
Dick
You are certainly not alone being concerned about this. This key man dependency is a big risk, its one of the issues mentioned in my http://www.Eusprig.org paper this year.
You make a very good point about moving the complexity rather than removing it.
Its worth noting though that there are more and more independent consultants with Excel/VBA skills who can be called in urgently in a bus type scenario. Google is getting quite busy on those key words.
The alternative is to try and limit your solutions to technologies the client can maintain (as opposed to ‘think they can maintain’). Its a trade off, sometimes a few lines of VBA can rescue a spreadsheet monster, sometimes it makes things worse.
In my experience organisations that use lots of spreadsheets don’t seem too worried about this risk. That doesn’t mean as developers we should ignore it, it just means we don’t get much help resolving it, still worth highlighting to the powers that be though.
Cheers
Simon
Strangely this problem is not just Excel specific and probably get worse as you move into DB or custom applications.
There are more people around who can fix a spreadsheet app than a DB app and a custom app etc, So I prefer to cut the risk and stay with the most common platforms and one that new users / administrators are likely to be familiar with.
At the end of the day it is about documentation, education and company skill sets.
I try and train people in how the company and specifically it’s data flow works.
If people understand the business, the tools become a lot simpler and easier to fix especially when there broken.
I am also not scared in people having more skills than me and aspiring for my job, as that means redundancy and longevity.
Having said that I shifted jobs 3 months ago, Now just to pinch my old engineer.
Hui…
Business is always a balancing act between risk and action. Only very large businesses (and those under the SOX yolk) tend to truly manage risk. Think of all the small businesses that would collapse if the owner got hit by that killer-bus. It tends to be only when a business has reached a mature stage of growth that the “luxuries” of risk management, key-person insurance etc. are addressed and just like people, some businesses never manage to mature!
Go with the flow, is what I say.
Interesting,
If I got ran down by a bus, I really wouldn’t care if a SS I wrote worked or not! – well, unless it was used as some sort of bus guidance system, but that seems unlikely!
Having said that I wrote some shocking SS when I started out, they did become important, I don’t know what’s happened to them now – I know I wouldn’t want to go back to them!
I finish with my current employer this Friday; I have just finished debugging a SS app that will control a prize draw process the company run. Now, it’s quite robust and flexible but when the process changes, they will have to update the system, – I made sure I put plenty of links to my web site and e-mail all over the app and the documentation. I hope they will come back to me and I can charge them for updates or upgrades as I like to call them :-)
So in a way the business’s lack of forward thinking can be to our benefit – as Simon says*
*Simon Says?!
I guess I qualify as an enthusiastic amateur as well.
I am just finishing the replacement for a system that ran using Rexx.
Classic case of having a system that did the job very well, was very neat, but no-one was able to amend it when any changes were made to file formats etc. So yours truly has come up with a replacement using just VBA and Excel.
I followed the principle of keeping the input very simple and in 1 place, and then having any complex processing hidden from the user. I always try to keep the code as simple as is reasonably possible for the following reasons:
1. So that other people of differing skill levels can understand it
2. So that I can understand it if I have to go back to it at a later date
3. Its easier to document
The “system” is covered by comments in the code, a technical guide and an admin guide. This will be sufficient for anyone with a reasonable degree of VBA experience to understand and change if, and when it might be required. For me the key thing is not to make it too complex – the simpler it is, the cheaper it is to maintain / update (mind you I’m nowhere near as technically competent as most of the people on this forum).
By the way, many thanks for the site – I find it invaluable.
DickK >> Am I the only who thinks about this stuff?
JamieC > No way. A significant part of my job (technical lead) is to think (read: agonize) about such matters. Port/convert or re-write/re-engineer? If port/convert how much resource to identify/purchase a suitable third party tool? If re-write, how much resource to identify/isolate existing product (code etc) that could be salvaged? Functional equivalence (warts and all) or allow bug fixes/new features along the way?
Hi
Yes this can be a problem. I am an independent consultant, who sets up systems for a range of SME clients, using worksheet functions and VBA code where required.
One client, last year, had a system in place to take accounting data from Sage to produce management reports for 14 costcentres, with numerous departments. It was horrendous, using 16 workbooks, each with at least 30 sheets, with manual cutting and pasting of data all over the place, unhiding columns to permit the next month’s data to be visible, copying the current month’s data into the comparison column to compare with budget and so on. It took them at least half a day to carry out the task and it was fraught with danger of wrong data being pasted.
I wrote a small amount of VBA code to extract the output from Sage to a format where a Pivot Table was easily able to generate all the required figures, and then pulled that data into their reporting format along with comparisons.
They then appointed a new accountant, who claimed(?) to have Excel skills, who immediately said he wanted to stick with the old system because he could understand it and fix it if it went wrong, and would not have to rely upon outside help.
I pointed out to him, that even though he doesn’t fully understand the workings of the modern internal combustion engine, he doesn’t still travel to work by horse and cart!!
If one doesn’t embrace change, even with some added complexity then one stagnates.
As Simon M said, there are plenty of people for hire to fix things if that darned bus does come along. From a company viewpoint, the question is do you pay for that extra employee to sit alongside you to know the system (but don’t let them ever travel in the same vehicle at the same time!!), which is effectively the insurance premium, or do you just take the hit of the cost of bringing in a consultant, IF and when that nasty bus comes along?
I’ve had exactly this problem several times, and I used to solve it by writing lots of code, which creates keyman risk. These days, I try to keep the code extremely simple, using it just to automate or retrieve data in a standard way, with all the smarts and business logic out on the worksheet where the main users can modify them. This makes the code easily maintained. Clearly, this won’t work on really big systems, but it has great for small to medium size projects.
In one place where I used to work, the succession plan was basically to put that person’s work onto CDs and forget where you put them. We were understaffed, and people ended up working on what they had an aptitude for and an interest in, not what was necessarily the right thing to do. (Note: we were not accountants doing corporate financial stuff.)
In another place, they’d lay off someone, then stop a minute and wonder who else could do that job. So they’d grab someone from another function to fill it. Then they’d stop a minute and wonder who else could do THAT job. Ad infinitum. This process led me to doing about three people’s jobs over a span of two years, none the one I was originally hired to do. I don’t know who could have taken over my job(s) when I was laid off, because there was nobody in the chain with anything approaching the right background.
Now I build relatively small independent utilities (for the most part) which could be readily debugged, improved upon, or replaced by myself or most any competent Excel/VBA developer. Some of the things I’m working on are huge. A couple are relatively robust because there are several of us familiar with the functionality if not the actual programming. At least one more is fragile because it started life as a monster workbook loaded with inefficient and undocumented recorded macros created by an avid but amateur employee; I have only slowly been able to swap in structured worksheets and working code for spaghetti. If I disappear before the project is done, it will probably die.
I always worry about this problem and have taken jobs at places that were knee deep in antiquated spreadsheets. One big mess at a company I know you’ve heard of required setting up an Access database to hold data. I then built using VBA a way to distribute data into peoples spreadsheets and they added their own calculations. I was happy with the result, but it was slow for the others to change their way of thinking. At least I knew we were all using the same data. The database was also automatically updated with general ledger data as well as imputed statistics. I took this approach because the old sheets were left by employee’s that left and were their work papers. The problem was nobody could figure out how it all worked including me. I spent most of my time rebuilding the old data. My general approach is to quarantine the old spreadsheets and build better ones on a go forward basis.
I’m too much of an amateur to worry about leaving complex spreadsheets/VBA behind. However, for a couple of years I have thought that my firm could benefit from some add-ins that I could develop, but then what happens if I get run over by a dump truck? (I live in Pittsburgh, where the public transit system just cut bus service, so I figure I have a greater chance of getting hit by a dump truck). Sadly, it seems that no one in my firm has reached my level of Excel knowledge/skills, no matter how rudimentary they may seem compared to a professional developer, so I don’t even bring it up.
Simple rules
1. If you’re the only person who knows KludgeScript in your company or organization, don’t use it to write anything for use by others.
2. User guides aren’t implementation documentation. The latter takes much longer to write (if done properly) than the former.
[I’ve written one of the latter, and I managed that because at the time I had a boss who understood that I needed to do so. The user guide took 2 weeks to write. The implementation document took 6 weeks to write (both sprinkled with other tasks).]
3. If you inherit large code bases (in this case spreadsheet models), presumably someone else in the company/organization knows how to write this sort of code (unless you’re the replacement for the guy who got hit by the bus – that’s addressed in #4). Find out who that is and get him/her to review your coding, i.e., make sure there’s someone else who has some understanding of what your code does.
4. If you’re replacing the guy who got hit by the bus, ask your boss whether s/he wants you to be the only person working on the code base, and if not to name someone else to act as your coding reviewer (see #3).
[FWIW, I had to teach a co-worker awk before I left my 2nd job, in which I’d written several awk scripts to parse files that were nothing more than ASCII dumps of mainframe printer jobs, which was all that that MIS/DP department was willing to provide at the time. Fortunately, the other guy knew how to write mainframe FORTRAN and CLISTs, so I wasn’t teaching programming fundamentals. Still, this drains time and can be frustrating, but it’s necessary for relatively painless maintenance.]
In my current role (something of an inhouse BA-come-coder) I’ve generated numerous Excel-based applications that are routinely used by (in some cases) hundreds of users daily. Such ‘rapid solutions’, when they meet an immediate business need well, tend to remain entrenched within the organisation for a long time. A rapid solution that meets business needs and has a long lifespan is typically viewed as successful, and quickly become viewed as ‘core’ business systems/processes. All seems to be good in the world until IT and Risk begin sniffing around…
The problem that many businesses are unable to adequately tackle is the old Butterfly Effect or as I term it in these parts, the “(V-B)utterfly Effect”: solutions don’t exist in isolation, and for every issues resolved, 10 new problems may be created. It is the fear of such problem-solution-more problem type chains of events that lead to something of solution paralysis, which funnily enough, typically lead to stakeholders seeking a (VBA-based) ‘rapid solution’ in the first place. In my experience, few organisations are adept at achieving a good balance between meeting short-term business needs and long-term risk management.
I got hit by a bus today, and already my workplace has turned into a hub for leadership and productivity.
I used to worry about it but I think we all have an inflated sense of worth (my self included). The reality is that the organisation will continue and someone else will hash together a quick Excel file (because it is easy to do that without the worries of Normalisation and foreign key constraints etc) it will be wrong but it will be 95% there, and it will be much simpler than the one that was lost under the Bus. No man (or woman) is an Island. Life goes on.
If you’re the only person who knows KludgeScript in your company or organization, don’t use it to write anything for use by others.
That sounds great, in theory. But in practice it always fails when KludgeScript is the only way to accomplish a particular task.
“…but it will be 95% there…”
Even if it’s only 50% there (more likely IMO), it will have come from a computer, so it will be widely believed and worshipped.
“If you’re the only person who knows KludgeScript in your company…”
I’m the only one in my company that knows a lot of things. My clients seek me out because I know Technology X and they don’t, but they think or I’ve convinced them that it will help them. If I get hit by the bus or dump truck, they’ll use Google to find another programmer, same way they found me.
If a given task could be accomplished by KludgeScript, it should also be possible to accomplish that task MANUALLY. Up to management to set development policy that could be affected by key employees getting hit by buses, etc. If maintenance is too unpredictable, the manual approach wins. The benefit of the manual approach is that it usually motivates more than one person to learn KludgeScript.
As for finding other programmers, I meant my comments to apply strictly to employees, not outside consultants.
As someone who’s held a dozen jobs in less than a dozen years, from a 20,000+ employee investment bank to a 5 person CPA firm, I think I am qualified to make the following statement: Management couldn’t care less about improving accuracy, efficiency, streamlining, or anything that remotely sounds like that. As long as the job gets done in a way that doesn’t make them look bad, nothing else matters. So why bother? I’ve asked myself a million times. There is one class of people who care, and they are the end users.
What I’ve learned is that the only person to develop for when you are an employee (not a consultant, which is obviously a different scenario) is yourself. I create tools and templates and code and databases and standalone apps to make my job and my life easier. If I can’t justify the time investment with a return in time savings or education gained then it’s not worth it. But I will never justify it by saving anyone else time. I will demonstrate my tools for interested people and will share and train them to use my tools if they so desire, but they have to want it and they have to want to know how it all works. From there on out, if I am no longer around to do what I do, it is their choice to try to maintain it on their own or to go back to the old way. My hope is that they are so intrigued by the “new way” of doing things that they’ll want to learn for themselves. That, or they’ll call me back in as a consultant. In the end, what else can you do besides leave code liberally commented with your contact info and maybe a link to a Google search for “Excel consultant”?
For what it’s worth, my results are as follows:
Job A: Still uses my templates. In fact they even use one template I put together but never gave to anyone, they just found it on my shared drive and decided it was useful.
Job B: None
Job C: 3 years later and I am still maintaining and building new projects for them
Job D: Still use one simple text file parser and a Monarch model
Job E: Probably still use the spreadsheet but not the VBA
Job F: Had a 5 man department doing the job of 1. My “efficiencies” were not much appreciated.
Job G: Ported my database tables to SQL server but left the code behind
Job H: Remains to be seen
Dick,
It seems like you’re asking a multi level question.
You’ve moved from a world in which multiple users designed single person customized applications to one where a single developer designs multi user applications, using a spreadsheet front end/language.
If done well, your organization benefits because the inefficienies and mistakes in the former paradigm are driven out, at the cost of investment in your time and the need to maintain and periodically enhance a complex and sophisticated legacy application.
Apart from the straitforward question of who can maintain and extend your application, the next interesting question is when will organizations come to recognize that this is a potential trade off that they can make over and over again – in other words, starting down the path of bringing a discipline framework to the current Wild West of end user computing.
From what I can tell, hardly anyone is thinking about this problem holistically right now. Business opportunity, business opportunity…
I’ve got the opposite problem. I work for a huge company that won’t do anything that it can’t maintain … which means we rairly do anything. I personally have written some simple (I consider them tiny, but I’ve been a professional programmer in the past and have an advanced degree in CS) vba macros that save hundreds of hours of work. However, noone else is allowed to use them because that is not allowed by the corporate policy.
So, I think you are providing a service to your customers. I understand that maintainability is important but, small companies take these risks. That’s what makes working at a small company a fast paced and exciting endevor (one that I’m thinking of trying again). I say, comment your code, follow propper design principals, and leave breadcrumbs for the next guy. At the end of the day, however, there’s not much else that you can do.
Dixon,
Amazing. I guess your huge company is in the business of making money? And still they turn down, as a matter of policy, ideas that can save hundreds of hours of work. You must be frustrated as heck!
1 minute saved on a task X the number of people who have to perform it X how often the task needs doing. You’d be saving them years of wasted wages in the first month. But then I’m ranting to the already converted.
Why don’t they just employ a small number of staff who CAN maintain VBA macros? It shouldn’t take a 12 month study and a business case to demonstrate the logic.
I my work I delve into anything that can speed up what I do, Excel VBA, VBScript, batch files, combinations of these etc. Often I’m the only person to put the work into the development of them but a few colleagues get to use my gadgets (the developmnent hierachy I use goes: Macros -> Gadgets -> Tools -> Utilities -> Programs and I spend most time stumbling optimistically around in the Macros/Gadgets area (programmer I ain’t!!).
The corporate bugaboo about non-IT-approved programming usually doesn’t apply to individuals doing what they can for themselves only since those sorts of things usually don’t survive that individual changing jobs. Trouble begins when non-IT employees write bits of code that other people either in other departments or different grographical offices use. That’s the sort of stuff that can become heavily relied upon, and that’s what triggers regulatory compliance issues.
Most tasks that can be automated can also be done manually. Automation may save lots of time for the employees who need to perform those tasks, but IT is usually responsible for all types of automation. Unless IT departments view other departments as clients, they too often fall back on just saying NO.
Much of the time that’s unreasonable, but IT’s on firmer ground when there’s only one person who can maintain a particular piece of code. This is a reasonable rule-of-thumb: if only one person understands a piece of code, only that person gets to use it. Any piece of code that would ideally be used by many people needs to be understood and maintainable by at least two people.
Dick,
I always like to use the “If I won the lottery” scenario in preference to “hit by a bus”.
Seems more palatable in a tempting-fate kind of way.
Matt.
MattW – one of my colleagues takes this the other way and says “if you get hit by a bus and die…”