You can create your very own functions that do whatever you want it to; are called
User Defined Functions or
UDF and they are amazing.
To make these functions; we just need a little bit of VBA Coding since they are basically macros.
Steps to Create a UDF in Excel
- Hit Alt + F11 to go to the VBA Editor window
- Go to Insert > Module
You should now see a window that looks like this:
- In that window type Function and the name of your function and then an open and closing parenthesis and then hit Enter. I named my function CountCharacters
You can give your function basically any name that is not already used for functions.
Once you do this, the window will automatically add End Function to the window and it should look something like this:
- Put some code within the function to make it do something.
Here, I will simply set a variable equal to some text that I would like to output.
outputText = "This is output."
- Now, we need to put the output of our function into a variable that has the SAME name as our function.
To do this, I add this line of code to the bottom of the function's code:
CountCharacters = outputText
- Hit Alt + F11 to go back to Excel and input the new function into a cell.
Notice that when I start typing the name of the function it appears in the function list drop-down for Excel.
When you finish entering the function hit Enter like you would with a regular function.
- That's it! Here is the final result.
In this example, I created the simplest form of a UDF or User Defined Function.
Every UDF that you create will follow the basic structure outlined in this example, which is:
- Functions must start with Function and then the name you want to give that function and an open and closing parenthesis. Arguments can go into the parenthesis, as discussed below in another example.
- Functions must end with the text End Function, which the Excel VBA window will usually input for you.
- To generate the output for the function and send it back to Excel, you must assign the output of the function to a variable that has the exact same name as your function.
Adding Arguments to the 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.
Multiple Arguments
You can have multiple arguments, just separate each one with a comma like this:
Function MyFunctionName(Argument_1, Argument_2, Etc.)
Using VBA within the Function
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.
Output is:
We now have a function that counts how many characters are in a cell and gives us the result.
The Final Result: Custom Excel Function
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
Notes
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.
