PDA

View Full Version : Solved: Help with Chart Lines



RSTools413
02-08-2008, 04:24 PM
I am creating an XYScatter plot from several series of data. I would like the "raw" series to be markers only, and the "smoothed" series to be lines, no marker. I would like the line of the matching series to be the same color.

(I am currently working in Excel 2007, but would like a solution that will also work in earlier versions.

Current code:
-----
Set sSerRaw = cGraph.SeriesCollection.NewSeries
sSerRaw.Name = sBuildSeriesName(rRaw.Column, wS)
sSerRaw.Values = rRaw
sSerRaw.XValues = rDateVals
sSerRaw.Type = xlXYScatter
sSerRaw.ChartType = xlXYScatter

Set sSerSmooth = cGraph.SeriesCollection.NewSeries
sSerSmooth.Name = sBuildSeriesName(rSmooth.Column, wS)
sSerSmooth.Values = rSmooth
sSerSmooth.XValues = rDateVals
sSerSmooth.Type = xlXYScatter
sSerSmooth.ChartType = xlXYScatterLinesNoMarkers
sSerSmooth.MarkerStyle = xlMarkerStyleNone
sSerSmooth.Format.Line.ForeColor.RGB = sSerRaw.Format.Line.ForeColor.RGB
sSerSmooth.Format.Line.BackColor.RGB = sSerRaw.Format.Line.BackColor.RGB
------

What is happening currently is each series is appearing in the default colors.

Thanks -
Bill

shades
02-09-2008, 06:18 AM
Howdy and welcome to the board.

In your posted code, you have set the colors of the two series to be the same (which happens to be default), but you have not set the colors for the individual series.

RSTools413
02-09-2008, 08:52 AM
Hi - first - thanks for the reply :)

I do want the colors of th two series (rSerRaw and rSerSmooth) to be the same. The idea is to plot the raw data with markers only (no lines), and the transformed data as a continuous line, but no markers. The result should look to the viewer as though they belong together. (The explanatory image that just sprang into my mind is imagine this before we had color - only black & white.)

I eventually will want to plot several data sets on the same axis, with each pair of sets having their own color - but, at the moment, succeeding with just the pair will be great!

I don't understand quite what you mean by "you have not set the colors for the individual series." That is what I was attempting to accomplish with:

sSerSmooth.Format.Line.ForeColor.RGB = SerRaw.Format.Line.ForeColor.RGB
sSerSmooth.Format.Line.BackColor.RGB = SerRaw.Format.Line.BackColor.RGB

Am I invoking the wrong property?

Again, thanks; and further help will be appreciated.
Bill

Bob Phillips
02-09-2008, 09:09 AM
Isn't it just



sSerSmooth.Border.ColorIndex = sSerRaw.Border.ColorIndex

JonPeltier
02-10-2008, 10:02 AM
ColorIndex may not be reliable when using XL2007, and in 2003 and earlier, if the series are using their defaults, .ColorIndex = xlDefault. Better to explicitly state the color:

With ActiveChart.SeriesCollection(1)
.MarkerBackgroundColor = RGB(0, 0, 255)
.MarkerForegroundColor = RGB(0, 0, 255)
.Border.LineStyle = xlNone
End With
With ActiveChart.SeriesCollection(2)
.Border.Color = RGB(0, 0, 255)
.MarkerStyle = xlNone
.Smooth = True
End With

Note: in 2003 and earlier you may not get exactly the color your RGB specifies, you'll get an element of the color palette which Excel thinks most closely matches the RGB. But at least the two series will be the same color.

RSTools413
02-11-2008, 04:18 PM
The answers here definitely helped!

Jon was correct - the color index values were xlDefault. In addition, a number of other aspects of the series values seem to be at default values. I had been attempting to let Excel set the color for the first series, and then pick that up & use it for my smoothed one. However, I am now explicitly assigning the color to each series, using an internal table to provide the color value for each set of series.


sSerRaw.MarkerForegroundColor = lColorToUseForSeries(lColNum)
sSerRaw.MarkerBackgroundColor = lColorToUseForSeries(lColNum)

sSerSmooth.Border.Color = lColorToUseForSeries(lColNum)


Thank you all very much for your help!