Get proficient with function macros

Intro:
Hello, and welcome to the CPA Australia podcast, your weekly source for accounting, education, career and leadership discussion.

Neale Blackwood CPA:
The Excel Yourself, October article related to how you can use something called a user defined function in Excel. So user defined functions are functions that you can create in Excel's macro language. So Excel's macro language is called visual basics for applications or often shorten to VBA. And it's sort of a subset of macros. So macros typically automate processes, but you can create what's called a function in VBA and that function can be used on the spreadsheet. So just like a sum function, you can create your own function to do a calculation that you want to do. So it's sort of like a do it yourself function. So the reason you would create one sometimes in Excel, you might have to create a very long formula to achieve what you want. And long formulas tend to be hard to maintain, and in some cases hard to understand.

Neale Blackwood CPA:
And so what a user defined function can do is simplify the formula. So the abbreviation for a user defined function is a UDF. Now there are some UDF rules. So the first is you can't create a function. That's gonna change something on the sheet. Now, the main thing that people try to do with a function that won't work is they try and change a format. So you can't write a function that's going to change a format on the sheet. Excel just won't let you do it. It'll return an error. You can't change something on the sheet. A function has to return a value or text or something like that. It just has to return a result. It's not allowed to have changed what's on the spreadsheet. It has to be saved in a module. Now, they're hard to explain a module is just basically a repository of code that sits in the VBA window.

Neale Blackwood CPA:
So you can check out the companion video to see where the module sits, but all the UDFs have to be saved in the module, in the file that they work on. You can only use the UDF in that file that has that module that has the code. And also because it is associated with macros, you have to have macros enabled for the function to actually return a result. So there's three examples in the article and also in the video. So the first one is a username. So VBA has lots of extra functionality that you can't access on the spreadsheet. One of them is you can actually extract the username from the system. What you can do is you can create a UDF that will return the username. It's a pretty straightforward VBA command to do it. And it's just basically application dot username. So I'm not going to go through the sort of nuts and bolts of creating the UDFs, cause you can watch the video for that.

Neale Blackwood CPA:
So I just thought, I'd take you through some of the issues and some of the reasons why you use UDFs in Excel. So the ones in the article are the username. There's also what's called an is date function in VBA, but there isn't and is date function in Excel. Again, you can add that functionality by creating a UDF that accesses the VBA code. And again, it's just a one line piece of code in the function. So it's very simple to create. And the third example that I've given is a bit more complicated and it allows you to extract out of a text string, certain parts. Now it is sort of assuming some sort of code. So the example I've given is something like 1, 2, 3 dash 4, 5, 6 dash 7, 8, 9, and there might be three parts to the code and you want to split it up. Now in Excel, you can do that with a formula, but the formula gets very big.

Neale Blackwood CPA:
And also it's not scalable. So if you have a longer code, then it tends to be a much longer formula that's required, but in VBA, VBA has a split command. So with the split command, it will allow you to basically chop up a code based on what's called a dilemmata, which is sort of data speak for whatever the character is that's splitting up the pieces of code. And again, you can access that split command by creating a UDF that uses it. So the example in the article allows you to access each individual segment of the code, which could be quite useful especially when you've got codes that do have some built in structure to them. Now splitting code is possible with things like power query and text to columns. They're two other ways to split code, but they're not formula based. And so that's the advantage of using a formula to do the split is you have a little bit more control over it and you can use it a little bit easier and it automatically calculates.

Neale Blackwood CPA:
So there's no extra manual steps involved or refreshes involved in terms of power query. One thing to be aware of when you create a UDF, depending on what you're doing, sometimes you might create a UDF that is, let's say we want to add up all of the bold entries in a range and because that's a format, you can do it. But the problem is if you change the entries and make it bold that won't automatically get picked up in the calculation because unfortunately changing a format, doesn't trigger a recalculation, you'd have to press the F nine key to do that. Now there is an option to sort of force Excel, to calculate a UDF every time Excel calculates, and it's called application dot volatile. Now I put that in the video, but I didn't include it in the article.

Neale Blackwood CPA:
So application dot volatile, and that will force the UDF to calculate every time Excel calculates, which will impact the speed. Also UDFs tend to be slower than Excel's native functions. So just be aware of that. The application dot volatile still doesn't help for the issue of changing a format because formats do not trigger the calculation engine to recalculate. So if you're doing something with formats and calculations, then you got to remember to click the F nine key pretty much to make sure it updates. I've included a few bonus functions that weren't in the article that are in the companion file. And I covered them briefly in the video. So there's a UDF that will return the current sheet name. You can also find the current file name, so that will return that. And also you have the ability to get the sheet name from another sheet.

Neale Blackwood CPA:
It can be handy for things like an index sheet or a content sheet, and you need to get the names of all the sheets and you need it to be updated. So when the sheet name changes or the sheet tab name changes, it automatically updates, which that function does. So UDFs, they are fairly specialised. A lot of the ones, the examples here are reasonably straightforward and there's not a lot of code involved. So you may be surprised at how little code is required to do some of the things, especially if you are accessing a built in part of the VBA code. Now you may want to use your UDFs across multiple files. Now it's really easy. And I demonstrated it in the video that you can just copy one module from one file to another file in the VBA window.

Neale Blackwood CPA:
And you basically just drag it around and you'll copy it. Because you need to have the module in the file that you want to use the UDF. Also, you need to save any files so that they are either a binary file or a macro enabled file as well. So you can't have macros in the standard Excel file. Now, if you did want to have it more broadly available, you can create what's called an Excel add-in that has the functions. And then you instal your add-in and then when it's loaded, then you can actually use the UDF across multiple files. Basically any time you've got Excel open and the add-in is installed and operational, then those functions will work across the board in all of the files that you open. Obviously if you're sharing and things like that, you'd need to share the add in for people to get the UDFs to work as well.

Neale Blackwood CPA:
So, as I mentioned, UDFs, a fairly specialised area of VBA. It can help in some circumstances where you need certain Cal calculations that just aren't in Excel, you can create your own calculations and basically in a standard one, you save it in a module within the file. And so it's like a self-contained model. And so it will work as long as people enable the macros. That's the important thing, enabling the macros. Thanks for listening. I hope you found UDFs interesting. Thanks a lot.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.CPAaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.