Custom UDF function vs VBA macros: advantages and drawbacks
We are continuing the series of tutorials about user defined functions. In our previous articles, we got acquainted with custom functions and learned how to create and use them. In this manual we will look at the specifics of using these functions and see the differences between UDFs and VBA macros.
In this tutorial, we will learn the following:
We hope this article will boost your knowledge of UDF and help you use them even more effectively in your Excel workbooks.
Is UDF and Macro the same thing?
Both user defined functions and VBA macros are created using the VBA editor. What is the difference between them and what to give preference to?
The most important difference is that the function performs the calculation, and the macro performs some action. A user defined function, like a regular Excel function, must be written in a cell. As a result of its execution, the cell returns some value. At the same time, it is impossible to change the values of other cells, as well as some properties of the current cell (in particular, formatting). However, you can use a custom function in conditional formatting formulas.
UDF and VBA macro work in different ways. For example, when you create a UDF in the Visual Basic Editor, you start with a statement Function and end with an End Function. When you record a macro, you start with a statement Sub and end with an End Sub.
Not all Visual Basic operators can be used to create UDFs. For this reason, a macro is a more versatile solution.
A macro does not require the user to pass any arguments (nor can it accept any arguments), unlike a user-defined function.
The point is that some commands of macros can use cell addresses or formatting elements (for example, color). If you move cells, add or remove rows and columns, change the format of cells, then you can easily "break" your macros. This is especially possible if you share your file with colleagues who do not know how your macros work.
For instance, you have a file with a perfectly working macro. This formula calculates the percentage of cell A1 to A4. Macro changes the color of these cells to yellow. A percentage format is set in the active cell.
If you or someone else decide to insert a new row, the macro will continue looking for the value in the A4 cell (the 4,1 parameter in your UDF), fail and return an error:
In this case, the error occurred due to division by zero (no value in a newly added row). In case the macro performs, let’s say, summation, then you will simply get a wrong result. But you won't know about it.
In contrast to macros, user defined functions cannot cause such an unpleasant situation.
Below you see the performance of the same calculations using a UDF. Here you can specify input cells anywhere in the worksheet and you will not face any unexpected issues when changing it.
I wrote the following formula in C3:
=UDF_vs_Macro(A1,A4)
Then I inserted a blank row, and the formula changed as you can see in the screenshot above.
Now we can move an input cell or a cell with a function anywhere. The result will always be correct.
An additional benefit of using UDFs is that they automatically update when the value in the input cell changes. When using macros, you must always ensure that all data is up to date.
Keeping this example in mind, I’d prefer using UDFs wherever possible and use macros only for other non-calculation activities.
Limitations and disadvantages of using UDF
I have already mentioned the advantages of UDF above. Long story short, it can perform calculations that are not possible with standard Excel functions. In addition, it can save and use long and complex formulas, turning them into a single function. And you won't have to write complicated formulas over and over again.
Now let's talk in more detail about the UDF’s shortcomings:
- Creating UDFs requires the use of VBA. There is no way around it. This means that the user cannot record the UDF in the same way as an Excel macro. You have to create the UDF yourself. However, you can copy and paste portions of the previously recorded macro code into your function. You just need to be aware of the limitations of custom functions.
- Another drawback of UDF is that like any other Excel function it can only return a single value or an array of values into a cell. It simply performs calculations, nothing more.
- If you want to share your workbook with your colleagues, be sure to save your UDFs in the same file. Otherwise, your custom functions won't work for them.
- Custom functions created with the VBA editor are slower than regular functions. This is especially noticeable in large tables. Unfortunately, VBA is a very slow programming language so far. Therefore, if you have a lot of data, try to use standard functions whenever possible, or create UDFs using the LAMBDA function.
Custom Function Limitations:
- UDFs are designed to perform calculations and return a value. They cannot be used in place of macros.
- They cannot change the contents of any other cells (only the active cell).
- Function names must follow certain rules. For example, you cannot use a name that matches a native Excel function name or a cell address, such as AB123.
- Your custom function cannot contain spaces in the name, but it can include the underscore character. However, the preferred method is to use capital letters at the beginning of each new word (for example, GetMaxBetween).
- A UDF cannot copy and paste cells to other areas of the worksheet.
- They cannot change the active worksheet.
- UDFs can't change the formatting in the active cell. If you want to change the formatting of a cell when displaying different values, you should use conditional formatting.
- They cannot open additional books.
- They cannot be used to run macros using Application.OnTime.
- A user-defined function cannot be created using the macro recorder.
- Functions do not appear in the Developer > Macros dialog.
- Your functions will appear in the dialog box (Insert > Function) and in the list of functions only if they are declared as Public (this is the default, unless otherwise noted).
- Any functions declared as Private will not appear in the feature list.
A quite slow operation, as well as some restrictions in use, may make you think: "What is the use of these custom functions?"
They can come in handy, and do if we are mindful of the constraints imposed on them. If you learn how to properly create and use UDFs, you can write your library of functions. This will greatly expand your ability to work with data in Excel.
As for me, custom functions are great time-savers. And what about you? Have you already tried creating your own UDF? Did you like it better than the basic Excel functions? Let’s discuss it in the Comments :)
