User Defined functions (UDF) is also known as Custom Functions (formulas) in Excel. Previously I have created many different User Defined Functions for different purposes but never told you about How to create Custom Functions in Excel. Therefore in this article I am going to tell you stel by step procedure of creating Custom formula in Excel.

You can read and try following User Defined functions created:

### What is User Defined Function in Excel?

Though in Excel there are thousands of Built-in formulas which are used to make our job easier. Still, sometimes while working we feel…Ooppss.. to do this task there is no built-in formula in excel. But you don’t worry, Excel has provided you flexibility for creating your own Functions to use.

Therefore such functions which are defined by users for their own tasks, are called as User Defined Functions. They are also called Custom Functions.

### How to create a User Defined Function in Simple Four (4) Steps

Follow the below steps to create your own defined function:

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

#### Important Points to Note

While writing your function make sure you follow below points:

• A User Defined function should Start with a FUNCTION Statement
• Function Statement usually specifies one or more Parameters.
• DO NOT forget to assign the result of the function in to a variable name same as the Function. THIS 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 VALUE IN RESULT IN THE CELL

#### Example:

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

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