Create UDF in Excel in 4 Steps – Your Own Excel Formula

.

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

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
 
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
….. 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:
 
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.

List of User Defined Functions Created

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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Learn Excel Macro Custom Formula to Reverse a String in Excel - [...] Dear Friends,   This is a very small but useful Custom Function which can be used to reverse a…
  2. Check if file is already open using excel VBA - Welcome to LearnExcelMacro.com - […] 4 Steps to Create UDFs […]
  3. UDF to Convert Numbers to Letters - Spell Currency in Words - […] UDF: To exctract All Numbers, Special Characters and Alphabets from a String Create UDF in Excel in 4 Steps…
  4. Custom Excel Formula to Count based on Color - Let's excel in Excel - […] Refer to the bellow article to know how to use the above code in order to create a custom…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest