An Introduction to Office Macros
If there is one thing computers are good for it’s performing repetitive tasks. But sometimes you may find that you are the one doing the repetition. Have you ever had to create a spreadsheet in Excel by manually copying values from several different databases? Or maybe you find yourself copying and pasting the output of an Excel spreadsheet into a monthly Word report? Doing this process manually once or twice is an inconvenience. Doing it repeatedly becomes a productivity killer. Those who have been in this situation have probably asked themselves weren’t computers invented to do this sort of work in the first place?
Macros provide a way to automate repetitive tasks in Office applications. In simple terms you can think of a macro as a piece of code that can perform a series of tasks that you would otherwise have to manually perform yourself.
Here is an example that highlights the power of macros. An accountant, lets call him John, was required to produce a monthly report in Excel that displayed a breakdown of the wages being paid to the employees in his company. There were around 100 employees, and each of them could have money paid to them in over 50 different categories (standard wages, sick leave, holiday pay etc). To make matters worse, this data was spread out over 2 or 3 different databases. Creating this report took a full day of John’s time, and the process of manually copying data from a database into an Excel spreadsheet was error prone, leading to inaccurate reports.
The problems with this situation are clear: it’s a tedious job, the reports inevitably included errors, and the time it was taking to create the reports meant that it was quite an expensive operation.
By automating the manual task that John performed by way of a macro, a full day’s job could be done in a matter of minutes with a single click. Not only were the reports accurate (by eliminating human error), they were easy to produce and saved the company thousands of dollars in labour costs.
In Microsoft Office, macros are written in VBA (Visual Basic for Applications). Writing a macro from scratch does require programming knowledge, but anyone familiar with VB (Visual Basic) will find VBA is almost identical. But unlike creating an application in VB all the tools you need to start writing VBA are included with Office. Hitting ALT-F11 when Word or Excel are open will open up the VBA IDE (Integrated Development Environment) which includes everything you need to write VBA code.
The good news is that you can also get Office to record your actions into a macro, allowing you to automate Office without having to write a single line of code. For example, if you find yourself formatting a table the same way over and over, you can record this process into a macro and subsequently have the entire process performed with a single click. When recording a macro, Office will monitor your actions in the background and convert them into the equivalent VBA code for you.
Macros are an excellent way to automate repetitious procedures in Office, and they allow you to make your spreadsheet or document work for you (and not the other way around). If you dig deeper you will find that VBA, and therefore the macros that are written in it, is a complete programming language. As such, with the right knowledge, macros can also be used to create fully fledged applications that offer a deep integration with the Office suite.
