A function that returns an error value

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

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

When used in a worksheet formula, this function removes the vowels from its single-cell argument. If the argument is a numeric value, this function returns the value as a string. You may prefer that the function returns an error value (#N/A), rather than the numeric value converted to a string.

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

RemoveVowels = "#N/A"

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 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:

RemoveVowels = CVErr(xlErrNA)

The revised RemoveVowels function follows. This function uses an If-Then construct to take a different action if the argument is not text. It uses Excel's ISTEXT function to determine whether the argument is text. If the argument is text, the function proceeds normally. If the cell doesn't contain text (or is empty), the function returns the #N/A error.

Function RemoveVowels(Txt) As Variant ' Removes all vowels from the Txt argument ' Returns #VALUE if Txt is not a string Dim i As Long RemoveVowels = ""

If Application.WorksheetFunction.IsText(Txt) Then 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 Else

RemoveVowels = CVErr(xlErrNA) End If End Function

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

0 0

Post a comment