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.
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:
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.
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:
Custom Function Limitations:
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 :)
Table of contents