Returning an Error from an Excel User-Defined Function
It’s often useful to write your own custom functions for Excel. These are often called User-Defined Functions, or UDFs. It’s actually not that hard if you know a bit of programming. It’s all done in VBA, or Visual Basic for Applications. I’ve probably written hundreds of these in the last 7 or 8 years, but one trick I always forget is how to return a customized error message.
If a line in your function produces a run-time error, you won’t get a popup message like you would for a Subroutine. Instead, code execution ends immediately, and you get the error #VALUE! in the cell on the worksheet.
It is often useful to return a more descriptive error. To do this, you can use On Error to trap the error, and return one of Excel’s built in error types. To do this, use the VBA function CVErr and a constant to set the built-in error type. These are (via the online help under “Cell Error Values”):
|
Constant |
|
|
|
xlErrDiv0 |
|
|
|
xlErrNA |
|
|
|
xlErrName |
|
|
|
xlErrNull |
|
|
|
xlErrNum |
|
|
|
xlErrRef |
|
|
|
xlErrValue |
|
|
For your UDF to return custom errors, you must declare the function so that it returns the data type Variant. See also this Microsoft knowldgebase article on Error Trapping with Visual Basic for Applications.
Here’s an example of a UDF that returns the error #N/A. In this case, the function is a slightly more sophisticated version of the built-in function FIND, which returns the starting position of a one text string within another string. I wanted a function that would return the nth position, rather than just the first occurrence.
Function FindNth(find_text As String, within_text As String, n As Long) As Variant
'Finding the nth occurrence of a text string within another text string
'Uses Excel's built-in Worksheet Find function iteratively
'(probably faster than a brute-force approach using a loop)
'If find_text is not found, returns the error #N/A
Dim pos As Variant
Dim i As Long
Dim result As Long
i = 0
result = 0
Do
On Error GoTo ErrLine
pos = Application.WorksheetFunction.Find(find_text, within_text)
result = result + pos
i = i + 1
If i = n Then Exit Do
within_text = Right(within_text, Len(within_text) - pos)
Loop
FindNth = result
Exit Function
ErrLine: FindNth = CVErr(xlErrNA)
End Function
For example:
=FINDNTH(" ", "Mira Vista Avenue", 2)
Returns 11, the position of the second occurrence of a space in “Mira Vista Avenue”.
=FINDNTH("x", "Microsoft", 3)
Returns #N/A, since it is looking for the 3rd letter x in “Microsoft”, and can’t find it.
In this example, the function should probably return #VALUE! to copy the FIND function’s built-in behavior. But I wanted to differentiate between a VBA run-time error and an error returned by the function, and #N/A seemed to fit the bill.