Beyond Macros — Automating Your Spreadsheet Workload
One of the more powerful, but seldom used functions of Excel is the ability to create automated tasks and custom logic within macros. Macros provide an ideal way to save time on predictable, repetitive tasks as well as standardise document formats — many times without having to write a single line of code.
What is a Macro?
If you work in financial or real estate industry, you should already be familiar with macros. A Microsoft Office Macro (as this functionality applies to several of the MS Office Applications, not only available in Excel suite) is simply Visual Basic for Applications (VBA) code saved inside a document. For a comparable analogy, think of a document as HTML and a macro as Javascript. In much of the same way that Javascript can manipulate HTML on a webpage, a macro can manipulate a document.
Macros are incredibly powerful and can do pretty much anything your imagination can conjure. As an example list of functions you can do with a macro:
- Apply style and formatting
- Manipulate data and text
- Communicate with data sources (database, text files, etc.)
- Create entirely new documents
- Any combination, in any order, of any of the above
Across every department in your organisation, one tool is ubiquitous — the spreadsheet. Basic Excel functions are easy for any user to understand, and many of your business applications will be able to work with Excel. But spreadsheets also generate a lot of manual work, which only gets more tedious as a business or project scales. Whether you are entering a list of customers into a database, compiling a daily report, running efficiency or financial analysis, you’ve probably discovered how useful — and how painful — Excel and other spreadsheets can be. In today’s Untrite Insights series we’ll try to show you on one example how you can automate data with macro.
Creating a Macro: An Explanation by Example
We start with your garden variety CSV file. Nothing special here, just a 10×20 set of numbers between 0 and 100 with both a row and column header. Our goal is to produce a well formatted, presentable data sheet which includes summary totals for each row.
As we stated above, a macro is Visual Basic for Applications code, but one of the nice things about spreadsheets like Excel is you can create/record them with zero coding required — as we will do here.
To create a macro, go to View > Macros > Record Macro. Then assign the macro a name (no spaces) and click OK.
Once you’ve done this, all of your actions are recorded — every cell colour change, scroll action, window resize, you name it. There are a couple of places which indicate Excel is record mode. One is by viewing the Macro menu and noting that Stop Recording has replaced the option for Record Macro.
The other is in the bottom right corner. The stop icon indicates it is in macro mode and pressing here will stop the recording.
Now that we are recording our macro, let’s apply our summary calculations. First add the headers at the Value header row.
Next, apply the appropriate formulas (respectively):
- =SUM(B2:K2)
- =AVERAGE(B2:K2)
- =MIN(B2:K2)
- =MAX(B2:K2)
- =MEDIAN(B2:K2)
Now, replicate calculations for each row by dragging the values and scrolling them down.
Now, we want to get the summary data for the entire spreadsheet, so we let’s apply a few more calculations respectively:
- =SUM(L2:L21)
- =AVERAGE(B2:K21)
//This has to be calculated across all data because the average of the row averages does not necessarily equal the average of all the values.// - =MIN(N2:N21)
- =MAX(O2:O21)
- =MEDIAN(B2:K21)
//This is calculated across all data for the same reason as above.//
Now that the calculations are done, we will apply the style and formatting. First apply general number formatting across all the cells by doing a Select All (either Ctrl + A or click the cell between the row and column headers) and select the “Comma Style” icon under the Home menu.
We could go even further and play with formats, colours and so on but let’s skip that. Our spreadsheet data should look like this:
Since we are satisfied with the results, stop the recording of the macro.
Congratulations, you have just created your first Excel macro. Now, in order to use our newly recorded macro, we have to save our Excel Workbook in a macro enabled file format. However, before we do that, we first need to clear all the existing data so that it is not embedded in our template (the idea being every time we use this template, we will import the most up-to-date data).
To do this, select all cells (CTRL + A) and delete them.
With the data now cleared (but the macros still included and hidden in the Excel file), we want to save the file as a macro enabled template (XLTM) file.
Remember that if you save this as a standard template (XLTX) file then macros will not be able to be run from it. Alternately, you can save the file as a legacy template (XLT) file, which will allow macros to be run.
Once you have saved the file as a template, go ahead and close Excel.
How to use Excel Macro
Before covering how we can apply this newly recorded macro, it is important to cover a few points about macros in general:
- Macros can be malicious.
- See the point above.
Why is that? VBA code is actually quite powerful and can manipulate files outside of the scope of the current document. For example, a macro could alter or delete random files in your My Documents folder. As such, you should make sure you only run macros from trusted sources.
To put our data format macro to use, open the Excel Template file which was created above. When you do this, assuming you have standard security settings enabled, you will see a warning across the top of the workbook which says that macros are disabled. Because we trust a macro created by ourselves, click the ‘Enable Content’ button.
Import the latest data set from a CSV (this is the source the worksheet used to create our macro). You may have to set a few options in order for Excel to interpret it correctly (e.g. delimiter, headers present, etc.).
Once our data is imported, simply go to the Macros menu (under the View tab) and select View Macros. In the resulting dialog box, we see the macro file we recorded above. Select it and click Run. Once running, you may see the cursor jump around for a few moments, but as it does you will see the data being manipulated exactly as we recorded it. When all is said and done, it should look just like our original — except with different data.
Beyond Macro Magic
Macro is driven by Visual Basic for Applications (VBA) code, therefore, when you “record” a macro, Excel is actually translating everything you do into its respective VBA instructions. To put it simply — you don’t have to write any code because Excel is writing the code for you.
To view the code that makes our macro run, from the Macros dialog click the Edit button.
The window that opens displays the source code that was recorded from our actions when creating the macro. If you are more technical, you can modify this code or even create new macros entirely inside of the code window. While the recording action used in this article will likely fit most basic needs, more highly customised actions or conditional actions would require you to write the source code.
Conclusion
The macro recorder built in spreadsheet software such as Excel is a good way to begin writing your own data automation code, but it falls a little short when you require more complex action. With some technical help, you can make your macros code more efficient, easier to read, and even run faster.
And you? How is your spreadsheet hell going? :)
