PDA

View Full Version : Shorter way of putting names to SeriesCollection



chamster
09-27-2007, 01:47 AM
I have a passage where i go as follows.

ac.SeriesCollection(1).XValues = .Range(.Cells(1, 1), .Cells(9, 1))
ac.SeriesCollection(1).Name = "first"
ac.SeriesCollection(2).Name = "not so first"


Is there a way to assign the names as a whole set (array?) instead of, as shown above, going data set by data set? How?

Also, question number two. How important is it to assign .XValues to each of the data sets? It will be the same range anyway and as far i can see, it works with only one assignment.

On the other hand, once it seemed to be a good idea to use anti-pesticides. Later on they turned to cause deformations on babies. I don't want to deform my soon-to-be child by bad coding, you see... :)

rory
09-27-2007, 05:09 AM
1. Depends on your data layout - it may be easier to use SetSourceData rather than assigning data to each series.
2. Not important. Nice analogy, though...

chamster
09-27-2007, 06:44 AM
I was looking into SetSourceData but my source looks like this.
A B C sum
1 2 2 5
2 3 2 7
1 1 3 5

If i just put it in as a rectangular range, i'll get the sum/total as the last series. I want it to be the first. I can solve it by setting drawing order or whatever it's called but i wonder if there's a nicer solution.

JonPeltier
09-29-2007, 05:58 PM
I was looking into SetSourceData but my source looks like this.
A B C sum
1 2 2 5
2 3 2 7
1 1 3 5

If i just put it in as a rectangular range, i'll get the sum/total as the last series. I want it to be the first. I can solve it by setting drawing order or whatever it's called but i wonder if there's a nicer solution.

You must have noticed that the data is plotted in the order it appears in the sheet. If you don't want to mess with the chart, put Sum in the first column. If you don't want to mess with the data, you'll have to change the series order in the chart.

ActiveChart.SeriesCollection("Sum").PlotOrder = 1

JonPeltier
09-29-2007, 06:02 PM
Also, question number two. How important is it to assign .XValues to each of the data sets? It will be the same range anyway and as far i can see, it works with only one assignment.

Depends on chart type (in an XY chart, each series can have different XValues). However, for completeness I almost never skip this. If you're coding it, what's the big deal?

If you use SetSourceData, the XValues are filled in for all series.

chamster
09-30-2007, 12:17 AM
You must have noticed that the data is plotted in the order it appears in the sheet. If you don't want to mess with the chart, put Sum in the first column. If you don't want to mess with the data, you'll have to change the series order in the chart.

ActiveChart.SeriesCollection("Sum").PlotOrder = 1
I was affraid it was so. Well, in that case, i know what to do. Thank you.

One thing i wonder is how the X.PlotOrder = 1 affects the other series to be plotted. In fact, i'm most curious what happens to the series that had plot order of 1 just before the change. Is it simply updated to 2? Is that what happens to all the series of order X, so they become X + 1?

So, in short - is the update of plot order to the rest of series done automagically or do i need to do it?

chamster
09-30-2007, 12:22 AM
Depends on chart type (in an XY chart, each series can have different XValues). However, for completeness I almost never skip this. If you're coding it, what's the big deal? If you use SetSourceData, the XValues are filled in for all series.

I take that as a "yes, why not" to assigning x-values to each series, even if they are exactly the same.

Are you saying that you generally don't use SetSourceData but rather loop through and assign each series separately? Is it faster (run-time-wise)?

JonPeltier
09-30-2007, 04:22 AM
One thing i wonder is how the X.PlotOrder = 1 affects the other series to be plotted.

All the other series adjust automatically, keeping the same relative order with each other, while the designated series goes into its designated slot.


Are you saying that you generally don't use SetSourceData but rather loop through and assign each series separately? Is it faster (run-time-wise)?

I'm saying that I almost never omit the XValues of a series. You only really need to specify XValues for the first series, but if you don't set the rest, and you delete the first, or move a series without its XValues set into plot order 1, the chart will not know what to use for XValues, and may default to counting numbers 1, 2, 3, etc.

I can't imagine looping through each series is faster than SetSourceData. If my data is well structured I use SetSourceData when defining the chart. However, it is rare that my data is well structured, and looping through to set series .Name, .XValues, and .Values gives me greater control over the data in each series.

chamster
09-30-2007, 01:16 PM
Got it. Muchos thankos!