UDF error handling
This module shows how to handle routine errors in user defined functions (UDFs) and other procedures. The simple example used is a function to return the reciprocal of a number, with the error raised when divide by zero occurs. Three variations of the function are developed in Code panes 1, 2, and 3. Without any specific error handling, all three return a #VALUE! error when used as worksheet functions. The analyst wants to replace the #VALUE! error with a #DIV/0! error.
Code 1
In Code 1, the divide by zero condition is tested by the If statement in line 4 . If Number = 0
is TRUE, then the CVErr(xlErrDiv0) value is assigned to the function name xlfReciprocal_1
in line 5. Execution then skips to line 8.
Code 1: Function
xlfReciprocal_1
the reciprocal of Number: 1 / Number
Function xlfReciprocal_1(Number As Double) As Variant '' Return the reciprocal of Number: 1 / Number If Number = 0 Then xlfReciprocal_1 = CVErr(xlErrDiv0) Else xlfReciprocal_1 = 1 / Number End If End Function
The #DIV/0! error is code value 2007 (see the Cell Error Values table below) with an enumeration of xlErrDiv0. This value is converted to an error with the VBA CVErr function. CVErr returns a Variant with subtype Error, thus the function name is set to type Variant to allow this return type.
Code 2
This example uses formal error handling. In code 2 line 3, the VBA On Error Goto <line / label> statement transfers program control to the line with the label name - ErrHandler line 7. This occurs in the case of a worksheet #VALUE! error, or a VBA Run-time error (shown in figure 1). All GoTo labels end with a : character. In cases where an error is not raised, the Exit Function
statement in line 6 suppresses execution of the error handler statement otherwise execution will always continue to the End Function
statement
Code 2: Function
xlfReciprocal_2
the reciprocal of Number: 1 / Number
Public Function xlfReciprocal_2(Number As Double) As Variant '' Return the reciprocal of Number: Number^(-1) On Error GoTo ErrHandler xlfReciprocal_2 = Number ^ -1 Exit Function ErrHandler: xlfReciprocal_2 = CVErr(xlErrDiv0) End Function
an alternate version of line 8 is ...
xlfReciprocal_2 = VBA.CVErr(XlCVError.xlErrDiv0)
where XlCVError displays the CVerror constants list (figure 1).

Code 3
The xlfReciprocal_3 function contains no error handling statements. In the case of a an error, it returns a #VALUE! error from the worksheet, and raises a Debug Error message when called from a VBA procedure - see code 4 for statement details.
Code 3: Function
xlfReciprocal_3
the reciprocal of Number: 1 / Number
Function xlfReciprocal_3(Number As Double) As Variant '' Return the reciprocal of Number: 1 / Number xlfReciprocal_3 = 1 / Number End Function
The Debug Error is in figure 1 - top panel. It is called a Debug Error because the default button is Debug.
Code 4: Sub
TestxlfReciprocal
the reciprocal of Number: 1 / Number
Sub TestxlfReciprocal() Dim Ans1, Ans2, Ans3 Ans1 = xlfReciprocal_1(0) Ans2 = xlfReciprocal_2(0) Ans3 = xlfReciprocal_3(0) End Sub
If the Analyst clicks the Debug button, the code window displays the Yellow step line at the place where the error was raised (figure 2 - lower panel).

References
Cell Error Values
Excel CVerror constant(number) | Cell error value |
---|---|
xlErrBlocked (2047) | #BLOCKED! |
xlErrCalc (2050) | #CALC! |
xlErrConnect (2046) | #CONNECT! |
xlErrDiv0 (2007) | #DIV/0! |
xlErrField (2049) | #FIELD! |
xlErrGettingData (2043) | #GETTING_DATA |
xlErrNA (2042) | #N/A |
xlErrName (2029) | #NAME? |
xlErrNull (2000) | #NULL! |
xlErrNum (2036) | #NUM! |
xlErrRef (2023) | #REF! |
xlErrSpill (2045) | #SPILL! |
xlErrUnknown (2048) | #UNKNOWN! |
xlErrValue (2015) | #VALUE! |
VBA functions
VBA function | Description |
---|---|
CVErr(errornumber) | Returns a Variant of subtype Error (Locals Window > Type: Variant/Error) containing an error number specified by the user |
VBA statements
VBA statement | Description |
---|---|
Goto line | Branches to a specified line within a procedure |
On Error Goto line | Enables an error-handling routine and specifies the location of the routine within the procedure. Enables the error-handling routine that starts at specified line |
- This example was updated with Excel version 2004 (build 12730.20270) and VBA 7.1 version 1097
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]