U

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

## 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

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

# 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.