Microsoft Office Tutorials and References
In Depth Information
Why Create Custom Functions?
The following is a custom function defined in a VBA module. This function, named
RemoveVowels , uses a single argument. The function returns the argument, but with all the
Function RemoveVowels(Txt) As String
‘ Removes all vowels from the Txt argument
Dim i As Long
RemoveVowels = “”
For i = 1 To Len(Txt)
If Not UCase(Mid(Txt, i, 1)) Like “[AEIOU]” Then
RemoveVowels = RemoveVowels & Mid(Txt, i, 1)
This function certainly isn’t the most useful one I’ve written, but it demonstrates some key
concepts related to functions. I explain how this function works later, in the “Analyzing the custom
When you create custom functions that will be used in a worksheet formula, make sure
that the code resides in a normal VBA module. If you place your custom functions in a
code module for a UserForm , a Sheet , or ThisWorkbook , they won’t work in your
formulas. Your formulas will return a #NAME? error.
Using the function in a worksheet
When you enter a formula that uses the RemoveVowels function, Excel executes the code to
get the value. Here’s an example of how you’d use the function in a formula:
See Figure 10-1 for examples of this function in action. The formulas are in column B, and they use
the text in column A as their arguments. As you can see, the function returns the single
argument, but with the vowels removed.
Actually, the function works pretty much like any built-in worksheet function. You can insert it in
a formula by choosing Formulas➜Function Library➜Insert Function or by clicking the Insert
Function Wizard icon to the left of the formula bar. Either of these actions displays the Insert
Function dialog box. In the Insert Function dialog box, your custom functions are located, by
default, in the User Defined category.
You can also nest custom functions and combine them with other elements in your formulas. For
example, the following formula nests the RemoveVowels function inside Excel’s UPPER
function. The result is the original string (sans vowels), converted to uppercase.