Dear Readers,
 
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:

 

  1. UDF – Is File Open?
  2. UDF to Convert Currency to Words
  3. UDF to Count Words in Cell
  4. UDF to Extract Numbers from a cell
  5. UDF to Extract Special Characters from a cell
  6. UDF to Extract Alphabets from a cell
  7. UDF to Strip/Remove HTML tags
  8. UDF to Extract email ID from a Cell

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 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
 
How to Add - Module
 

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

 

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:

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:
 
UDF - GetWordCount Formula
 
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.

You can read about some more examples and Downloadable files about User Defined Function

 

  1. UDF – Is File Open?
  2. UDF to Convert Currency to Words
  3. UDF to Count Words in Cell
  4. UDF to Extract Numbers from a cell
  5. UDF to Extract Special Characters from a cell
  6. UDF to Extract Alphabets from a cell
  7. UDF to Strip/Remove HTML tags
  8. UDF to Extract email ID from a Cell