XL: Error Programmatically Accessing Data Series on Chart

ID: Q139327

The information in this article applies to:

Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, when you run a macro that attempts to access a data series in a chart, you may receive any of the following error messages:

Microsoft Excel 97 for Windows and Excel 98 Macintosh Edition

Run-time error '1004':
Unable to get the Name property of the Series class.

Microsoft Excel 5.0 or 7.0

Run-time error '1006':
Unable to get the Name property of the Series class.

You may also receive the following error message:

Run-time error '1005':
Unable to set the Value property of the Series class.

CAUSE

This problem occurs when you run a macro that attempts to access a data series that is not visible on a chart, and the chart is a line, xy (scatter), or radar series chart.

The error message appears when the data series is not visible because it has an empty set of y-axis values or has #N/A for all y-axis values. The error that you receive depends on which method you apply to the series or which property you set or return in the macro.

If you click Options on the Tools menu, click the Chart tab, and then click

"Not Plotted (leave gaps)", all y-axis values are empty. A macro that accesses the series generates a run-time error.

If all the y-axis values for the series are #N/A, a run-time error appears regardless of the Options dialog box settings.

WORKAROUND

To work around this problem, use one of the following appropriate methods.

Method 1: All Y-Values Are Empty

To work around this problem, use the following steps to make the series visible on the chart:

On the Tools menu, click Options.

In Excel 97 and Excel 98, click the Chart tab. Under Plot Empty Cells As, verify that the Zero check box is selected, and then click OK.

In Microsoft Excel 5.0 or 7.0, click the Chart tab. Under Empty Cells Plotted As, verify that the Zero check box is selected, and then click OK.

With the Zero check box selected, empty cells are plotted as zeros.

Method 2: All Y-Values Are #N/A

To work around this behavior, use a formula in the y-axis value cells that returns blank data instead of an error. For example, if the #N/A is the result of the following formula:


   =VLOOKUP(C1,D2:E5,2)

modify the formula to the following:


   =IF(ISERROR(VLOOKUP(C1,D2:E5,2)),"",VLOOKUP(C1,D2:E5,2))

This causes any errors (#N/A, #REF!, and so on) to be returned as blanks. Data points that contain blanks for the y-axis values are displayed as zeros on the chart, and the series is visible. The run-time error does not appear when you access the series programmatically.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: 5.00a 5.00c 7.00a xl97 xl98

Keywords : kberrmsg kbprg kbdta PgmOthr KbVBA xlchart
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbbug