How to force series into a new ChartGroups in VBA so I can turn off HiLoLines for charttype xlStockHLC

[steve harris]

I'm using EXCEL 2007

I want to overlay a line graph over a stock chart so that the line graph is not connected to the HiLoLines of the Stock Chart. It seems to me putting the line graph data into a new ChartGroup object would allow me to do what I need. Here's the code I've tested so far:

Sub gen_stock_chart()
'
' gen_stock_chart Macro
'
Dim i As Long

'
Range("D1:F32").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$D$1:$F$32")
ActiveChart.ChartType = xlStockHLC

series1 = ActiveChart.SeriesCollection(1).Values
series2 = ActiveChart.SeriesCollection(2).Values
series3 = ActiveChart.SeriesCollection(3).Values


For i = 1 To ActiveChart.SeriesCollection(1).Points.Count
ActiveChart.SeriesCollection(1).Points(i).MarkerStyle = xlMarkerStyleDash
ActiveChart.SeriesCollection(1).Points(i).MarkerBackgroundColor = RGB(128, 128, 128) 'gray
ActiveChart.SeriesCollection(1).Points(i).MarkerForegroundColor = RGB(128, 128, 128) 'gray

ActiveChart.SeriesCollection(2).Points(i).MarkerStyle = xlMarkerStyleDash
ActiveChart.SeriesCollection(2).Points(i).MarkerBackgroundColor = RGB(128, 128, 128) 'gray
ActiveChart.SeriesCollection(2).Points(i).MarkerForegroundColor = RGB(128, 128, 128) 'gray

ActiveChart.SeriesCollection(3).Points(i).MarkerStyle = xlMarkerStyleCircle
ActiveChart.SeriesCollection(3).Points(i).MarkerSize = 4#
ActiveChart.SeriesCollection(3).Points(i).MarkerBackgroundColor = RGB(255, 0, 255) 'purple
ActiveChart.SeriesCollection(3).Points(i).MarkerForegroundColor = RGB(255, 0, 255) 'purple
Next

' This is where the problem lies... using the same ChartGroup connects series4 points with the same line as for the other series.
' Default is ChartGroups(1).HasHiLoLines = TRUE. I turned it FALSE and ALL the lines went away.
' If I could force series4 into ChartGroups(2) I could turn off HiLoLines just for that series.
' Is there a way to do that? Everything I've tried has failed. (All the code here works.)

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "='Sheet1'!$C$1"
ActiveChart.SeriesCollection(4).Values = "='Sheet1'!$C$2:$C$32"

series4 = ActiveChart.SeriesCollection(4).Values

For i = 1 To ActiveChart.SeriesCollection(1).Points.Count
ActiveChart.SeriesCollection(4).Points(i).MarkerStyle = xlMarkerStyleCircle
If series4(i) < series2(i) Or series4(i) > series1(i) Then 'I want these points dis-connected!!!
ActiveChart.SeriesCollection(4).Points(i).MarkerBackgroundColor = RGB(255, 0, 0) 'red
ActiveChart.SeriesCollection(4).Points(i).MarkerForegroundColor = RGB(255, 0, 0) 'red
Else
ActiveChart.SeriesCollection(4).Points(i).MarkerBackgroundColor = RGB(0, 255, 0) 'green
ActiveChart.SeriesCollection(4).Points(i).MarkerForegroundColor = RGB(0, 255, 0) 'green
End If
Next


End Sub

 

[steve harris]

Re: How to force series into a new ChartGroups in VBA so I can turn off HiLoLines for charttype xlStockHLC ***SOLVED***

Problem Solved. I found a doc by Jon Peltier that addressed what I was trying to do. Turns out that in Excel 2007 it's not possible to add a line graph to a stock chart. Jon's suggestion is to create an XY-scatterplot with my dynamic data, manually add HiLoLines, remove the series line segments, then add a line graph to handle the series that I want disconnected from the HiLoLines of the other series. I followed his directions while recording a macro, fine-tuned it, and have what I need. It took me a few days' experimenting to figure out that I needed the .border option set to xlNone to get rid of that little link I was trying to remove in the first place.

Here's the link to Jon's doc: Stock Charts in Excel 2007 | Peltier Tech Blog | Excel Charts

Here's my working code:

Sub create_ci_chart()
'
' create_ci_chart Macro
'

Dim i As Long

'
Range("C1:F32").Select
ActiveSheet.Shapes.AddChart.Select

chartname = ActiveChart.Parent.Name

ActiveChart.SetSourceData Source:=Range("'Sheet1'!$C$1:$F$200")
ActiveChart.ChartType = xlLine 'chartgroups(1)
ActiveSheet.ChartObjects(chartname).Activate

With ActiveChart
.SetElement (msoElementLineHiLoLine)
.SetElement (msoElementChartTitleAboveChart)
.HasTitle = True
.ChartTitle.Text = "Confidence Intervals"
.ChartTitle.Characters.Font.Color = RGB(0, 0, 128) 'light blue
End With

With ActiveChart.ChartGroups(1)
.GapWidth = 10
End With

'MsgBox ("chartgroup: " & ActiveChart.ChartGroups.Count) 'shows 1

ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).ChartType = xlXYScatterSmooth 'chartgroups(2) no hi-lo lines

'MsgBox ("chartgroup: " & ActiveChart.ChartGroups.Count) 'shows 2

series1 = ActiveChart.SeriesCollection(1).Values
series2 = ActiveChart.SeriesCollection(2).Values
series3 = ActiveChart.SeriesCollection(3).Values
series4 = ActiveChart.SeriesCollection(4).Values

ActiveSheet.ChartObjects(chartname).Activate
ActiveChart.SeriesCollection(1).Select
With Selection
.Border.ColorIndex = xlNone
.MarkerStyle = xlMarkerStyleDash
.MarkerSize = 5
.MarkerBackgroundColor = RGB(0, 0, 128) 'light blue gray RGB(128, 128, 128)
.MarkerForegroundColor = RGB(0, 0, 128) 'light blue
End With

ActiveSheet.ChartObjects(chartname).Activate
ActiveChart.SeriesCollection(2).Select
With Selection
.Border.ColorIndex = xlNone
.MarkerStyle = xlMarkerStyleDash
.MarkerSize = 5
.MarkerBackgroundColor = RGB(0, 0, 128) 'light blue
.MarkerForegroundColor = RGB(0, 0, 128) 'light blue
End With

ActiveSheet.ChartObjects(chartname).Activate
ActiveChart.SeriesCollection(3).Select
With Selection
.Border.ColorIndex = xlNone
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 5
.MarkerBackgroundColor = RGB(128, 0, 128) 'light purple
.MarkerForegroundColor = RGB(128, 0, 128) 'light purple
End With

ActiveSheet.ChartObjects(chartname).Activate
ActiveChart.SeriesCollection(4).Select
With Selection
.Border.Color = RGB(0, 255, 0) 'light green
.Border.Weight = xlMedium

For i = 1 To ActiveChart.SeriesCollection(4).Points.Count

If series4(i) < series2(i) Or series4(i) > series1(i) Then
ActiveChart.SeriesCollection(4).Points(i).MarkerStyle = xlMarkerStyleCircle
ActiveChart.SeriesCollection(4).Points(i).MarkerSize = 5
ActiveChart.SeriesCollection(4).Points(i).MarkerBackgroundColor = RGB(255, 0, 0) 'red conf interval doesn't include the mean
ActiveChart.SeriesCollection(4).Points(i).MarkerForegroundColor = RGB(255, 0, 0) 'red
Else
ActiveChart.SeriesCollection(4).Points(i).MarkerStyle = xlMarkerStyleDash
ActiveChart.SeriesCollection(4).Points(i).MarkerSize = 3
ActiveChart.SeriesCollection(4).Points(i).MarkerBackgroundColor = RGB(0, 255, 0) 'green
ActiveChart.SeriesCollection(4).Points(i).MarkerForegroundColor = RGB(0, 255, 0) 'green
End If
Next

End With

ActiveChart.ChartArea.Select
ActiveSheet.Shapes(1).ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleWidth 2, msoFalse, msoScaleFromTopLeft ' widen graph for looks

ActiveSheet.Range("A1").Select
End Sub

I'm creating a chart of confidence intervals to demo to my classes, emphasizing the Central Limit Theorem.

(Don't have permissions to post a screen shot of the resulting chart.)


Thanks, Jon! You saved my sanity!