Chart Seriew Values

  • Thread starter Thread starter Mitch
  • Start date Start date
M

Mitch

I have an existing macro that charts user selected data after perfroming a
few 'conditioning' functions. The macro works for low numbers of data points
but does not work for series' with large numbers of data points (30 for
example). The data range is read from any Excel workbook into a memory array
named thisDataSet. 'thisDataSet' is redimensioned earlier in the macro
following elimination of unwanted data points. That part produces the
correct array size and the correct data points. I use the following code to
populate the chart series values:
Charts.Add
ActiveChart.SeriesCollection(1).Values = thisDataSet
When the macro does not work it always stops at the
ActiveChart.SeriesCollection line and gives the message:
Run-time error '1004': Unable to set the Values property of the Series
class.
Any suggestions will be appreciated.
 
What is "thisDataSet"

If it's a horizontal array that'll end up writing values to the series
formula it'll fail when that section of the string tries to exceed 255
characters, including the curly brackets and commas. There is a different
approach but quite a lot of work.

Regards,
Peter T
 
Your suggestion does correct the problem. I accidentally found the reason
for the problem. When creating a chart from a memory array Excel tries to
fill in the data in the form ={34.03874,36.28374,37.48762.... and so on. If
the selection contains too many characters is exceeds the 144 character limit
for the line and returns the error. If the array is written to the worksheet
first the the chart is created the chart line has only the range reference
which is well within the 144 character limit. This might mean that the
problem of writing directly from a memory array to a chart might not exist at
all in Excel 2007.
 
the 144 character limit

You should be able to get nearer to 255, though perhaps not quite

Regards,
Peter T
 
As far as I could tell, 2007 and 2003 have the same behavior. And the limit
is actually closer to 255.

- Jon
 
Back
Top