ser Defined functions (UDF) is also known as Custom Functions (formulas) in Excel. So far, I have created many different User Defined Functions for different purposes but never told you about How to create Custom Functions in Excel in a systematic way.. Therefore in this article I am going to teach you Step by step procedure to create your own Custom formula in Excel.
What is User Defined Function in Excel (UDF) ?
With the name it is very clear that this is a function which is defined by users 😀 . Though, Excel has thousands of Built-in formulas which makes our job lot easier. Still, sometimes while working we feel..I wish… I had a simple excel formula to do this. Your wish come true by this feature of Excel called – UDF (User Defined Function).
Therefore such functions which are defined by users for their own tasks, are called as User Defined Functions (UDF). They are also called Custom Functions.. Once they are defined in your workbook, they exactly work like a Built-in formula of Excel.
4 Simple Steps to create User Defined Functions in Excel
Follow the below steps to create your own User Defined Function:
Step 1.
Open your Workbook
Step 2.
Press ALT+F11 to Open VBE (Visual Basic Editor) of the Workbook
Step 3.
Add a Module as shown below
Step 4.
This is the step where exactly you are going to write your function.
Your function should have the following format:
Public Function FunctionName (parameter1 As type1, parameter2 As type2,….) As returnType
….. lines of code to calculate result
FunctionName = result
End Function
Important Points to Note
While writing your function make sure you follow below points:
1. A User Defined function should Start with a FUNCTION Statement
2. Function Statement usually specifies few Parameters based on the requirement. It is not mandatory though.
3. DO NOT forget to assign the result of the function (which you would like to see as a result of the formula in the Cell) in to a variable name same as the Function Name.
Note: 3rd POINT IS VERY IMPORTANT TO DISPLAY THE RESULT IN THE CELL WHERE FORMULA IS WRITTEN. IF VARIABLE NAME AND FUNCTION NAME IS NOT SAME THEN YOU WILL FIND NO RESULT IN THE CELL
Yes, you are done. Now you know how to create a custom function in excel. This was theory part.. now learn it by doing it with the following example.
Example: How to Create Custom Function
I will take an example from my previous article to explain this – UDF – to get Word Count Excel Formula.
'********************************************************
'** Cell is a veriable of a Range Type. It means user **
'** is allowed to select a cell address as input.Value **
'** Value of that cell is used for getting the word **
'** count. **
'** ----------------------------------------------- **
'** Result of this Function is passed in a variable **
'** of the same name as the Function - GetWordCount **
'********************************************************
Public Function GetWordCount(Cell As Range) As Integer
GetWordCount = UBound(VBA.Split(Cell.Value, " ")) + 1
End Function
Explanation : How it Works ?
1. As soon as you type =GetWordCount in any cell of the same Workbook then excel will look in to the module, if there is any function defined with the same name. Excel will display the function name as soon as you start typing the Function Name as shown in beloe picture:
2. Once found then this function will get triggered.
3. Result of the function will be shown in the cell ONLY IF the variable name is same as the Function. This is important. Otherwise this function will be executed and result will be calculated but user will not be able to see any result in the cell.
List of User Defined Functions Created
You can read about some more examples and Downloadable files about User Defined Function
0 Comments
Trackbacks/Pingbacks