Scatter chart series not modifiable from VBA code

Not even a clue, anybody?

If you have questions about my somewhat long-winded and no doubt
confusion post, please fire away...

--Tian:what



--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html

Okay, what was your post? Apparently your news client never passed it to
the rest of usenet.

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

TianVanHeerden wrote:
Not even a clue, anybody?

If you have questions about my somewhat long-winded and no doubt
confusion post, please fire away...

--Tian:what



--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html

In addition to Jon's comment, I visited the mcse.ms link you posted for
your original message. The message includes line breaks in the middle
of words! Makes it painful to read. You might want to consider some
way of proper line wrapping (break between words not within a word).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , TianVanHeerden.19sli2
@mail.mcse.ms says...

Not even a clue, anybody?

If you have questions about my somewhat long-winded and no doubt
confusion post, please fire away...

--Tian:what



--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html

The OP might consider a more reliable web interface for this forum, such
as http://www.ExcelForum.com/. Or use a newsreader (Outlook Express,
Netscape, etc.) and access the news server msnews.microsoft.com directly.

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

Tushar Mehta wrote:

In addition to Jon's comment, I visited the mcse.ms link you posted for
your original message. The message includes line breaks in the middle
of words! Makes it painful to read. You might want to consider some
way of proper line wrapping (break between words not within a word).

Hi Jon,

I posted via the MCSE usenet digest site, perhaps it wasn't forwarded
to usenet because it had a zip attachment. You can read the original
post here (and look at my sample):

http://www.mcse.ms/message873389.html

After you've waded through my post, I'd like to add that I think I got
a clue today when the same error occurred in a different chart (and not
of the XYScatter type) and there it seemed to be caused by missing data
points (blank cells) in the series source data range. Could that have
the effect of making the chart series inaccessible to VBA code? (Can
still do anything to the series via the Excel manual interface).

Thanks!
--Tian

Jon Peltier wrote:
*Okay, what was your post? Apparently your news client never passed
it to
the rest of usenet.

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

TianVanHeerden wrote:
Not even a clue, anybody?

If you have questions about my somewhat long-winded and no doubt
confusion post, please fire away...

--Tian:what



--
TianVanHeerden

------------------------------------------------------------------------
Posted via http://www.mcse.ms

------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html
*



--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html

Tian -

You got it. If there are not plottable points in the data range, the
series formula becomes inaccessible. This is the case for many but not
all of Excel's chart types. The workaround is to temporarily convert the
series into an Area type, access the series formula, then convert it back.

In general you don't need to submit an attachment to the newsgroups.
Your description below (I never bother to go to a web interface for a
news post, except to Google's archives) made perfect sense without one.
And in general, there aren't going to be too many issues that we haven't
already grappled with.

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

TianVanHeerden wrote:

Hi Jon,

I posted via the MCSE usenet digest site, perhaps it wasn't forwarded
to usenet because it had a zip attachment. You can read the original
post here (and look at my sample):

http://www.mcse.ms/message873389.html

After you've waded through my post, I'd like to add that I think I got
a clue today when the same error occurred in a different chart (and not
of the XYScatter type) and there it seemed to be caused by missing data
points (blank cells) in the series source data range. Could that have
the effect of making the chart series inaccessible to VBA code? (Can
still do anything to the series via the Excel manual interface).

Thanks!
--Tian

Jon Peltier wrote:

*Okay, what was your post? Apparently your news client never passed
it to
the rest of usenet.

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

TianVanHeerden wrote:

Not even a clue, anybody?

If you have questions about my somewhat long-winded and no doubt
confusion post, please fire away...

--Tian:what



--
TianVanHeerden


------------------------------------------------------------------------

Posted via http://www.mcse.ms


------------------------------------------------------------------------

View this thread: http://www.mcse.ms/message873389.html
*




--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html

Jon,

Thanks for the confirmation. I have used your workaround of changing
the chart type to Area, and this allows me to modify the series
formulae.
However, there are series properties which are not accessible while the
chart type is Area, e.g. marker details.
When I change the chart type back to XYScatter, the series is still
inaccessible because of unavoidable missing points.
This would be ok if the chart would default the series markers to
different colours/symbols. However, when you convert to/from Area type,
all the markers become black diamonds, and I can't change them
afterwards.

Could one work around this by setting the series data sources to some
trivial, one-point range which is guaranteed to have a value, modify
the properties, then restore the value ranges? Actually, don't think
that will work since each series needs TWO value ranges (.Values and
.XValues) and one of them is guaranteed to have missing points. So, as
soon as the one is set, the series object would instantly become
inaccessible.

On the topic of 'unplottable' data points: does it make any difference
whether one leaves those cells blank or use the #N/A special value?
Spaces are taken as zeros for charting which is not acceptable.

This sucks! Why hasn't Microsoft fixed this before now?

--Tian

Jon Peltier wrote:
*Tian -

You got it. If there are not plottable points in the data range, the
series formula becomes inaccessible. This is the case for many but
not
all of Excel's chart types. The workaround is to temporarily convert
the
series into an Area type, access the series formula, then convert it
back.

In general you don't need to submit an attachment to the newsgroups.
Your description below (I never bother to go to a web interface for
a
news post, except to Google's archives) made perfect sense without
one.
And in general, there aren't going to be too many issues that we
haven't
already grappled with.

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

TianVanHeerden wrote:

Hi Jon,

I posted via the MCSE usenet digest site, perhaps it wasn't
forwarded
to usenet because it had a zip attachment. You can read the
original
post here (and look at my sample):

http://www.mcse.ms/message873389.html

After you've waded through my post, I'd like to add that I think I
got
a clue today when the same error occurred in a different chart (and
not
of the XYScatter type) and there it seemed to be caused by missing
data
points (blank cells) in the series source data range. Could that
have
the effect of making the chart series inaccessible to VBA code?
(Can
still do anything to the series via the Excel manual interface).

Thanks!
--Tian

Jon Peltier wrote:





--
TianVanHeerden

------------------------------------------------------------------------
Posted via http://www.mcse.ms

------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html
*



--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html

Tian -

You could certainly change the series source to a range with dummy data
for the purposes of changing the marker formatting. I didn't realize
that the round trip to Area type changed the marker styles. You could
change the series formula to change values and xvalues in one step, a
little trickier, using string manipulation of the range addresses.

For unplottable points, leaving a true blank in the cell is better than
NA(), because a true blank has several options: interpolate, gap, zero,
while NA() only has interpolate.

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

TianVanHeerden wrote:

Jon,

Thanks for the confirmation. I have used your workaround of changing
the chart type to Area, and this allows me to modify the series
formulae.
However, there are series properties which are not accessible while the
chart type is Area, e.g. marker details.
When I change the chart type back to XYScatter, the series is still
inaccessible because of unavoidable missing points.
This would be ok if the chart would default the series markers to
different colours/symbols. However, when you convert to/from Area type,
all the markers become black diamonds, and I can't change them
afterwards.

Could one work around this by setting the series data sources to some
trivial, one-point range which is guaranteed to have a value, modify
the properties, then restore the value ranges? Actually, don't think
that will work since each series needs TWO value ranges (.Values and
.XValues) and one of them is guaranteed to have missing points. So, as
soon as the one is set, the series object would instantly become
inaccessible.

On the topic of 'unplottable' data points: does it make any difference
whether one leaves those cells blank or use the #N/A special value?
Spaces are taken as zeros for charting which is not acceptable.

This sucks! Why hasn't Microsoft fixed this before now?

--Tian

Jon Peltier wrote:

*Tian -

You got it. If there are not plottable points in the data range, the
series formula becomes inaccessible. This is the case for many but
not
all of Excel's chart types. The workaround is to temporarily convert
the
series into an Area type, access the series formula, then convert it
back.

In general you don't need to submit an attachment to the newsgroups.
Your description below (I never bother to go to a web interface for
a
news post, except to Google's archives) made perfect sense without
one.
And in general, there aren't going to be too many issues that we
haven't
already grappled with.

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

TianVanHeerden wrote:


Hi Jon,

I posted via the MCSE usenet digest site, perhaps it wasn't

forwarded

to usenet because it had a zip attachment. You can read the

original

post here (and look at my sample):

http://www.mcse.ms/message873389.html

After you've waded through my post, I'd like to add that I think I

got

a clue today when the same error occurred in a different chart (and

not

of the XYScatter type) and there it seemed to be caused by missing

data

points (blank cells) in the series source data range. Could that

have

the effect of making the chart series inaccessible to VBA code?

(Can

still do anything to the series via the Excel manual interface).

Thanks!
--Tian

Jon Peltier wrote:





--
TianVanHeerden


------------------------------------------------------------------------

Posted via http://www.mcse.ms


------------------------------------------------------------------------

View this thread: http://www.mcse.ms/message873389.html
*




--
TianVanHeerden
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message873389.html