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

vowels removed.

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)

End If

Next i

End Function

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

function” section.

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:

=RemoveVowels(A1)

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.