An Introductory Function Example

Without further ado, this section presents an example of a VBA Function procedure.

A custom function

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 certainly isn't the most useful function 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.

Caution  

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 will not work in your formulas.

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 would 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.

Figure 10-1: Using a custom function in a worksheet formula.

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.

 =UPPER(RemoveVowels(A1)) 

Using the function in a VBA procedure

In addition to using custom functions in worksheet formulas, you can also use them in other VBA procedures. The following VBA procedure, which is defined in the same module as the custom RemoveVowels function, first displays an input box to solicit some text from the user. Then the procedure uses the VBA built-in MsgBox function to display the user input after it's processed by the RemoveVowels function (see Figure 10-2). The original input appears as the caption in the message box.

Figure 10-2: Using a custom function in a VBA procedure.

 Sub ZapTheVowels()      Dim UserInput as String      UserInput = InputBox("Enter some text:")      MsgBox RemoveVowels(UserInput), , UserInput End Sub 

In the example shown in Figure 10-2, the string entered in response to the InputBox function was Excel Power Programming With VBA . The MsgBox function displays the text without vowels.

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 = ""     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 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-ROM  

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