Asp Forum - Re: Dynamic Chart Building - "Unable to set the ... property of the Se

Jon Peltier

12/19/2006 5:07:00 PM

What do these strings look like? Are they valid for this use?
strPrefix & SeriesStartRow & "C" & SeriesColumn
strPrefix & SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow & "C"
& XValueColumn
strPrefix & SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C" &
DataColumn

What kind of chart is it? What is the initial source data? Valid numbers and
labels, or blanks, or what?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"Dale Fye" <dale.fye@nospam.com> wrote in message
news:03E638A9-7F49-4835-8972-8DB016B3F049@microsoft.com...
>I have a function that loops through a dataset and dynamically builds the
> chart series for a bunch of charts. 95% of the time, this function works
> properly, but occassionally, I get an error that reads "Unable to set the
> .... property of the Series class", where the ... represents Name,
> XValues,
> and Values.
>
> Some of the sheet names contain spaces, so I encapsulated them in single
> quotes, which seems to work most of the time.
>
> The code that is generating the problem looks like:
>
> strPrefix = "='" & ActiveSheet.name & "'!R"
> ActiveChart.SeriesCollection(SeriesCount).Name = strPrefix &
> SeriesStartRow
> & "C" & SeriesColumn
>
> ActiveChart.SeriesCollection(SeriesCount).XValues = strPrefix &
> SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow & "C" &
> XValueColumn
>
> ActiveChart.SeriesCollection(SeriesCount).Values = strPrefix &
> SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C" & DataColumn
>
>
> --
> Email address is not valid.
> Please reply to newsgroup only.

3 Answers

Dale Fye

12/19/2006 6:04:00 PM

Jon

Love your site. Have been using it a lot lately to get charting ideas.

This particular function is formatting stacked bar charts.

An example of these property values is :

....Name = "='AC_losses_pct'!R617C4"
...XValues = "='AC_losses_pct'!R617C6:R633C6"
...Values = "='AC_losses_pct'!R617C7:R633C7"

The way my code is written, after selecting a chart, it deletes all of the
data series except the first one (1). I'm keeping that one , because it
appears that the other chart properties (X and Y axis formatting) get lost if
I delete all of the data series.

After posting this message, I went back into the worksheet, and reset each
of the charts so that their first data series was working properly, and
deleted all of the other series from each chart. When I re-ran the function,
It worked fine. I think the error may have to do with an invalid reference
in the first data series on a chart.

Thanks, would appreciate any of your insights.

--
Email address is not valid.
Please reply to newsgroup only.

"Jon Peltier" wrote:

> What do these strings look like? Are they valid for this use?
> strPrefix & SeriesStartRow & "C" & SeriesColumn
> strPrefix & SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow & "C"
> & XValueColumn
> strPrefix & SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C" &
> DataColumn
>
> What kind of chart is it? What is the initial source data? Valid numbers and
> labels, or blanks, or what?
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://Pelti...
> _______
>
>
> "Dale Fye" <dale.fye@nospam.com> wrote in message
> news:03E638A9-7F49-4835-8972-8DB016B3F049@microsoft.com...
> >I have a function that loops through a dataset and dynamically builds the
> > chart series for a bunch of charts. 95% of the time, this function works
> > properly, but occassionally, I get an error that reads "Unable to set the
> > .... property of the Series class", where the ... represents Name,
> > XValues,
> > and Values.
> >
> > Some of the sheet names contain spaces, so I encapsulated them in single
> > quotes, which seems to work most of the time.
> >
> > The code that is generating the problem looks like:
> >
> > strPrefix = "='" & ActiveSheet.name & "'!R"
> > ActiveChart.SeriesCollection(SeriesCount).Name = strPrefix &
> > SeriesStartRow
> > & "C" & SeriesColumn
> >
> > ActiveChart.SeriesCollection(SeriesCount).XValues = strPrefix &
> > SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow & "C" &
> > XValueColumn
> >
> > ActiveChart.SeriesCollection(SeriesCount).Values = strPrefix &
> > SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C" & DataColumn
> >
> >
> > --
> > Email address is not valid.
> > Please reply to newsgroup only.
>
>
>

Jon Peltier

12/20/2006 12:13:00 AM

> I think the error may have to do with an invalid reference
> in the first data series on a chart.

This is what I was thinking. If you may get this kind of problem, you could
temporarily change the chart type to area or column (which are more tolerant
of bad data than XY or line types), but this may disturb your axis layout.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"Dale Fye" <dale.fye@nospam.com> wrote in message
news:E3B4F33A-6511-4023-A8EF-9FE374D31236@microsoft.com...
> Jon
>
> Love your site. Have been using it a lot lately to get charting ideas.
>
> This particular function is formatting stacked bar charts.
>
> An example of these property values is :
>
> ...Name = "='AC_losses_pct'!R617C4"
> ..XValues = "='AC_losses_pct'!R617C6:R633C6"
> ..Values = "='AC_losses_pct'!R617C7:R633C7"
>
> The way my code is written, after selecting a chart, it deletes all of the
> data series except the first one (1). I'm keeping that one , because it
> appears that the other chart properties (X and Y axis formatting) get lost
> if
> I delete all of the data series.
>
> After posting this message, I went back into the worksheet, and reset each
> of the charts so that their first data series was working properly, and
> deleted all of the other series from each chart. When I re-ran the
> function,
> It worked fine. I think the error may have to do with an invalid
> reference
> in the first data series on a chart.
>
> Thanks, would appreciate any of your insights.
>
>
> --
> Email address is not valid.
> Please reply to newsgroup only.
>
>
> "Jon Peltier" wrote:
>
>> What do these strings look like? Are they valid for this use?
>> strPrefix & SeriesStartRow & "C" & SeriesColumn
>> strPrefix & SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow &
>> "C"
>> & XValueColumn
>> strPrefix & SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C"
>> &
>> DataColumn
>>
>> What kind of chart is it? What is the initial source data? Valid numbers
>> and
>> labels, or blanks, or what?
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://Pelti...
>> _______
>>
>>
>> "Dale Fye" <dale.fye@nospam.com> wrote in message
>> news:03E638A9-7F49-4835-8972-8DB016B3F049@microsoft.com...
>> >I have a function that loops through a dataset and dynamically builds
>> >the
>> > chart series for a bunch of charts. 95% of the time, this function
>> > works
>> > properly, but occassionally, I get an error that reads "Unable to set
>> > the
>> > .... property of the Series class", where the ... represents Name,
>> > XValues,
>> > and Values.
>> >
>> > Some of the sheet names contain spaces, so I encapsulated them in
>> > single
>> > quotes, which seems to work most of the time.
>> >
>> > The code that is generating the problem looks like:
>> >
>> > strPrefix = "='" & ActiveSheet.name & "'!R"
>> > ActiveChart.SeriesCollection(SeriesCount).Name = strPrefix &
>> > SeriesStartRow
>> > & "C" & SeriesColumn
>> >
>> > ActiveChart.SeriesCollection(SeriesCount).XValues = strPrefix &
>> > SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow & "C" &
>> > XValueColumn
>> >
>> > ActiveChart.SeriesCollection(SeriesCount).Values = strPrefix &
>> > SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C" &
>> > DataColumn
>> >
>> >
>> > --
>> > Email address is not valid.
>> > Please reply to newsgroup only.
>>
>>
>>

Dale Fye

12/20/2006 12:41:00 PM

Jon,

I think I'll try dropping all of the data series, and then reformat the axis
at the end of the process for each chart.

Thanks for your help.
--
Email address is not valid.
Please reply to newsgroup only.

"Jon Peltier" wrote:

> > I think the error may have to do with an invalid reference
> > in the first data series on a chart.
>
> This is what I was thinking. If you may get this kind of problem, you could
> temporarily change the chart type to area or column (which are more tolerant
> of bad data than XY or line types), but this may disturb your axis layout.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://Pelti...
> _______
>
>
> "Dale Fye" <dale.fye@nospam.com> wrote in message
> news:E3B4F33A-6511-4023-A8EF-9FE374D31236@microsoft.com...
> > Jon
> >
> > Love your site. Have been using it a lot lately to get charting ideas.
> >
> > This particular function is formatting stacked bar charts.
> >
> > An example of these property values is :
> >
> > ...Name = "='AC_losses_pct'!R617C4"
> > ..XValues = "='AC_losses_pct'!R617C6:R633C6"
> > ..Values = "='AC_losses_pct'!R617C7:R633C7"
> >
> > The way my code is written, after selecting a chart, it deletes all of the
> > data series except the first one (1). I'm keeping that one , because it
> > appears that the other chart properties (X and Y axis formatting) get lost
> > if
> > I delete all of the data series.
> >
> > After posting this message, I went back into the worksheet, and reset each
> > of the charts so that their first data series was working properly, and
> > deleted all of the other series from each chart. When I re-ran the
> > function,
> > It worked fine. I think the error may have to do with an invalid
> > reference
> > in the first data series on a chart.
> >
> > Thanks, would appreciate any of your insights.
> >
> >
> > --
> > Email address is not valid.
> > Please reply to newsgroup only.
> >
> >
> > "Jon Peltier" wrote:
> >
> >> What do these strings look like? Are they valid for this use?
> >> strPrefix & SeriesStartRow & "C" & SeriesColumn
> >> strPrefix & SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow &
> >> "C"
> >> & XValueColumn
> >> strPrefix & SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C"
> >> &
> >> DataColumn
> >>
> >> What kind of chart is it? What is the initial source data? Valid numbers
> >> and
> >> labels, or blanks, or what?
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> http://Pelti...
> >> _______
> >>
> >>
> >> "Dale Fye" <dale.fye@nospam.com> wrote in message
> >> news:03E638A9-7F49-4835-8972-8DB016B3F049@microsoft.com...
> >> >I have a function that loops through a dataset and dynamically builds
> >> >the
> >> > chart series for a bunch of charts. 95% of the time, this function
> >> > works
> >> > properly, but occassionally, I get an error that reads "Unable to set
> >> > the
> >> > .... property of the Series class", where the ... represents Name,
> >> > XValues,
> >> > and Values.
> >> >
> >> > Some of the sheet names contain spaces, so I encapsulated them in
> >> > single
> >> > quotes, which seems to work most of the time.
> >> >
> >> > The code that is generating the problem looks like:
> >> >
> >> > strPrefix = "='" & ActiveSheet.name & "'!R"
> >> > ActiveChart.SeriesCollection(SeriesCount).Name = strPrefix &
> >> > SeriesStartRow
> >> > & "C" & SeriesColumn
> >> >
> >> > ActiveChart.SeriesCollection(SeriesCount).XValues = strPrefix &
> >> > SeriesStartRow & "C" & XValueColumn & ":R" & SeriesEndRow & "C" &
> >> > XValueColumn
> >> >
> >> > ActiveChart.SeriesCollection(SeriesCount).Values = strPrefix &
> >> > SeriesStartRow & "C" & DataColumn & ":R" & SeriesEndRow & "C" &
> >> > DataColumn
> >> >
> >> >
> >> > --
> >> > Email address is not valid.
> >> > Please reply to newsgroup only.
> >>
> >>
> >>
>
>
>

microsoft.public.excel.programming

Re: Dynamic Chart Building - "Unable to set the ... property of the Se

Jon Peltier

Dale Fye

Jon Peltier

Dale Fye

x Login to ForumsZone