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:
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:
The preceding formula works, but with longer formulas it can
With the user-defined function that is provided in this
article, the workaround formula is as follows:
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
How to create the sample function
- In Excel, open the Microsoft Visual Basic Editor.
To do this in
Microsoft Office Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. Alternatively, press ALT+F11.
To do this in Microsoft Office
Excel 2007, click the Developer tab, and then click
Visual Basic in the Code group. Alternatively, press ALT
Note To show the Developer tab in the Ribbon, click the
Microsoft Office Button, click Excel Options, click the
Popular category, click to select the Show Developer
tab in the Ribbon check box, and then click OK.
- 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
IfError = formula
- On the File menu, click Close and Return to Microsoft
- 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 following article number to view the article in the Microsoft Knowledge Base:
How to run sample code from Knowledge Base articles in Office 2000
Article ID: 280094 - Last Review: May 17, 2007 - Revision: 4.2
- Microsoft Office Excel 2007
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 97 Standard Edition