Analyzing the custom function

Function procedures can be as complex as you need. 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 Reverse(InString) As String ' Returns its argument, reversed

Dim StringLength as Integer, i as Integer Reverse = ""

StringLength = Len(InString) For i = StringLength To 1 Step -1

Reverse = Reverse & Mid(InString, i, 1) Next i End Function

Notice that the procedure starts with the keyword Function, rather than Sub, followed by the name of the function (Reverse). This custom function uses only one argument (InString), 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 a comment (optional) that describes what the function does. This is followed by a Dim statement for the two variables (StringLength and i) used in the procedure.

The procedure initializes the result as an empty string. Note 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.

Next, VBA's Len function determines the length of the input string and assigns this value to the StringLength variable.

The next three instructions make up a For-Next loop. The procedure loops through each character in the input (backwards) and builds the string. Notice that the Step value in the For-Next loop is a negative number, causing the looping to proceed in reverse. The instruction within the loop uses VBA's Mid function to return a single character from the input string. When the loop is finished, Reverse consists of the input string, with the characters rearranged in reverse order. This string is the value that the function returns.

The procedure ends with an End Function statement.

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 will always return an error. Remember, a function simply returns a value. It cannot perform actions with objects.

Was this article helpful?

0 0

Post a comment