How to create user defined formulae in Excel

Excel has hundreds of pre-defined formula built in.  For example: SUM, VLOOKUP, IF, AVERAGE, etc.  You’ve probably used them hundreds of times before.  Did you know though, that you can create your own formula?  As the name suggests, UDF (user defined formulae) allow you to do just that!  This can be useful if you do the same calculations over and over again, or if a formula become nested and overly complicated.

Like macros, UDFs use the VBA (visual basic for applications) programming language.  They are different though in 2 ways:

  1. They use FUNCTION procedures instead of SUB procedures.  
  2. They perform calculations instead of taking actions.  So for example, they cannot alter the structure of a worksheet, such as changing the worksheet name, turning off gridlines, protecting the worksheet etc.  In the same way, they cannot be used to alter the physical characteristics of cells.

An example of a UDF might be if you wanted to calculate a 10% discount on orders using the price in column A and the number ordered in column B and display the results in column C.  You could either do a calculation, for example in cell C2 you could write “=A2 * B2 * 0.1” or you could write a UDF in the standard code module:

Function Discount(quantity, price)
    If quantity >= 100 Then
        Discount = quantity * price * 0.1
        Discount = 0
    End If
    Discount = Application.Round(Discount, 2)
End Function

In cell C2 all you then need type is “=Discount(A2,B2)”.  When Excel calculates the result, it runs the VBA code, knowing that the two named cells A2 and B2 give the arguments “quantity” and “price” as detailed in line 2 of the code.

For those who are not used to writing VBA or code, this is probably getting a little complicated, so let’s go through it step by step!

Where are UDFs created / saved?

If you haven’t got the developer tab visible, you will need to make it visible.  Then click on the visual basic button.

developer tab

Rather than saving / creating the code on one of the sheets, UDFs are saved on the modules.  You can create a new one easily by clicking on insert > module.  You’ll see in the picture below that I’ve added my function into module 1 (the first module is always called module 1, then module 2 etc, but you can change this name if you want to.)

moduleOnce you have a module you can simply type your new function, or copy / paste a function into the module and save, always remembering that any excel 2010 file that has code needs to be saved with the extension *.xlsm

What are the different elements of a UDF?

UDFs always start with the word “Function” and end with the words “End Function“.  After the word function is the Name of your UDF followed by Arguments in brackets. So, for example:

Function Discount (quantity, price)

... code goes here

End Function

In the code above, “Discount” is the Name of the function and what we will use in our excel formula later.  For example: “=Discount(A2,B2)”

Quantity” and “price” are arguments.  Arguments are the different elements used in the calculation.  We will explain in the later code what we want Excel to do with these arguments, for example perform a multiplication.  When you use the function in a cell, you will need to explain to Excel where to find the values for these arguments.  In our example, when we type in the formula in Excel, we will type “=Discount(A2,B2)”.  This tells Excel that the value of the “quantityargument will be found in cell A2 and the value of the “priceargument will be found in cell B2.  Excel uses the same order in the formula as given in the code.

The remainder of the UDF is the calculation that must be performed.  This calculation will use the arguments already defined, along with any of Excel’s existing formulae that need to be used.  In our example, we get excel to check that the quantity is greater than 100 and if so, we calculate the discount of 10%.  If the quantity is less than 0, then there is no discount.  Finally, since we are dealing with money, we round the number up to 2 decimal places.

Function Discount(quantity, price)
    If quantity >= 100 Then
        Discount = quantity * price * 0.1
        Discount = 0
    End If
    Discount = Application.Round(Discount, 2)
End Function

How do we then add this into excel?

We can now use this UDF as a normal part of a formula.  In our case we would type in cell C2 “=Discount(A2,B2)”.  This tells Excel to calculate the discount.  We could also use it again, in another cell or column, as long as we remember to explain to Excel where to find the values for each argument.

Can you show me some other examples?

This method can be used for virtually ANY calculation.  You just need to be able to define your name, arguments, and the calculation to be done.

Just to show you how diverse it can be, this one will give you the RGB value of the fill of any cell you choose using the formula “=getRGB(R2), where R2 is the cell whose RGB value you want to know :

Function getRGB(RefCell)
Dim mystr As String
    mystr = Right("000000" & Hex(RefCell.Interior.Color), 6)
    getRGB = Application.Hex2Dec(Right(mystr, 2)) & ", " & _
             Application.Hex2Dec(Mid(mystr, 3, 2)) & ", " & _
             Application.Hex2Dec(Left(mystr, 2))
End Function

Another example can be found below with the formula “=TrainingResult(G2), where G2 is the cell you want to check:

Function TrainingResult(grade As Integer) As String
If grade >=80 Then
    TrainingResult = "Pass"
    TrainingResult = "Fail"
End If
End Function

What do you think?  I’ve uploaded two spreadsheets with the first two examples to get you started.  You can download them here (discount) and here (colour).  The discount sheet has a bonus example of a UDF (total).  Enjoy!

+Alesandra Blakeston

3 thoughts on “How to create user defined formulae in Excel

  1. Excellent work! I’ve done on for a Mc/Mac workaround for PROPER() but haven’t got around to posting it yet.

    If you don’t mind… something that helps using VBA, or that I have found useful when starting out (or when I’m a little rusty), is to record a macro as you work on the sheet and then use the VBA editor to see how it’s built the code up and see what commands it is using – obviously this won’t give the IFs, but it can help with the bits in between.

    It’s not an easy one, that, well explained.

    1. Great comment Matt.

      You are right of course, the recording function is extremely useful. It’s a little difficult recording UDFs though. You have to cut and paste the sub procedures into a function procedure and if you don’t really understand what you are doing it can make things worse not better! I must admit though, I do use this method myself. Wasn’t sure whether to include it in the post though, so I’m glad you posted it! Cheers.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s