Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Simon Hurst's practice IT casebook: Excess Excel

by
19th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

Excel is a fantastic tool. It's the Swiss Army penknife of the accountancy profession. Quick tax calculation - use Excel. Fixed asset lead schedule - use Excel. Expenses claim - use Excel. Small time-recording system - use Excel. Complex data analysis application - use Excel. What makes Excel so easy is the complete lack of any enforced structure.

Apart from a basic understanding of how to play battleships and a modest grasp of the arithmetic operators such as plus, minus, multiply and divide, you really need little knowledge to jump straight into a spreadsheet and start doing something useful. Of course, it is this very lack of structure that makes a spreadsheet so dangerous and error-prone.

Brilliant execution, wrong tool
Some tools are better for some jobs than others. Consider Madame La Guillotine and the aforementioned Swiss Army penknife, for example.

A client phoned me and asked if I would be happy to provide help and guidance to a new member of staff who was doing some work on an Excel spreadsheet project. In retrospect, I should have asked more questions at that point, rather than confidently assuring the client that it was all part of our normal support service. Over the next few months the member of staff phoned me several times with intelligent questions about Excel, but increasingly these questions started to concern the use of quite complex VBA (Visual Basic for Applications) programming.

Sadly, I still remember the frisson of excitement I felt when I first discovered Lotus 1-2-3 macros. Sensible use of macros in office applications can result in impressive efficiency gains. Excel uses VBA for its macro language and as a result, Excel macros can be anything from a recording of a few simple keystrokes or mouse operations, to a sophisticated VB program. There is little doubt that VBA can be used in Excel to handle some very complex applications. There is even less doubt that it is very easy to create poorly structured code that is prone to error and almost impossible for someone else to understand.

My client was comparatively lucky, their employee, whilst largely self-taught, did a pretty good job of structuring and - at least as importantly - documenting their code. However, what they ended up with after several months of effort, was a pretty impenetrable nest of interlinked Excel workbooks driven by pages of VBA code that no-one else in the organisation really understood. It was a tribute to the client's original design vision, and the intelligence of the employee, that the system actually seemed to work pretty well.

Just after completing the bulk of the project, the employee moved on to another job.

Lessons
This case highlights several issues. Most importantly, whilst the client ended up with a quite remarkable Excel application that, by-and-large, worked as intended, they could probably have achieved an equivalent result in a fraction of the time by using a database rather than a spreadsheet. Excel may be wonderfully flexible and brilliant at calculations, and analysing and presenting data, but it's not the ideal application for storing complicated sets of data, or controlling the input of that data. It takes a lot of effort to build structure into a spreadsheet.

On the other hand, databases are structured. To get anywhere with them you have to learn a bit about how databases work and how to design the system you are working on. This is often enough of a hurdle to send people rushing back to Excel, but often the time taken to force Excel to do something it feels uncomfortable with would have been much better spent on learning how to use a database.

On the positive side, this particular system had been well documented and the code was reasonably well constructed. This made it possible for the system to continue to be of use even without its author. However, the fact that using Excel meant that all processing had to be carried out in code, made it difficult to track down errors or modify the application in any way.

For many people and organisations, the greatest improvement in the way they use Excel is likely to come from learning how to use a database, and researching other applications that are already designed to do jobs that Excel has to be painfully cajoled into.

Related material

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.