Multiple Bars: How To Make Them All Side-By-Side?

[(PeteCresswell)]

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 Where:=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
====================================================================================

 

[Jon Peltier]

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

 

[(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?

 

[Peter T]

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)]

Per Peter T:

"mySeries"

Another reformed Mac Pascal programmer?

Thanks.

 

[Jon Peltier]

When I was coding for myself, I used variables like mySeries and myChart all
the time. Now that I'm doing it for other folks, those names seem rather
informal, so I have to think of things like chtROI, srsAssumptionA, and the
like. Don't want the clients to think I'm still an amateur!

- Jon

 

[Peter T]

Reformed - an on going process!

I don't think I've ever used mySeries or myChart in my own work (well
obviously).
I don't know the origin of 'my' but pretty sure it pre-dates Mac and
possibly Pascal.

I trust in context it was pretty obvious that mySeries clearly referred to a
Series object of 'your' choosing, and not me being overly possessive about
one belonging to me.

Regards,
Peter T

 

[Jon Peltier]

I picked up the my- prefix from forums such as these (I've never done
Pascal). More recently in my posts I use names like theChart, thisChart,
thatChart, newChart, oldChart, in an attempt to be descriptive. In my own
coding I use descriptive prefixes like cht-, ws-, wb- and so forth, not as a
strict Hungarian approach as followed by some programming prescriptivists,
but more in the sense of Simonyi's original intent, to help the coder
remember what the hell he's thinking. For example, I don't use i- for
integer or l- for long. I use mostly longs anyway, because I read somewhere
that the performance difference between longs and ints isn't so much, my
programs have more delays waiting for the user to select an option than in
calculations, and I hate having to redeclare my ints as longs if I decide
I'll allow the user to use all rows of his own worksheet. I use i- for
something that increments, n- for a count of that something, sometimes o-
for an initial value, and l- for other kinds of numbers:

For iNumber = oNumber to nNumber Step lNumber

See, it makes good sense, and I remember what it means.

- Jon

 

[Peter T]

Not sure how we got onto the subject of notation, I'll blame the other
Peter!

See, it makes good sense, and I remember what it means.

Yes that's key, and also to make sense to any one else who needs to
understand. I pretty much follow your naming of objects, but I prefix all
number variables with an "n". If not a long followed by b, s or d for byte,
single, double. I hate the "l" prefix (ie small L) !

more in the sense of Simonyi's original intent

As I recall he named his notation Hungarian as a joke, describing it as so
confusing it might as well be Greek, and hence named after his nationality.

Regards,
Peter T