A function that returns an error value

In some cases, you might want your custom function to return a particular error value. Consider the Reverse function, which I presented earlier in this chapter:

Function Reverse(InString) As String ' Returns its argument, reversed

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

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

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

When used in a worksheet formula, this function reverses the contents of its single-cell argument (which can be text or a value). Assume that you want this function to work only with text strings. If the argument doesn't contain a string, you want the function to return an error value (#N/A).

You might be tempted simply to assign a string that looks like an Excel formula error value. For example:

Although the string looks like an error value, it is not treated as such by other formulas that may reference it. To return a real error value from a function, use the VBA CVErr function, which converts an error number to a real error.

Fortunately, VBA has built-in constants for the errors that you would want to return from a custom function. These errors are Excel formula error values and not VBA runtime error values. These constants are as follows:

To return a #N/A error from a custom function, you can use a statement like this:

Reverse = CVErr(xlErrNA)

The revised Reverse function follows. This function uses Excel's IsText function to determine whether the argument contains text. If it does, the function proceeds normally. If the cell doesn't contain text (or is empty), the function returns the #N/A error.

Function Reverse(InString) as Variant ' If a string, returns its argument, reversed ' Otherwise returns #N/A error

Dim i as Integer, StringLength as Integer If Application.WorksheetFunction.IsText(InString) Then Reverse = ""

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

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

Else

Reverse = CVErr(xlErrNA) End If End Function

Notice that I also changed the data type for the function's return value. Because the function can now return something other than a string, I changed the data type to Variant.

Was this article helpful?

0 0

Post a comment