Analyzing the custom function

Function procedures can be as complex as you need them to be. Most of the time, they are 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 = ""

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 simply an optional comment that describes what the function does. This is followed by a Dim statement, which declares the variable (i) used in the procedure as type Long.

What Custom Worksheet Functions Can't Do

When you develop custom functions, it's important to understand a key distinction between functions that you call from other VBA procedures and functions that you use in worksheet formulas. Function procedures used in worksheet formulas must be passive. For example, code within a Function procedure cannot manipulate ranges or change things on the worksheet. An example can help make this clear.

You might be tempted to write a custom worksheet function that changes a cell's formatting. For example, it could be useful to have a formula that uses a custom function to change the color of text in a cell based on the cell's value. Try as you might, however, such a function is impossible to write. No matter what you do, the function won't change the worksheet. Remember, a function simply returns a value. It cannot perform actions with objects.

That said, I should point out one notable exception. It is possible to change the text in a cell comment by using a custom VBA function. Here's the function:

Function ModifyComment(Cell As Range, Cmt As String) Cell.Comment.Text Cmt End Function

Here's an example of using this function in a formula. The formula replaces the comment in cell A1 with new text. The function will not work if cell A1 doesn't have a comment.

=ModifyComment(A1,"Hey, I changed your comment")

Note 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 Excel's Like operator. In other words, the If clause is true if the character is not 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 the coding for this function can be done 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 Dim TempString As String TempString = "" For i = 1 To Len(txt)

Select Case ucase(Mid(txt, i, 1)) Case "A", "E", "I", "O", "U"

'Do nothing Case Else

TempString = TempString & Mid(txt, i, 1) End Select Next i

RemoveVowels = TempString End Function

In this version, I used a string variable (TempString) to store the vowel-less string as it is being constructed. Then, before the procedure ends, I assigned the contents of TempString to the function's name. This version also uses a Select Case construct rather than an If-Then construct.

CD- Both versions of this function are available on the companion CD-ROM. The file is named



0 0

Post a comment