Excel VBA

Why you should create a User defined function?

Following can be the reasons to create user defined function

When there is no function for this

Sometimes there is no specific formula for what you need to calculate. In that case you can create your own function to perform a particular task.

Replace a complex Formula

There are some formulas which are difficult to understand and also hard to use. In that case you can create your own easy custom formula to perform a particular task. 

When you don’t want to use Sub Routine

Although you can use a VBA code to perform a calculation but if you want to repeat the task then you have to run that code and again if you update your calculation. But if you convert that code into a function then you simply insert it as a function instead of running that code again and again.

How you can create a user defined function in Excel?

Creating a custom function in Excel is a process that can be divided in following ways:

1. At the very first you have to declare your Procedure as a Function

2. Defining its Argument and their Data Type

3. Add code to calculate the desired value.

functions

What are the different ways to create a VBA function in Excel?

Without any Argument

With just one Argument

With Multiple Argument

Using Array as the Argument

What are the Limitations of using User Defined Function in Excel?

Following are the limitation of User Defined Function in Excel:

• It can’t make changes to any of the environment options
• Make changes to another cell’s value.
• You can’t change, delete or format cells and a range by using a custom function.
• It does not allow to rename, or add worksheets to a workbook.

A function is a collection of code.  As a developer, we very often need a custom function (User-defined function) in our projects. These functions can be used as normal functions in Excel. These are helpful when the existing functions are not enough. In such cases, the user can create his own custom user-defined function to fulfil the need.