Sample user-defined function to hide formula errors in Excel (280094)

SUMMARY

Some formulas in Microsoft Excel return error values under certain conditions. For example, when you use a division formula that multiplies a number by zero, you receive the following error value:

#DIV/0!

Using the sample formula =100/0, you can work around this behavior by hiding the error value. To do this, modify the formula as follows:

=IF(ISERROR(100/0),"",100/0)

NOTE: The preceding formula works, but with longer formulas it can become cumbersome.

With the user-defined function provided in this article, the workaround formula is as follows:

=IFERROR(100/0,"")

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To Create the Sample Function

In Microsoft Excel, open the Microsoft Visual Basic Editor. To do this, point to Macro on the Tools menu, and then click Visual Basic Editor (or press ALT+F11).

Click Module on the Insert menu, and then type the following macro:

Function IfError(formula As Variant, show As String)

    On Error GoTo ErrorHandler

    If IsError(formula) Then
        IfError = show
    Else
        IfError = formula
    End If

    Exit Function

ErrorHandler:
    Resume Next

End Function

On the File menu, click Close and Return to Microsoft Excel.
To use the function, click Insert Function on the Insert menu. In the Insert Function dialog box, click User Defined under Categories, and then click IfError under Select a function. Click OK.
Next to Formula, type the formula for which you want to hide the error value; do not include the equal (=) sign.
Next to Show, type what you want to show in place of the error value. If you want to hide the error value, type double quotes ("").
Click OK.

For more information about how to use the sample code in this article, click the article number below to view the article in the Microsoft Knowledge Base:

212536 How to run sample code from Knowledge Base articles in Office 2000