Microsoft Office Tutorials and References
In Depth Information
Function Examples
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:
h xlErrDiv0 (for #DIV/0!)
h xlErrNA (for #N/A)
h xlErrName (for #NAME?)
h xlErrNull (for #NULL!)
h xlErrNum (for #NUM!)
h xlErrRef (for #REF!)
h xlErrValue (for #VALUE!)
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 isn’t 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
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 .
 
Search JabSto ::




Custom Search