To Macro in Excel or not

Excel Macros are perhaps the easiest way to automate the boring task at work, be it the mundane report that has to be sent to the VP each Thursday or that dreadful number crunching exercise which cannot be done using excel formulae alone. That being said, I have seen a fair amount of cases where a piece of VBA code is used ,just because it comes in handy for a complex application.

               So where does a macro fit in and where do more complex solutions fit in? Every guy in IT will have his own take on it. If you are in India, every other guy you encounter in your long journey home will be an IT Project Manager and he will have his own take on it. I don’t profess to be an expert in making that decision, but having spent the bulk of my time writing macros to automate small tasks, here is my two cents on it.

When macro?

·        When you have to generate a report for which you can bring in data from any source into Excel, which has to be generated in regular intervals, say every week. This can be a real time saver and probably I will say the best use case for the macro.

·        When you have an unrealistic timeline for doing the same repeated work for say 10000 times and have to submit it by lunch the next day. And you cannot construct a simple excel formula to do the number crunching. You business partner will be impressed by your speed of crunching of what he/she thought would take forever.

·        When you need a simple front end for your code in a hackathon and your piece of code has an API which can feed into excel. What Excel offers is a ready made front end with basic calculations in the form of formulae which is hard to replicate in any other front end solution like html or java front ends.

·        When you have a series of calculations made in a spreadsheet, but the method of calculation needs to be hidden from the person to whom you are going to show it. In this case it would be better to protect the worksheet cells which you feel might give out your secret.

When can a macro come back to haunt you?

·        When you are just the coder of the macro, but not the executor. Murphy can come to haunt you badly, especially in the ad-hoc macro which has been coded for a small task in half hour by you. The situation can go haywire if you are going to run the macro in some other computer other than the one coded.

·        While you might think what has been described in the first point is bad, things can worse when your computer has got Excel 2016 and your client’s system has got Excel 2007 running it. Excel certainly is not fully backward compatible, more so in the case of VBA code.

·        When you want do an online download of data from a RDBMS like Oracle. The API can break at any time without giving any warning and fixing it can be a tedious task.

·        When Excel is to connect and pull data from other office applications like word or powerpoint. While Excel can connect and pull data, maintainability of the VBA code is such a big headache that someday , you would regret having written the piece of code in the first place.

The author’s views are personal .