XL5: NAMES() Returns #N/A If Active Sheet Is Chart or Module

ID: Q112179

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SUMMARY

In Microsoft Excel, if you use the NAMES() function in a Microsoft Excel version 4.0 macro to return defined names on a workbook, the function returns the #N/A error value if the active sheet in the workbook is a chart or a module, even if the active workbook does contain workbook-level ("global") defined names.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

WORKAROUND

To avoid having the NAMES() function return the #N/A error value when the active workbook does contain workbook-level defined names, use the WORKBOOK.SELECT() function in your macro to select a worksheet in the workbook before you use the NAMES() function, as in the following example:


   A1: Count_Names
   A2: =WORKBOOK.SELECT("Sheet1","Sheet1")
   A3: =IF(ISNA(INDEX(NAMES(,3),1)))
   A4: =  ALERT("No names defined")
   A5: =  RETURN()
   A6: =END.IF()
   A7: =ALERT(COLUMNS(NAMES(,3)))
   A8: =RETURN()

The above macro selects Sheet1 in the active workbook, uses the NAMES() function to count the number of workbook-level defined names in the workbook and sheet-level defined names on Sheet1, and then it displays the number in a message. Because you would usually define a name to run the macro, the macro usually displays at least 1 in the message.

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. This macro is provided as is and Microsoft in no way guaranties that the following code can be used in all situations and will not support modifications of the code to suit specific customer requirements.

Note that this problem does not occur when you use the Count property of the Names object, or the Name property of the Name object in a Microsoft Visual Basic Programming System, Applications Edition module. However, the Count property of the Names object and the Name property of the Name object includes both sheet-level and book-level names from the workbook.

MORE INFORMATION

You can use the NAMES() function in a Microsoft Excel version 4.0 macro to return workbook-level defined names in a specified workbook as a horizontal array of text. Sheet-level names of only the active worksheet are also included in the array.

REFERENCES

For more information about NAMES, choose the Search button in the Microsoft Excel Macro Functions Help and type:

NAMES function

Additional query words:

Keywords :
Version : 5.00
Platform : WINDOWS
Issue type :