PPT Excel: How to create a custom function in Excel

Do you need to create a custom function in excel for your project? This post explains how to create a custom function to count the number of vowels in a string.

Suppose you automate reports or create a dashboard. You may need to create a custom function for your projects or sometimes for your analysis. UDFs are special functions that accept input, execute, and return the output. The input of the function can be any excel compatible type.

Syntax

Accept an argument, counts the number of vowels in it, and return the count.

Function Name: NoOfVowels

Input parameter: Excel cell reference

Return: An integer value (frequency of vowels)

Please follow the below steps to create a user defined custom function and how to use it in Excel.

1. Copied the above data in your excel column A

2. Press Alt + F11 – to open Visual Basic Editor in a new window without closing Excel
3. Go to Insert
4. Select Module

Function NoOfVowels(rge As Range)
' for loop
For i = 1 To Len(rge.Value)
    txtVal = UCase(Mid(rge.Value, i, 1))
    If txtVal Like "[AEIOU]" Then
        vCnt = vCnt + 1
    End If
Next i
'Return Count
NoOfVowels = vCnt
End Function

5. Copy and paste the above script to your module
6. Save the file

The custom functions always start with Function and end with End Function. The above function accepts one argument. you can create a custom function to accept an array or multiple values as arguments.

VBA script finds the length of the given string in a cell. check one by one character and increase vCnt by 1 if the character is a Vowel AEIOU.

Once it is executed finally it returns the vCnt.

How to use the custom function in excel

1. Type the header Number of Vowels in cell B1
2. Select cell B2, type the below formula

=NoOfVowels(A2)

3. Drag the formula till cell B11

Conclusions

Even excel has a number of in-built functions. Still, the programmers or analysts need specific User Defined Function. VBA allows you to create your own custom function and use it in excel. Your custom function name cannot be the excel in-built function name and it should be descriptive and easy to understand.