First of all, what are they? Macros are sets of coded instructions embedded in Microsoft Office documents, particularly Excel documents, that allow for repetitive tasks to be automated. They are usually triggered by visual controls like a button that is placed in the document by whoever programmed the instructions. Macros are written in a computing language called VBA (Visual Basic for Applications), which first appeared in the early 90’s to enable the creation of user-defined functions in Office products.
Now, the why. Have you ever had to type in formulas to compute business figures? Or click-and-dragged cells to propagate calculations across multiple rows? Or manually calculated fields entered in an Excel sheet? These tasks are common rudiments that happen the world over in small businesses everywhere. They are fine when they only have to be done once and then never again, but if they have to be repeated on a monthly, weekly, or even daily basis, then they get repetitive, time-consuming, and, worst of all, error prone.
Not every business is large enough to justify deploying an enterprise software arsenal to handle every tangible aspect of every workday. For many small shops, the Microsoft Office suite is chief among enterprise toolsets. Little known to most shops, however, is how powerful this set of tools truly can be when one knows how to leverage the power of VBA onto those tools. I wish I had compiled a personal estimate log of the number of man-hours I have saved the companies where I worked by implementing Excel macros that automated entire office workflows over the years. This goes without mentioning the dollars they saved avoiding the infinite potential of human error that often creeps into manual processes.
I’ve seen 300+ employee companies altogether forego ERPs in favor of the permanent services of two dedicated programmers who handled the internal creation and maintenance of macros that automated everything in the house like clockwork. Would I recommend it? Probably not, although it did work for them. So even if it doesn’t possess the out-of-the-box features that a full-fledged ERP can boast, it does get the work done in as customized a way as possible. After all, for smaller companies, there is often no need for the bloated excess of features large providers offer, and a small, tight, customized workflow can be more than enough to get by for a long while.
So would a VBA solution work for you? It depends on the type of business problems you solve on a repetitive basis. If, as mentioned above, you ever find yourself copying figures into Excel sheets, entering formulas and/or click-and-dragging cells regularly, then automation is probably your friend. I have seen many scenarios where a VBA hack saved several hours or even days of work.
The first job where I ever used macro automation was a security company specializing in software that controls doors, cameras, intercoms and other devices in high-risk facilities like prisons and courthouses. For facility security staff to use our software to, say, open and close doors or activate an intercom, we had to create a symbol for each door and intercom with a label like ‘door[1] ‘, ‘door[2]’, ‘door[3]’, etc. Some of these facilities had upwards of a thousand doors, so naturally, creating a label for each door could represent a major repetitive pain in the neck. You can either type it one by one into each door symbol, or you could be a smarty and use Excel to do some of the grunt work. You could write the first door name as “door[1” – without the closing bracket and then drag that cell down until you reach “door[1000”. You notice that we left the closing bracket out. That’s because if you don’t, Excel will not know to automatically increment the door number enclosed in the brackets. So you have to manually add the closing bracket “]” for each entry, which is still a pain. What to do? Macros to the rescue!
Once I learned to harness the power of VBA macros, I realized not only how lightning fast I could make my workflow, but how customizable the hacks I designed could get. For instance, to solve the problem outlined above, I would write a quick loop that would print “door[“, then print the next door number, and then print the closing bracket “]”. I would tell VBA to do this until it hit 1000, and I would have all my expressions in a column ready to copy and paste where they needed to go! Now each door had approximately 12 different label types, so with 1000 doors, you’re looking at the creation of 12,000 distinct labels printed in a single button click. Compound that with the possible complexity of having to execute calculations based on which door number you are dealing with for each door, and you have yourself an undeniable case for this type of automation. This. Saves. Time!
Sound like a narrow use case? I ended up using this skillset in every job I had subsequently in a variety of contexts. Data spreadsheets, language translation, greeting card headings, generating entire workbooks’ worth of data calculation based on a single spreadsheet, the works. So if you have any repetitive workflow that is error-prone but predictable, you’ve got yourself a VBA macro candidate!