How to Create a User Defined Function in Excel VBA?
A function is a collection of code. As a developer, we very often need a custom function (User-defined function) in our projects. 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 integer (Number of vowels)
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.
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
- Return number of vowels.
countVowel = vowelCount End Function
Step 5: Save your Excel workbook as Excel Macro-Enabled Workbook (*.xlsm)
Step 6: Type “Customer” in cell “A1” and “Vowels_Count” in cell “B1” as header.
- Fill names in cells A2:A6
Step 7: Write below formula in cell “B2” (Img 2) and drag it to fill “B2:B6” (Img 3)