XL2000: Error Programmatically Accessing Data Series on a Chart (213379)
The information in this article applies to:
This article was previously published under Q213379 SYMPTOMS
In Microsoft Excel 2000, when you run a macro that attempts to access a data
series on a chart, you may receive the following error message:
Run-time error '1004':
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, then 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 behavior, use the appropriate method.
Method 1: All Y-Values Are Empty
To make the series visible on the chart, follow these steps:
- On the Tools menu, click Options.
- Click the Chart tab. Under Plot empty cells 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
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.
STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Modification Type: | Major | Last Reviewed: | 11/5/2003 |
---|
Keywords: | kbbug kberrmsg kbpending kbProgramming KB213379 |
---|
|