I've got a chart with three lines and three sets of bars on it.

viz: http://tinyurl.com/3cjjbg

Two of the bars behave as expected, being side-by-side.

The third set of bars, however, seems to sit on top of the other two instead of
next to them.

Can anybody offer up a SeriesCollection(n).Property that I could set to coerce
that one set to be side-by-side like the others?

For whatever it's worth (probably not much...),
here's the code that creates the chart:
================================================== =============================
Private Sub chart_Create(ByRef theCI() As mChartInfo, ByRef theWS As
Excel.Worksheet)
4000 debugStackPush mModuleName & ": chart_Create"
4001 On Error GoTo chart_Create_err

' PURPOSE: To create a single chart on our spreadsheet
' ACCEPTS: - Pointer to array of struct containing info about the chart we
will create
' - Pointer to the worksheet we will put the chart on

4002 Dim mySS As Excel.Application
Dim myChart As Excel.Chart
Dim mySeries_Bar() As Excel.Series
Dim mySeries_Line() As Excel.Series

Dim i As Long
Dim maxRows As Long
Dim trancheCount As Long
Dim myTitle As String

Const myTickLabelFontSize As Long = 8

' -----------------------------------------------------------
' Create chart

4030 Set mySS = theWS.Parent.Parent
4039 Set myChart = mySS.Charts.Add

' --------------------------------------------------------
' Sometimes, for unknown reasons, there may already be one or more
' series. We want to start with a clean slate, so we delete them
' before creating our own

4080 With myChart
4040 Do Until .SeriesCollection.Count = 0
4041 .SeriesCollection(1).Delete
4042 Loop

'4043 Set mySeries_Bar = .SeriesCollection.NewSeries
'4044 Set mySeries_Line = .SeriesCollection.NewSeries
4049 End With

' --------------------------------------------------------
' Figure out how many tranches we have

4050 trancheCount = UBound(theCI)

' --------------------------------------------------------
' Determine the max number of rows (i.e. payments)
' of all the tranches.
' "RowCount" is sitting at the number of the last
' row written for that tranche's section of the data grid

4060 For i = 0 To trancheCount - 1
4061 If mTI(i).RowCount maxRows Then
4062 maxRows = mTI(i).RowCount
4063 End If
4069 Next i

' --------------------------------------------------------
' Again, looping through the array,
' Add our own series and point it to our data

4100 For i = 0 To trancheCount - 1
4101 ReDim Preserve mySeries_Bar(i)
4109 Set mySeries_Bar(i) = myChart.SeriesCollection.NewSeries

4110 With mySeries_Bar(i)
4113 .ChartType = xlColumnClustered
4111 .XValues = "='" & theWS.Name & "'!" & theCI(i).RangeAddress_XLabels
4112 .Name = theCI(i).CellAddress_Bar_Name
4114 .Values = "='" & theWS.Name & "'!" & theCI(i).RangeAddress_Bar_Values
4119 End With

4120 ReDim Preserve mySeries_Line(i)
4129 Set mySeries_Line(i) = myChart.SeriesCollection.NewSeries

4130 With mySeries_Line(i)
4133 .ChartType = xlColumnClustered 'xlLineMarkers
4131 .XValues = "='" & theWS.Name & "'!" & theCI(i).RangeAddress_XLabels
4132 .Name = theCI(i).CellAddress_Line_Name
4134 .Values = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_Line_Values
4139 End With
4199 Next i

' --------------------------------------------------------
' Create an invisible text box that we use to pass on the rowcount
' to charts_Arrange - which needs to grow the width of the chart depending on
' now many payments need to be shown without the labels piling up

4210 With myChart
4211 .Shapes.AddTextbox(gExcelConstant_TextOrientation_ Horizontal, 0, 0, 100,
15).Name = mLit_PaymentCount
4212 With .Shapes(mLit_PaymentCount)
4213 .TextFrame.Characters.Text = maxRows
4214 .Visible = False
4215 End With
4219 End With

' --------------------------------------------------------
' Coerce the chart type to a custom format
' NB: For some reason, this and the following prop settings
' work only AFTER we have set up the series...

4310 With myChart
4319 .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2
Axes"

4320 .HasLegend = True
4321 With .Legend
'4212 .Width = theWS.ChartObjects(1).Width
4323 .Position = xlBottom
4324 .Border.LineStyle = xlNone
4329 End With

4330 .HasTitle = True
4339 .ChartTitle.Characters.Text = theCI(0).Title

4340 .Axes(xlValue, xlPrimary).HasTitle = True
4341 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
mLit_AxisTitle_PctNotional 'Looks like this one is the bars

4342 .Axes(xlValue, xlSecondary).HasTitle = True
4343 .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
mLit_AxisTitle_CumNotional 'Looks like this one is the lines

4344 .Axes(xlCategory).HasTitle = True
4345 .Axes(xlCategory).AxisTitle.Characters.Text = mLit_AxistTitle_PaymentNum
'This is the value stream along the bottom: the x-axis labels?

4349 .ChartGroups(1).GapWidth = 300 'Make bars thinner
4399 End With

4400 For i = 0 To trancheCount - 1
4409 Next i

' --------------------------------------------------------
' Coerce the chart's subtypes to line/bar
' Again, the order in which this is done counts: it does not
' work until we have done the .ApplyCustomType

4410 For i = 0 To trancheCount - 1
4420 With mySeries_Bar(i)
4421 .ChartType = xlColumnClustered
4422 .Border.ColorIndex = mTI(i).BarColor_Outline
4423 .Interior.ColorIndex = mTI(i).BarColor_Body
4429 End With

4430 With mySeries_Line(i)
4431 .ChartType = xlLineMarkers
4432 .MarkerBackgroundColorIndex = mTI(i).LineMarkerColor_Body
4433 .MarkerForegroundColorIndex = mTI(i).LineMarkerColor_Outline
4434 .MarkerStyle = mTI(i).LineMarkerShape
4435 .Smooth = True
4436 .MarkerSize = 7
4439 .Shadow = False
4440 With .Border
4441 .ColorIndex = mTI(i).LineColor_Line
4442 .Weight = xlMedium
4443 .LineStyle = xlContinuous
4449 End With
4450 End With
4499 Next i

4998 myChart.Location Whe=xlLocationAsObject, Name:=theWS.Name

chart_Create_xit:
DebugStackPop
On Error Resume Next
For i = 0 To trancheCount - 1
Set mySeries_Bar(i) = Nothing
Set mySeries_Line(i) = Nothing
Next i
Set myChart = Nothing
Exit Sub

chart_Create_err:
' With theCTFI_Array(theEntityTypeID, theFundID)
' BugAlert True, "ChartTitle='" & theChartTitle & "', ValueColumnOffset='" &
theDataRangeValueColumnOffset & "', FundName='" & .FundName & "', DataSheet='" &
..DataSheetName & "', DataRange='" & .DataRangeAddress & "'."
' End With
BugAlert True, "i='" & i & "'."
Resume chart_Create_xit
End Sub
================================================== ==================================
--
PeteCresswell

Your description sounds like one of the column series is on the opposite
axis from the other two. Instead of using this kind of chart type:

..ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"

just make a regular column (or line) chart, then series by series, change
half to the other type, and if needed, put some onto the secondary axis.
This gives you better control over how each series is displayed.

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


"(PeteCresswell)" wrote in message
...
I've got a chart with three lines and three sets of bars on it.

viz: http://tinyurl.com/3cjjbg

Two of the bars behave as expected, being side-by-side.

The third set of bars, however, seems to sit on top of the other two
instead of
next to them.

Can anybody offer up a SeriesCollection(n).Property that I could set to
coerce
that one set to be side-by-side like the others?

For whatever it's worth (probably not much...),
here's the code that creates the chart:
================================================== =============================
Private Sub chart_Create(ByRef theCI() As mChartInfo, ByRef theWS As
Excel.Worksheet)
4000 debugStackPush mModuleName & ": chart_Create"
4001 On Error GoTo chart_Create_err

' PURPOSE: To create a single chart on our spreadsheet
' ACCEPTS: - Pointer to array of struct containing info about the chart
we
will create
' - Pointer to the worksheet we will put the chart on

4002 Dim mySS As Excel.Application
Dim myChart As Excel.Chart
Dim mySeries_Bar() As Excel.Series
Dim mySeries_Line() As Excel.Series

Dim i As Long
Dim maxRows As Long
Dim trancheCount As Long
Dim myTitle As String

Const myTickLabelFontSize As Long = 8

' -----------------------------------------------------------
' Create chart

4030 Set mySS = theWS.Parent.Parent
4039 Set myChart = mySS.Charts.Add

' --------------------------------------------------------
' Sometimes, for unknown reasons, there may already be one or more
' series. We want to start with a clean slate, so we delete them
' before creating our own

4080 With myChart
4040 Do Until .SeriesCollection.Count = 0
4041 .SeriesCollection(1).Delete
4042 Loop

'4043 Set mySeries_Bar = .SeriesCollection.NewSeries
'4044 Set mySeries_Line = .SeriesCollection.NewSeries
4049 End With

' --------------------------------------------------------
' Figure out how many tranches we have

4050 trancheCount = UBound(theCI)

' --------------------------------------------------------
' Determine the max number of rows (i.e. payments)
' of all the tranches.
' "RowCount" is sitting at the number of the last
' row written for that tranche's section of the data grid

4060 For i = 0 To trancheCount - 1
4061 If mTI(i).RowCount maxRows Then
4062 maxRows = mTI(i).RowCount
4063 End If
4069 Next i

' --------------------------------------------------------
' Again, looping through the array,
' Add our own series and point it to our data

4100 For i = 0 To trancheCount - 1
4101 ReDim Preserve mySeries_Bar(i)
4109 Set mySeries_Bar(i) = myChart.SeriesCollection.NewSeries

4110 With mySeries_Bar(i)
4113 .ChartType = xlColumnClustered
4111 .XValues = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_XLabels
4112 .Name = theCI(i).CellAddress_Bar_Name
4114 .Values = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_Bar_Values
4119 End With

4120 ReDim Preserve mySeries_Line(i)
4129 Set mySeries_Line(i) = myChart.SeriesCollection.NewSeries

4130 With mySeries_Line(i)
4133 .ChartType = xlColumnClustered 'xlLineMarkers
4131 .XValues = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_XLabels
4132 .Name = theCI(i).CellAddress_Line_Name
4134 .Values = "='" & theWS.Name & "'!" &
theCI(i).RangeAddress_Line_Values
4139 End With
4199 Next i

' --------------------------------------------------------
' Create an invisible text box that we use to pass on the rowcount
' to charts_Arrange - which needs to grow the width of the chart
depending on
' now many payments need to be shown without the labels piling up

4210 With myChart
4211 .Shapes.AddTextbox(gExcelConstant_TextOrientation_ Horizontal, 0,
0, 100,
15).Name = mLit_PaymentCount
4212 With .Shapes(mLit_PaymentCount)
4213 .TextFrame.Characters.Text = maxRows
4214 .Visible = False
4215 End With
4219 End With

' --------------------------------------------------------
' Coerce the chart type to a custom format
' NB: For some reason, this and the following prop settings
' work only AFTER we have set up the series...

4310 With myChart
4319 .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on
2
Axes"

4320 .HasLegend = True
4321 With .Legend
'4212 .Width = theWS.ChartObjects(1).Width
4323 .Position = xlBottom
4324 .Border.LineStyle = xlNone
4329 End With

4330 .HasTitle = True
4339 .ChartTitle.Characters.Text = theCI(0).Title

4340 .Axes(xlValue, xlPrimary).HasTitle = True
4341 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
mLit_AxisTitle_PctNotional 'Looks like this one is the bars

4342 .Axes(xlValue, xlSecondary).HasTitle = True
4343 .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
mLit_AxisTitle_CumNotional 'Looks like this one is the lines

4344 .Axes(xlCategory).HasTitle = True
4345 .Axes(xlCategory).AxisTitle.Characters.Text =
mLit_AxistTitle_PaymentNum
'This is the value stream along the bottom: the x-axis labels?

4349 .ChartGroups(1).GapWidth = 300 'Make bars thinner
4399 End With

4400 For i = 0 To trancheCount - 1
4409 Next i

' --------------------------------------------------------
' Coerce the chart's subtypes to line/bar
' Again, the order in which this is done counts: it does not
' work until we have done the .ApplyCustomType

4410 For i = 0 To trancheCount - 1
4420 With mySeries_Bar(i)
4421 .ChartType = xlColumnClustered
4422 .Border.ColorIndex = mTI(i).BarColor_Outline
4423 .Interior.ColorIndex = mTI(i).BarColor_Body
4429 End With

4430 With mySeries_Line(i)
4431 .ChartType = xlLineMarkers
4432 .MarkerBackgroundColorIndex = mTI(i).LineMarkerColor_Body
4433 .MarkerForegroundColorIndex = mTI(i).LineMarkerColor_Outline
4434 .MarkerStyle = mTI(i).LineMarkerShape
4435 .Smooth = True
4436 .MarkerSize = 7
4439 .Shadow = False
4440 With .Border
4441 .ColorIndex = mTI(i).LineColor_Line
4442 .Weight = xlMedium
4443 .LineStyle = xlContinuous
4449 End With
4450 End With
4499 Next i

4998 myChart.Location Whe=xlLocationAsObject, Name:=theWS.Name

chart_Create_xit:
DebugStackPop
On Error Resume Next
For i = 0 To trancheCount - 1
Set mySeries_Bar(i) = Nothing
Set mySeries_Line(i) = Nothing
Next i
Set myChart = Nothing
Exit Sub

chart_Create_err:
' With theCTFI_Array(theEntityTypeID, theFundID)
' BugAlert True, "ChartTitle='" & theChartTitle & "',
ValueColumnOffset='" &
theDataRangeValueColumnOffset & "', FundName='" & .FundName & "',
DataSheet='" &
.DataSheetName & "', DataRange='" & .DataRangeAddress & "'."
' End With
BugAlert True, "i='" & i & "'."
Resume chart_Create_xit
End Sub
================================================== ==================================
--
PeteCresswell

Per Jon Peltier:
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"

just make a regular column (or line) chart, then series by series, change
half to the other type, and if needed, put some onto the secondary axis.
This gives you better control over how each series is displayed.

But I still have two different types of values: one instantaneous and the other
cumulative.

OTOH, I guess from the user's perspective it's moot as long as the single axes
has enough span to accommodate both. Better, maybe bc now the lines and the
bars are comparable.

Thanks.
--
PeteCresswell

Per (PeteCresswell):
But I still have two different types of values: one instantaneous and the other
cumulative.

OTOH, I guess from the user's perspective it's moot as long as the single axes
has enough span to accommodate both. Better, maybe bc now the lines and the
bars are comparable.

Going to a simple column chart and coercing the series to column or line has the
desired result.

But I still have a problem.

Turns out that the bars are for instantaneous values and the lines are for
cumulative values. After a year or two, the cumulative values will become very
large compared to the instantaneous values - so two axes really are necessary.


Sounds like the root issue is how to force a series to one axis or the other.

Anybody know what the property in question is?
--
PeteCresswell

Sounds like the root issue is how to force a series to one axis or the
other.

Anybody know what the property in question is?

mySeries.AxisGroup = xlSecondary ' or xlPrimary

At least one series (not necessarily the first) must be on the primary axis.

Regards,
Peter T



"(PeteCresswell)" wrote in message
...
Per (PeteCresswell):
But I still have two different types of values: one instantaneous and the
other
cumulative.

OTOH, I guess from the user's perspective it's moot as long as the single
axes
has enough span to accommodate both. Better, maybe bc now the lines and
the
bars are comparable.

Going to a simple column chart and coercing the series to column or line
has the
desired result.

But I still have a problem.

Turns out that the bars are for instantaneous values and the lines are for
cumulative values. After a year or two, the cumulative values will
become very
large compared to the instantaneous values - so two axes really are
necessary.


Sounds like the root issue is how to force a series to one axis or the
other.

Anybody know what the property in question is?
--
PeteCresswell

Per Peter T:
"mySeries"

Another reformed Mac Pascal programmer?

Thanks.

--
PeteCresswell