Microsoft KB Archive/280094

= Sample user-defined function to hide formula errors in Excel =

Article ID: 280094

Article Last Modified on 5/17/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q280094



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:


 * 1) 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),&quot;&quot;,100/0)

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

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

=IFERROR(100/0,&quot;&quot;)



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.

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 + F11.

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 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 (&quot;&quot;). Click OK.</li></ol>

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:

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

Additional query words: inf VLOOKUP #N/A XL2000 XL97 XL2002 XL2003 XL2007

Keywords: kbhowto kbinfo KB280094

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.