Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood and in this episode, I want to talk about macros. Now, in the companion article, I've shared six useful short macros, which I'll get to. But before we dive into macros, I just want to explain that macros are not dead. If you've been reading social media, you might see posts that macros aren't needed anymore.
Well, that's not true. Now, Power Query, which is the best practice way to import data into Excel has replaced a lot of macros. Macros do so much more than just import data. That was only one of the things you could do. Also, Power Query has an Achilles heel, which I'll talk about a little bit later.
So, there's six useful macros that I share. Macros, by the way, are written in the language Visual Basic for applications, which is always abbreviated to VBA. And they can run in the background. You can click a button and basically a task can be performed.
Now, the macros that I've shared can be adapted to do other things as well. So, it is worth checking out the companion video where I go into a lot more detail about the macros that I share and also discuss the code that I use so you can see how that can be adapted for other things. Now, before we dive in, we also need to go with the macro warning.
When you run a macro, it clears the undo list. So, that means you can't undo the macro and you can't undo anything you did before you ran the macro. So, in general, it's a good idea to save your file, run the macro. If it's worked, great. If it doesn't, then you can just close the file and not save it. Obviously, you can also try the macro on a copy of the file. The companion article has the companion video and the companion file.
So, the companion file has all of the macro code in it with descriptions. There's a separate sheet which has a copy of the macro code as an image as well as a section where you can practice and try out the macro.
Now AI, artificial intelligence, has made great claims about automating things, but macros have been automating things for 30 years. Also, AI works really well with macros, AI can write macro code. So, because VBA has been around for so long and AI has basically used the internet to learn things, it's pretty good writing VBA code. It's not perfect, but it is pretty good. So, you can use AI to help you edit, write, and even debug your VBA code.
Now, VBA is a great language to learn programming. It is old school, so just a heads-up there, but it is easy to pick up because it's really easy to read. So, some of the languages can be difficult to read because they use abbreviations.
They also tend to use a lot of different brackets for things which can make things confusing. Python, for example, uses indentation to control how the code runs. So, if you get the indentation wrong, the code won't work properly. VBA is a lot simpler than that. And as I mentioned, it's easy to read. So, if you wanted to dip your toe into programming, VBA is a great language to learn with.
Also, it uses Excel, which as accountants, we tend to know Excel fairly well. Now, the macros that I share work on unprotected sheets. If your sheet does have protection, these macros would probably generate an error. Okay, so let's work through and I'll just briefly describe the six macros.
So, there's an unhide all sheets macro. So, this goes through and unhides every single sheet in the file. There's also a yesterday macro. So, there is a keyboard shortcut to enter today's date and that's control and the semicolon, but there's no shortcut to enter yesterday's date.
And I was finding that I was entering yesterday fairly regularly, whether I was doing a time sheet, whether I was doing an invoice, I wanted to enter yesterday's date. So, I wrote a macro to do it and check out the video and you'll see how easy that is. There is a piece of code that is shared by a lot of these macros.
It identifies if a range is selected, which is a really handy piece of code to use because if a range isn't selected, then you can actually stop the macro. And so, again, check out the video to see that. The next macro was entering the red brackets for negatives as a format. So, it's a fairly common practice to use either red brackets or just black brackets to represent negative numbers, but it's not a standard format in Excel.
So, this macro adds it to the custom number formats and once it's there, then you can just keep using it. The next three macros that I share actually do something that Power Query can't do. Now, Power Query is great and I highly recommend you learn it, but it has an Achilles heel.
It can't fix data in situ. So, if you've got a 10,000-row table and you need to fix a few things, you have to create a separate table to do that with Power Query. Now with VBA, you can actually fix the data where it is. And so, these three macros have got examples of that. They do some data cleansing that you could do in Power Query, but the beauty with VBA is that you can do the data cleanse where the data is so you don't need a separate data set.
The first one was deleting any blank rows in the data. The secret with that is starting at the bottom and then deleting the rows as you go up the list. The next macro populated blank cells with a zero.
Now, you may have created a pivot table in the past and instead of defaulting to summing the values, it counted the values. Now, a common reason for that is blank cells in the value column. Now, pivot tables have improved recently, so they don't always do it, but if your data defaults to a count, you might find that you've got a lot of blanks in the column.
So, this macro populates all of the blank cells in a range that you select with a zero and that gets around the problem. And the last macro fixes any leading and trailing spaces. When you import data or copy data from other systems, oftentimes there will be leading and trailing spaces.
And in most cases, you want to remove those to get the most out of your data. Now the file that I shared has a module in it with all of the macro code. Now, if you wanted to use this on a regular basis, what you can do is copy that module into the personal macro workbook. Now, the personal macro workbook is a special system generated workbook that is saved to the Excel start folder and it opens every time Excel does.
The easy way to create the personal macro workbook is just to record a macro, save the macro to the personal macro workbook. Always remember to stop recording the macro and that's it. Excel does the rest. It creates the workbook. You just need to save it when you exit Excel.
It's as simple as dragging the module from the file that I've shared into the personal macro workbook. So, that will give you access to the macros whenever Excel is open. Something else you might want to consider is running the macros off an icon on the quick access toolbar. That's how I run the macros and the video goes into an explanation of how you do that. So, macros are not dead.
If anything, AI makes them more accessible. So, you can ask AI to write the macro for you. Though having said that, always test your AI generated macros because they're not always correct. A lot of times they are, so they can save you a lot of effort. Check out the companion article.
On the bottom of the article, there's also links to other macro topics that I've covered in the past. Okay. As I said, macros are not dead and I hope that the six macros that I've shared can give you some ideas for other ways that you can speed up your work. Thanks for listening.
Jackie 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.
