Microsoft Office Tutorials and References
In Depth Information
Why Create Custom Functions?
Analyzing the custom function
Function procedures can be as complex as you need them to be. Most of the time, they’re
more complex and much more useful than this sample procedure. Nonetheless, an analysis of this
example may help you understand what is happening.
Here’s the code, again:
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
Notice that the procedure starts with the keyword Function , rather than Sub , followed by the
name of the function ( RemoveVowels ). This custom function uses only one argument ( Txt ),
enclosed in parentheses. As String defines the data type of the function’s return value. Excel
uses the Variant data type if no data type is specified.
The second line is an optional comment that describes what the function does. This line is
followed by a Dim statement, which declares the variable ( i ) used in the procedure as type Long .
Notice that I use the function name as a variable here. When a function ends, it always
returns the current value of the variable that corresponds to the function’s name.
The next five instructions make up a For-Next loop. The procedure loops through each
character in the input and builds the string. The first instruction within the loop uses VBA’s Mid function
to return a single character from the input string and converts this character to uppercase. That
character is then compared to a list of characters by using VBA’s Like operator. In other words,
the If clause is true if the character isn’t A, E, I, O, or U. In such a case, the character is appended
to the RemoveVowels variable.
When the loop is finished, RemoveVowels consists of the input string with all the vowels
removed. This string is the value that the function returns.
The procedure ends with an End Function statement.
Keep in mind that you can do the coding for this function in a number of different ways. Here’s a
function that accomplishes the same result but is coded differently:
Function RemoveVowels(txt) As String
‘ Removes all vowels from the Txt argument
Dim i As Long
 
Search JabSto ::




Custom Search