SeriesCollection Object Issue

[Ronald R. Dodge, Jr.]

If the SeriesCollection Object on the chart has all values contained within
it as "#N/A", then the object effectively becomes invisible from the code
and when attempting to set the values to it again via code, it errors out as
the code can't see the object on the Chart Object (Note, Chart Object isn't
the same object as the ChartObject Object).

However, on the spreadsheet side, one can clearly go into the DataSource of
the chart and see it there. What needs to be done to get this issue
resolved as I must still be able to print out the charts, even if at least
one of the series contains all "#N/A" values, which is needed for
interpolated charts to work properly.

For the time being, I'm having to do this manually, and I don't like that
idea.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Peter T]

If the SeriesCollection Object on the chart has all values contained
within

it as "#N/A", then the object effectively becomes invisible from the code

In what way. What object effectively becomes invisible. If you mean the
series, sure it's not immediately visible to the user but it can be selected
(arrow keys), and not invisible from the code.

Note, Chart Object isn't
the same object as the ChartObject Object

Are you talking about something like this

set chtObj = ActiveSheet.ChartObjects(1)
Set cht = chtObj.Chart

I just tried various ways to reference the series, its values, and source
without any problem, even if all values in the series are #N/A, and then
assigning a new source range for the series.

Regards,
Peter T

 

[Andy Pope]

Hi,

It depends on the chart type.
I created this single line by changing the source data for a column chart.

ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R6C2"

No problem whether the range B2:B6 contains values, #N/As or is empty.

Change the chart type to Line and try using all empty or #N/A data and
it will raise a error 1004.

If your chart is not too complex you can change the type in order to
manipulate it and the return the chart type to that you want.

Cheers
Andy

 

[Peter T]

It depends on the chart type.

Ah of course, I should have remembered that!

Regards,
Peter T

 

[Ronald R. Dodge, Jr.]

This is the line that it fails on:

m_chtProductionRateLineChart.Chart.SeriesCollection("1st").Values =
m_str1stShiftProductionRateConstants

When the series contains the array of:

#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A

The Series object is no longer visible within the Watch window and the code
for some reason can not see it, but yet, even with the code in Break mode on
the line above, I can perform the following steps:

Within the Intermediate Window, type in:

?m_str1stShiftProductionRateConstants

And when I press the "Enter" key at the end of the line, it returns the
values within the string variable. I then go into the DataSource of the
chart on the spreadsheet side, and paste the data in between the curly
brackets, which then as long as it's not full of the "#N/A" values, the code
will move on after I click on the Run button within VB Editor.

Also note, if all series within the SeriesCollection has all values as
"#N/A", the SeriesCollection collection isn't even visible within the Watch
Window.

I hate the idea of comparing rather if a variable contains such a string,
and if so, then delete the series from the chart (if it's not already), but
if it doesn't, check to be sure the series is in the chart, and if it's not,
then add the series to the chart.

This would mean I would have to set a lot more properties in Run-Time mode
rather then depending on this sort of stuff being set in Design-Time mode.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

Bare in mind, I'm not assigning a range, but rather constants to the Values
property. Reason being, the plan is to eventually replicate a copy of the
worksheet from the process file to the work center file, but then the code
would then remove all external defined names from the work center file.

If it contained just a range name, that wouldn't have been an issue, but
it's assigning constants.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

So which chart type(s) would it still see such a series?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Jon Peltier]

Andy gave the example as a column chart. Essentially those charts without
the capability to display values as markers seem to treat #N/A as zero.

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

So which chart type(s) would it still see such a series?

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Jon Peltier]

It doesn't matter if the unplottable values are in a range or in an array.

Have you done the proof of concept with all values loaded into the chart as
arrays? If not, check it out before spending too much time on your final
approach. There is a limit to how long an array can be to work as the source
data for a chart series, and the limit is in the number of characters it
takes to represent the array as a string, surrounded with curly braces and
separated with commas. The limit is around 250 or so characters.

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

Bare in mind, I'm not assigning a range, but rather constants to the
Values property. Reason being, the plan is to eventually replicate a copy
of the worksheet from the process file to the work center file, but then
the code would then remove all external defined names from the work center
file.

If it contained just a range name, that wouldn't have been an issue, but
it's assigning constants.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Ronald R. Dodge, Jr.]

I'm not close to that limit, as far as the 255 character limit is concerned.
However, cause of the manual work that I was doing, I had been thinking
about truncating those values down to the significant 4 digits (an
Accounting rule), then this way, I don't have mutliple lines of data to copy
from the Intermediate window either. There would be, 49 characters at most
for any one data series given 8 points potentially to plot rather than the
currently 137 characters potentially to plot. No plottable point would reach
5 digit figures [to the left of the decimal point] given current capacities
of work centers.

Thank you for those that mentioned about the chart type as that resolved the
issue. I ended up using the following snippet of code:

m_chtSetupAverageTimeLineChart.Chart.ChartType = xlColumnClustered
m_chtSetupAverageTimeLineChart.Chart.SeriesCollection("1st").Values = _
m_str1stShiftAverageSetupTimeConstants
m_chtSetupAverageTimeLineChart.Chart.SeriesCollection("3rd").Values = _
m_str3rdShiftAverageSetupTimeConstants
m_chtSetupAverageTimeLineChart.Chart.ChartType = xlLineMarkers

While I may not know any one particular area fully inside out, I'm
integrating a lot of different areas into each other including those items
outside of MS Office. Some of these things came up as a result of other
issues that I have been resolving, which I been working on making some of my
older codes more dynamic and modulated, thus I also have gotten quite deep
into Class Coding too.

What brought on me making the adjustments that had worked for years prior?
Let's just say corporate did some things that broke my reports and it's was
even to the point that I can't really rely on formulas to do certain things,
and the only way to carry the dynamicness is to use range names and refer to
them within VBA, which then that brought on some other issues along the way,
such as having to use VBA coding to manage those names.
--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

It doesn't matter if the unplottable values are in a range or in an array.

Have you done the proof of concept with all values loaded into the chart
as arrays? If not, check it out before spending too much time on your
final approach. There is a limit to how long an array can be to work as
the source data for a chart series, and the limit is in the number of
characters it takes to represent the array as a string, surrounded with
curly braces and separated with commas. The limit is around 250 or so
characters.

- Jon