How to create a custom worksheet function in Excel. These are called User Defined Functions, or UDF, and they are amazing. You can create your very own function that does whatever you want it to, almost.
To make these functions we just need a little bit of VBA since they are basically macros.
Steps to Create a UDF in Excel
Adding Arguments to the Function
The Final Result: Custom Excel Function
outputText = "This is output."
CountCharacters = outputText
In this example I created the simplest form of a UDF or User Defined Function.
Let's now add an argument to the function in order to make it more useful.
Arguments are the parts of a function where you can input data, or select a cell that has data, that you want your function to use. Arguments are the way you get data into your function.
It's actually very simple; we just input text in-between the parenthesis after the name of the function, and that text will be the argument.
Let's start with the example we made above:
Now, add text in the parenthesis:
I put input_value as the argument. Note that you can't use spaces for the names, instead, separate words using underscores.
Now that we have input_value as an argument, we can use it within the function. This is how we get data from the user into the function.
To make this function more dynamic, since it currently only outputs the hardcoded text "This is output.", I will set the variable outputText equal to the new argument input_value.
Go back to Excel (hit Alt + F11) and let's try this function on another cell.
Here is the result:
This simple function now outputs whatever text is given to it, in this case, the text from cell A1.
You can have multiple arguments, just separate each one with a comma like this:
Function MyFunctionName(Argument_1, Argument_2, Etc.)
We are creating this function using VBA (Visual Basic for Applications), which is the same thing we use for regular macros. As such, we can do many interesting and powerful things. Now, functions can't do everything that a regular macro can do, because the goal of a function is to return a result back to the cell, but you can still do a lot.
Let's finish the function we started to create and make it count all of the characters that are in a cell.
To do this, we add the len VBA function, which is used to count the length of the value that you put inside of it.
I want to count the input to this function, which is provided through the input_value variable and so I need to put that inside the len function.
This:
outputText = input_value
Will become this:
outputText = len(input_value)
Since the rest of the function is already setup to output the value stored in the outputText variable, I don't need to change anything else.
Here is the final Function code:
Go back to Excel and try it now.
Outputs:
We now have a function that counts how many characters are in a cell and gives us the result.
This was a very simple example using VBA to create a function but you can include many lines of code depending on the complexity of what you are trying to do. In this example, I tried to keep things simple to help give you an idea of how everything works so you can build upon it.
Here is the final version of the code that was created:
Function CountCharacters(input_value)
outputText = Len(input_value)
CountCharacters = outputText
End Function
Custom Excel functions, or UDFs, are simply awesome. They are one of my favorite aspects of Excel because it allows you to easily create a function that does almost whatever you could want it to do. When you have repetitive tasks that take many steps or require a complex formula that is hard to remember, you can create a custom function to do the work for you.
The example that I created above is not a useful custom function on its own since there is already a LEN() function in Excel, but it should help you to understand how custom functions are made and used.
UDFs are only available in the workbook in which you have them or when that workbook is open and you are referencing them using the correct cross-workbook method, with is rather annoying. In another tutorial I will show you how to make them available everywhere.
Make sure to download the attached workbook to see the final result and get the code.