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

"Mitch" <(E-Mail Removed)> wrote in message
news:E4BF41EE-5D16-4619-A45A-(E-Mail Removed)...
> 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.
>

An explanation of the problem is given here:

http://peltiertech.com/Excel/ChartsH...ChartData.html

My advice is to drop the data into the sheet after processing it, then chart
from this range.

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


"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> 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
>
> "Mitch" <(E-Mail Removed)> wrote in message
> news:E4BF41EE-5D16-4619-A45A-(E-Mail Removed)...
>> 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.
>>
>
>

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.

"Jon Peltier" wrote:

> An explanation of the problem is given here:
>
> http://peltiertech.com/Excel/ChartsH...ChartData.html
>
> My advice is to drop the data into the sheet after processing it, then chart
> from this range.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > 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
> >
> > "Mitch" <(E-Mail Removed)> wrote in message
> > news:E4BF41EE-5D16-4619-A45A-(E-Mail Removed)...
> >> 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.
> >>
> >
> >
>
>
>

> the 144 character limit

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

Regards,
Peter T

"Mitch" <(E-Mail Removed)> wrote in message
news356588D-AF6D-4045-AD45-(E-Mail Removed)...
> 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.
>
> "Jon Peltier" wrote:
>
> > An explanation of the problem is given here:
> >
> > http://peltiertech.com/Excel/ChartsH...ChartData.html
> >
> > My advice is to drop the data into the sheet after processing it, then
chart
> > from this range.
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. - http://PeltierTech.com
> > _______
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:%(E-Mail Removed)...
> > > 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
> > >
> > > "Mitch" <(E-Mail Removed)> wrote in message
> > > news:E4BF41EE-5D16-4619-A45A-(E-Mail Removed)...
> > >> 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.
> > >>
> > >
> > >
> >
> >
> >

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

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


"Mitch" <(E-Mail Removed)> wrote in message
news356588D-AF6D-4045-AD45-(E-Mail Removed)...
> 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.
>
> "Jon Peltier" wrote:
>
>> An explanation of the problem is given here:
>>
>> http://peltiertech.com/Excel/ChartsH...ChartData.html
>>
>> My advice is to drop the data into the sheet after processing it, then
>> chart
>> from this range.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%(E-Mail Removed)...
>> > 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
>> >
>> > "Mitch" <(E-Mail Removed)> wrote in message
>> > news:E4BF41EE-5D16-4619-A45A-(E-Mail Removed)...
>> >> 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.
>> >>
>> >
>> >
>>
>>
>>