The range of formula within Excel is great. Most of the day to day functionality that you need is there but if you find that you need to do a different calculation that is not built in then you can create what is called a User Defined Function (UDF). If you are going this route beware you will need a basic knowledge of Visual Basic for Applications or maybe more than a basic knowledge depending on what you want to do.
As with all my articles we will start with a scenario. What I have below is a spreadsheet containing order details. Based on the total order amount we receive a sliding discount. If we order between 0$ and 49.99$ we receive no discount, between 50$ and 199.99$ we receive a 10% discount and between 200$ and 399.99$ is a 20% discount. If we order over 400$ of any product the order is considered invalid. This will add a little twist to make the UDF more interesting.
In order to create a UDF we need to add some VBA code to a module. To do this we need to be in the VBA environment and to get there we use the Visual Basic button on the Developer tab of the Ribbon, shown below, or the keyboard shortcut of ALT+F11.
Once we have done this the VBA development environment should appear. This is where you will be spending most of you time if you are following along with my blog posts or you choose to take our Excel VBA eLearning course.
Before we create a user defined function we need a Module added to our VBA Project. We would already have at least one if we had recorded any macros in this workbook but as I have not we will need to add one. To do this we right click over the VBA Project in the Project window and select Insert + Module (shown below).
Into this new Module we add the following VBA code:
The above function take two values as arguments and uses these to calculate the total amount of each order item. Then based on this total amount a discount is calculated. At the end of the function the discount is returned from the function as a currency amount.
We can now return to our sheet and use this UDF in the same way we would use a Formula as you can see below.
We can also use the fill down functionality to place this UDF in the required cells in the column.
This seems to work well but for the fact that our first order item goes above the amount we are allowed to order for a single item so we will need to address this as currently it is reporting a discount of 0.
It is interesting the number of times I have come across this. It makes sense to return a currency amount if I think about the function and how it should work normally but when you are writing code you spend most of your time thinking about how to handle the corner case scenarios. So what I am going to do is return text or as the type is called in VBA a String. Now this text could be an amount of discount but it will also allow me to return the text ‘Invalid Order’ which the current return type would not. So here is my adjusted function:
And the resulting sheet:
As you can see from this brief description you can build your own User Defined Functions quickly and easily to carry out your own specific calculations filling in the gaps you find with Excels formula and allowing you to extend Excel to your exact needs.