In this post we’re going to explore how we can make our very own functions (a user defined function or UDF) in Excel that will act very similar to those native to Excel such as SUM, VLOOKUP, INDEX, MATCH etc. You might find you want to create your own formulas because of various reasons.

• What you want to do is just not possible with the native functions. Creating a UDF might be the only solution.
• The formula you created with native functions are just too long with too many repeated inputs so it’s hard to read. Creating a UDF can simplify this.

If you’re not very familiar with VBA, how to open the visual basic editor and where to put the code, then you may want to read this post about How To Use The VBA Code You Find Online.

In our example we are going to create a UDF that returns the cell colour from the referenced cell. It will be a very simple function with only three lines of code, but there is no native Excel function that can do this and a UDF is the only solution.

In the example, we have a set of customer order data but the status has been colour coded into the Order ID cells. Having colour coded data can look nice, but it can be hard to analyse the data with formulas and pivot tables. To translate this colour coding into tangible data, we will create a UDF that takes a cell as input and returns the cell format colour.

1. Here we have a colour coding legend that has been applied to our data.
   • Green = order has been received by the customer.
   • Yellow = order has been shipped to the customer but not yet received.
   • Red = order has not yet been shipped to the customer.
2. Our data contains an Order ID which has also been colour coded to contain the order status. This column contains two different pieces of data, however the visual data is not easy to work with and we would like to translate this into tangible data in a separate column.

Open the visual basic editor (VBE) by either using the keyboard shortcut Alt + F11 or going to the Developer tab and pressing the Visual Basic button under the Code section.

1. Create a new module to insert the UDF.
2. Copy and paste this code into the VBE.

   
   Function GetColour(SelectedRange As Range)
   
       GetColour = SelectedRange.Interior.Color
   
   End Function
   

All UDF’s will have these main components to the code.

• Opening and closing – Must open with Function and close the code with End Function.
• Function name – Must use a unique name that’s not already used by the system.
• Variable inputs – This is where you can declare the arguments of your UDF. You can have empty brackets () if your function has no arguments.
• Code – Code for your UDF should assign a value to your function name.

Now we can use our UDF in our spreadsheet in the usual manner that we use other native Excel functions. Since we saved the VBA locally in a module in the file, we can only use the UDF in this particular spreadsheet and it will not be available globally.

1. Start to type out our UDF =GetColour( in a cell.
2. Function autocomplete will show our UDF along with native functions.
3. Excel’s colour format code results are now displayed.