RBLNC


Hi All,

See the code snippet below for reference. I have a worksheet that contains multiple charts (ChartType = xlLineMarker) used to display trend data. The series data for each chart is dynamically reset every time the vba code is run. This reset is based on user input that is defining a date range to run view in the trend graphs. The dates are displayed on x-axis of each chart. For some date ranges the code works fine, while for others I'm get the "unable to set the xvalues property of the series class" error.

I've done quite a bit of googling and I have not found a solution. Any help is MUCH appreciated!

Additional comments:
- sChartName is an array of chart names that get updated each time (never more than 6 charts)
- iOffset is predefined variable of the type Integer. It represents the column offset where the series data is to taken from
- When the code fails, it fails on chrt.SeriesCollection(1).XValues = rng line
- I have validated that the rng object contains the correct range and values for the series (the range is small - never more than a year for a date range)
- This part makes no sense to me and may not be pertinent, but I figured I would mention in case it means something to someone else. For single month date ranges (i.e. 1 data point on the chart) the code fails consistently. For date ranges of 2 months (i.e., 2 data points), the code fails intermittently. For date ranges greater than 2 months, the code works fine. I haven't run enough tests to unequivocally state this would happen every time, but in the 20 or so times I've run the report during my troubleshooting, this has been my observation.

Again, thanks in advance for any help!

Code Snippet

Set ws = ThisWorkbook.Sheets("TrendGraphs")
ws.Activate

For i = 0 To UBound(sChartName)
ws.ChartObjects(sChartName(i)).Activate
Set chrt = ws.ChartObjects(sChartName(i)).Chart
chrt.SeriesCollection(1).XValues = rng
chrt.SeriesCollection(1).Values = rng.Offset(0, iOffset(i, 0))
If iOffset(i, 1) > 0 Then chrt.SeriesCollection(2).Values = rng.Offset(0, iOffset(i, 1))
If iOffset(i, 2) > 0 Then chrt.SeriesCollection(3).Values = rng.Offset(0, iOffset(i, 2))
If iOffset(i, 3) > 0 Then chrt.SeriesCollection(4).Values = rng.Offset(0, iOffset(i, 3))
If iOffset(i, 4) > 0 Then chrt.SeriesCollection(5).Values = rng.Offset(0, iOffset(i, 4))

Next i





Re: Setting SeriesCollection Intermittently Fails: ERR - unable to set the xvalues property of the series class

Andy Pope


Hi,

Do any of the data ranges contain empty or #N/A cells

As your problem is intermittent the code used is working so therefore the cause must be related to the data or cells being used.






Re: Setting SeriesCollection Intermittently Fails: ERR - unable to set the xvalues property of the series class

RBLNC

All the cells have valid dates in them when I'm running into the problem. I have verified this in the immediate window during run-time using the variables in the code snippet above to ensure the code is retrieving the expected cell values. So unfortunately that is not the problem in this case. :-(





Re: Setting SeriesCollection Intermittently Fails: ERR - unable to set the xvalues property of the series class

Jon Peltier

What does the series contain before you reset the range If it does not contain chartable data, then you cannot assign new data, at least to a line or XY type series. Try changing the chart type of the series temporaarily to xlArea, then changing it back:

Set ws = ThisWorkbook.Sheets("TrendGraphs")
ws.Activate

For i = 0 To UBound(sChartName)
ws.ChartObjects(sChartName(i)).Activate
Set chrt = ws.ChartObjects(sChartName(i)).Chart

chrt.SeriesCollection(1).ChartType = xlArea


chrt.SeriesCollection(1).XValues = rng
chrt.SeriesCollection(1).Values = rng.Offset(0, iOffset(i, 0))
If iOffset(i, 1) > 0 Then chrt.SeriesCollection(2).Values = rng.Offset(0, iOffset(i, 1))
If iOffset(i, 2) > 0 Then chrt.SeriesCollection(3).Values = rng.Offset(0, iOffset(i, 2))
If iOffset(i, 3) > 0 Then chrt.SeriesCollection(4).Values = rng.Offset(0, iOffset(i, 3))
If iOffset(i, 4) > 0 Then chrt.SeriesCollection(5).Values = rng.Offset(0, iOffset(i, 4))

chrt.SeriesCollection(1).ChartType = xlLineMarker


Next i

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______