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

    Fig 1: XlCVerror - list of constants is available in the drop-down

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

    Fig 2: Visual Basic Run-time error - Division by zero - Debug window and associated error line


    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 functionDescription
    CVErr(errornumber)Returns a Variant of subtype Error (Locals Window > Type: Variant/Error) containing an error number specified by the user

    VBA statements

    VBA statementDescription
    Goto lineBranches to a specified line within a procedure
    On Error Goto lineEnables an error-handling routine and specifies the location of the routine within the procedure. Enables the error-handling routine that starts at specified line