Search Not Just Numbers

Tuesday 16 December 2008

Watch a macro doesn't hide a problem

When I tell clients that I can automate complex reporting in Excel so that it involves only a couple of clicks, they invariably assume I am going to use macros. Truth is, I hardly ever do.

Macros have their uses, i.e. to automate repetitive tasks, however in most cases the requirement for the repetitive tasks can be removed by better designing the spreadsheet.

If you can do what you want without macros, the spreadsheet tends to be more intuitive, easier to develop further and more flexible. Once a macro is used, users tend to be frightened to change anything in case the macro stops working.

Use of Pivot Tables in particular can automate a great number of reporting tasks far more elegantly than macros.

So, take a look at any spreadsheets that you use that incorporate macros and consider whether there is a better way.

No comments:

Post a Comment