A genius once wrote a famous monologue and 400 years later it still stirs up emotions and thoughts. Not surprisingly, Excel macros do not have such a dramatic appeal but using or not macros in a workbook is a question worth asking. Including one in the workbook is decided on the fly without much of a consideration. Quite often macro is the gin coming out of a programmer's hands that could magically solve any problem. As a favorite TV presenter would exclaim "How hard could it be?". Macros are brilliant helping hand but should be applied with consideration.
You could do virtually everything with a macro
Theoretically speaking, macro could do everything you can do manually in Excel plus much more as automatic integration and using the functionality of other MS Office applications, accessing Windows functionality, etc. and even more through plug-ins and ad-ons. Macros are indispensable for automation of simple operations sequences. The basic macro functionality is easy to use and not any programming skills required. I cannot explain why it is considered as an advanced skill as it could be acquired in an hour. Macros are even more valuable in aiding more complex tasks related to operations with data, formatting, calculations, external applications, etc. For example, updating a PowerPoint presentation, processing attachments in Outlook emails, importing and processing large data sets from several sources, manipulating charts and views for reporting purposes, etc - the examples are numerous. The only limitation are the available skills, time and reliable sources for technical resources and last but not least - the imagination of the developer. In essence one can develop a full-scaled software solution using VBA and Excel functionality.
... but
Here comes the inevitable "but"-paragraph. There are two main criteria when deciding for a macro - cost and reliability.
- Cost: the more complex the macros get the higher the cost as it would require more time to develop and test obviously. The cost of development should be weighted against doing the task manually or semi-manually as some features could take extensive efforts to build while combining simpler macros with some manual input could be faster and more reliable. Not everything should be automated. Some cases do not allow manual operations and full automation is necessary but these are rare so we should ask if it would not be faster and more reliable with some manual input. Some one-off tasks are much faster performed by the user and developing and testing the code would take much longer. Of course, you never know what task is one-off but this a risk that could be taken. Adding one feature after another is appealing as it is much better something else to do a job we hate but after a certain point adding automation brings more problems than solves. There is a line where macro development is getting more expensive. Knowing where this line is unfortunately comes with experience and many hours spent in fixing our own mistakes.
- Reliability: Excel is not a software development environment. Even purpose-built software development platforms fail so how much you can trust something that is not designed for that? Software development requires a application of quality assurance methodologies and tests that cannot be applied to VBA code in Excel and the developer has to provide it himself. As we know, relying on humans is not a good idea for these matters. There are guidelines and methods to provide more robust VBA code but they are usually not enough. Developing complex macros that will be used by many users should account for many factors such as Excel version and setup, security issues, software environment and many others. Even VBA code for a specific version and setup could be tricky when the user base is more than 2 users. All that is not impossible but it comes at the expense of the development time and time spent for support. Macros also are a black-box for the end user and she cannot see what and why is happening in the process. This is not the usual case but adjusting an someone else's macro to meet new requirements is difficult and even impossible when the user does not have the skill.
Cannot replace the good workbook design
Macros could be of a great help but they could not substitute a good workbook design. Some see macros as a panacea to solve all their problems with the spreadsheet but it is not. Usually these are people who have heard that there is a thing like macro and have not developed one themselves. Well thought spreadsheets as a rule require relatively simple VBA if macros are required at all. When it comes to macros the first question should be "Can it be done without a macro?". If not, then we should carefully analyze the level of automation to introduce and then be even more careful in writing the macros. There are many source that provide guidelines for a good VBA code.
No comments:
Post a Comment