VBA UDF error handler

# VBA 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


### 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 1 - lower panel).

## References

### Cell Error Values

Excel CVerror constant(number)Cell error value
xlErrDiv0 (2007)#DIV/0!
xlErrNA (2042)#N/A
xlErrName (2029)#NAME?
xlErrNull (2000)#NULL!
xlErrNum (2036)#NUM!
xlErrRef (2023)#REF!
xlErrValue (2025)#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

• This example was developed in Excel 2013 with VBA 7.1
• Revised: Wednesday 2nd of November 2016 - 09:12 AM, [Australian Eastern Standard Time (EST)]