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. 

What are User Defined Functions

User Defined Function(UDF) is the custom function that is created by the user to perform a specific task in VBA(Virtual basic application) which is a programming language in Excel.

Function Vs. Subroutine in VBA

In VBA, a ‘Subroutine’ lets you run a group of commands, while a ‘Function’ provides a result.

For example, consider a list of numbers, some positive and some negative. With a subroutine, you can scan each cell and mark negative ones by changing their colour. The subroutine modifies the cell properties.

On the other hand, a custom function can be used in a separate column. It returns TRUE for negative values and FALSE for positive ones. Functions can’t change cell properties directly but can be used with conditional formatting for the same effect.

When you create a User Defined Function (UDF) in VBA, you can use it just like any other Excel function, as I’ll explain further in the ‘Different Ways to Use a User Defined Function in Excel’ section.

Function Definition

VBA, a function is like a helpful tool that can give you a result if you ask it nicely. It’s like having a magic box that you put some things into (we call them parameters), and it gives you something back (we call it a return value).

One cool thing about this magic box is that it can give you not just one thing but a bunch of things all at once, and we call that a list or an array.

Before you can use this magic box, you need to tell VBA how it works. You do this by using the word “Function,” giving your magic box a special name, and then describing how it should work with any parameters it needs. Finally, you use “End Function” to say you’re done explaining.

Syntax

Function Functionname(parameter-list)

statement 1

statement 2

statement 3

…….

statement n

End Function

How to Create a User-Defined Function in VBA

 Here we explain how to create a UDF (user-defined function) to count/return the Number of vowels in a given string using Excel VBA.  

Example Custom Function Syntax

Function Name: countvowel()

Input parameter: an excel cell

Output: Return an interger (Number of vowels)

Implementation

Follow the below steps to create a User-defined Function in Excel VBA:

Step 1: Open an excel file, To create user-defined function “countVowel()” function.

Step 2: Press Alt + F11 – to open Visual Basic Editor.

Step 3: Click Insert >> Module – which will add a new module as in Img1.

The VBA code window will be opened where you can write the code of the Function.

Img 1

Step 4: Type the below code in “code window”.

• Function name with parameter Range

Function countVowel(rg As Range)

• Iterate each character of a given string and check the character is vowel or not.  If it is vowel character increase vowelCount = vowelCount +1

For i = 1 to Len(rg.Value

textValue = UCase(Mid(rg.value, i, 1))

If textValue Like “[AEIOU]”  then

vowelCount = vowelCount +1 

End If

Next i

• Returns Number of vowels.

countvowel = vowelCount

End Function

Step 5: Save your Excel workbook as Excel Macro-Enabled Workbook (*.xlsm) 

Calling a Function

To activate a function, you simply use the function name, just like in the example provided in the screenshot.

Step 1: Type “Customer” in cell “A1” and “Vowels_Count” in cell “B1” as header.

• Fill names in cells A2:A6

Step 2: Write below formula in cell “B2” and drag it to fill “B2:B6” .

=countVowel (A2)

Step 3: Preveiw the Result

Scope of User Defined Function

Public 

You have to make your function public if you want to access that function in all worksheets of the workbook. For making a function public, you just need to use the word “Public”. 

Public Function countVowel(rg As Range)

(WHOLE CODE)

Note: By default the functions are public. if you don’t make it private.

Private

When you make a function private you can use that particular function in the procedure of the same module. To make a function private just use the word “Private”

Private Function countVowel(rg As Range)

(WHOLE CODE)

If you have made a function privately in Module 1, then you can it in procedures you have in Module 1. And it won’t appear in the function list of teh worksheet (when you use = sign and try to type the name) but you can use it by specifying it’s name and arguments.

Also Read

Function and Sub in Excel VBA

FAQs on 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.