Microsoft KB Archive/94860

= Microsoft Knowledge Base =

Excel: Suppressing #N/A! Error in Custom Function
Last reviewed: September 12, 1996

Article ID: Q94860

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.x, 4.0, 5.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY
In Microsoft Excel, you can use the ISNA function along with an IF statement to determine if an argument in a custom function is missing or left blank. This function allows you to suppress the #N/A! error value that is returned by a custom function when an argument is blank or missing. (Note: this is true for version 5.0 macro sheets, but not modules.)

MORE INFORMATION
Microsoft Excel uses the #N/A! error value in place of missing or blank arguments in a custom function. The #N/A! error value is displayed on the worksheet in the cell that contains your custom function. You will also see the #N/A! error value in the Define Name box when you choose Define Name from the Formula menu when the active document is a macro sheet. In the Define Name dialog box, the error value is displayed in the Refers To edit box when you select the argument name in the Names In Sheet list.

Workaround
When arguments in a custom function are missing or left blank, use a macro similar to the following example to suppress the #N/A! error value. With this macro, if any of the arguments in your custom function are missing or left blank, the function returns a blank cell instead of the #N/A! error value:

A1: Concatenator A2: =ARGUMENT(&quot;first_number&quot;,1) A3: =ARGUMENT(&quot;text_string&quot;,2) A4: result=first_number&text_string A5: =IF(OR((ISNA(GET.NAME(first_number))),(ISNA(GET.NAME (text_string)))),SET.NAME(&quot;result&quot;,&quot;&quot;),result) A6: =RETURN(result) If you reference the custom function on a worksheet, it returns a blank cell instead of the #N/A! error value.

For example:

A1: =Macro1!Concatenator(,&quot;text&quot;)

-or-

A1: =Macro1!Concatenator(1,) In both of these examples, cell A1 displays blank rather than a #N/A!.

The following information describes the function macro shown above:

A1: Name of the custom function macro. A2: Defines the first argument as a number. A3: Defines the second argument as a text string. A4: Defines the name &quot;result&quot; as the concatenation of the two

arguments. For example, if 1 is the number and &quot;A&quot; is the text string being passed to the function, then &quot;result&quot; would refer to   refer to &quot;1A&quot;. A5: The IF statement checks to see if &quot;first_number&quot; or

&quot;text_string&quot; refers to #N/A!. If either of these names contains #N/A!, the variable named &quot;result&quot; is set to blank (&quot;&quot;). GET.NAME is used to get the definition of the defined argument names &quot;first_number&quot; and &quot;text_string&quot;. A6: Returns the information stored in the variable &quot;result&quot; to the

worksheet. Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.