Microsoft Office Tutorials and References
In Depth Information
Assume that you want this function to work only with strings. If the argument does not contain a string, you
want the function to return an error value (#N/A). You may be tempted to simply assign a string that looks like
an Excel formula error value. For example:
REVERSETEXT = “#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
constants are listed here:
• xlErrDiv0
• xlErrNA
• xlErrName
• xlErrNull
• xlErrNum
• xlErrRef
• xlErrValue
The following is the revised REVERSETEXT function:
Function REVERSETEXT(text As Variant) As Variant
Returns its argument, reversed
If WorksheetFunction.ISNONTEXT(text) Then
REVERSETEXT = CVErr(xlErrNA)
Else
REVERSETEXT = StrReverse(text)
End If
End Function
First, change the argument from a String data type to a Variant. If the argument's data type is String, Excel tries
to convert whatever it gets (for example, number, Boolean value) to a String and usually succeeds. Next, the
Excel ISNONTEXT function is used to determine whether the argument is not a text string. If the argument is
not a text string, the function returns the #N/A error. Otherwise, it returns the characters in reverse order.
The data type for the return value of the original REVERSETEXT function was String be-
cause the function always returned a text string. In this revised version, the function is
declared as a Variant because it can now return something other than a string.
Search JabSto ::




Custom Search