Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals. Brought to you by CPA Australia.
Neale Blackwood:
Why macros aren't dead:
Welcome to Excel Tips. My name is Neale Blackwood and in this episode we're going to look at three macros that prove macros are not dead in Excel.
There's been many social media posts over the years that have claimed Excel macros are redundant and macros are dead. As with many social media posts, there's an ounce of truth behind the statement, but a tonne of evidence has been ignored.
Power Query, which I've covered in previous podcasts and articles, and I use it all the time, that has taken over a lot of data-related macro tasks.
I used to spend hours writing macros to handle data that now can be done by Power Query in a few mouse clicks, but that only relates to data. That's all Power Query can handle.
Macros can automate the whole of Excel. Data importation was just a part of what macros were used for, and that part to some extent is redundant, but the rest is alive and kicking. To misquote Mark Twain, "rumours of macro's death have been greatly exaggerated." So, in this month's article, I looked at three useful macros.
Macro warning!
Before I start though, I need to go through a macro warning. Macros in Excel clear the undo list. So when you run a macro, the undo list is cleared, so you can't undo anything the macro did. Also, you can't undo anything you did before the macro was run. So when you're using macros, it's a good idea to save the file before you run the macro. And then if the macro doesn't work as expected, you can close the file without saving. Another option is to just use a copy of the file. So the F12 key is the save as shortcut, so you can save it as another name. Try it on the other file. If it works, great. If it doesn't, you can just get rid of it.
The problems macros solve:
The three macros that I've covered in the article and the companion video, which I recommend you watch, because they go into a lot more detail, they cover three areas that macros can save you time, and get rid of a lot of frustrations. First frustration is with pivot tables. When you create a pivot table, there is no formatting applied to the pivot table. So, one of the first things you usually do is you format the numbers. Also, the headings in pivot tables can be a little bit below average when it comes to presentation purposes, for example, having the word sumof in the column headings.
Macro 1 - editing column headings in pivot tables:
What a lot of people don't realise is that you can actually edit the column headings in a pivot table. You can't use the same name as a column in your data. So, what you do is you add a space to the end of that name, and that will display correctly. If you try and use the same name as a column name, Excel will display an error message, but as I mentioned, just add a space to the end of that name and Excel will allow that to go through, and that's what the macro does. So when we run the macro, it removes all the sum of from the headings, and at the same time, it formats the values to the comma and no decimals format, and that saves a lot of frustration with pivot tables.
Macro 2 - macro to format spill ranges:
The second macro that we covered was a macro that handled a formatting issue with spill ranges. Now, spill ranges are part of dynamic arrays, which I've covered a lot in recent podcasts, and that's a brand new feature in Excel, and macros have been updated to handle that feature. One of the problems with spill ranges is that the formulas spill down and across, or they can spill down and across, but the format doesn't.
So, the format of that top left cell where the formula is, the format doesn't spill to match the formula, and so that's what I wrote a macro to do. Basically, you select any cell within the spill range, you run the macro, and whatever the format of the top left cell is, is applied to the whole spill range. If you do modify the spill range, if it expands, then you will need to run the macro again to format at the extended range. Again, that's an example of how macros have been adapted in recent times to handle the new features that are in Excel.
Macro 3 - building an index sheet in excel:
Now, the third macro creates an index sheet in a file. So, index sheets are really useful when you do have a lot of sheets in a file, because you can get a list with a hyperlink that allows you to navigate to and from the sheets very easily. And so, what this macro does is it creates a brand new sheet. Now, I've used a naming convention here. I've put an underscore, followed by the capital index, followed by another underscore.
My theory was that no one's going to call a sheet that name, and so I can create that brand new sheet, and then I can insert the list of sheet names with hyperlinks. There's actually two lists of names. There's a list of names in alphabetical order, as well as the list of the sheet names in the order that they appear in the file. This macro and the other two actually have interaction with the user. Now, recorded macros are really good to start with, but recorded macros, which Excel creates for you, have a lot of limitations, and one of those is that they can't interact with the user.
Macro message boxes and examples overview:
So, all three of these macros use message boxes, so they can actually ask the user a question or tell the user something. So, in the case of the pivot table and the spill range, when you run the macro, you must be in the spill range or in the pivot table. If you're not, a message box displays and suggests that you do select a cell in the spill range or the pivot table. And in the case of the index sheet, if you already have an index sheet in the file, so you're running the macro a second time, then the macro does confirm that you want to overwrite the existing entries in there as well. And again, that's what you can do when you write macros. Recorded macros don't allow that user interaction. And there is an article that I wrote a few years back on message boxes, so you can check out INTHEBLACK website to track that down.
Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
Neale Blackwood:
So, macros can cut down a lot of manual processes. So, any time you're doing something manually and you're doing it over and over again, that's a good use case for a macro. My rule of thumb tends to be about 30 minutes. So, if some process is taking you longer than 30 minutes, there is either a better way to do it, which involves existing Excel features, or you could write a macro that could assist. Now, you might not be able to get a macro that does everything, but it might be a bit like the 80/20 rule. You might be able to get a macro that can do 80% of what you want and you can complete the other 20% and still save quite a lot of time. So, macros are still a great productivity tool. There's lots of resources on the internet. ChatGPT can even write macro code for you. So, macros are still alive and kicking and saving lots of time and effort from many people around the world. Thanks for listening.
Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode, and we hope you can join us next time for another episode of Excel Tips.
