Adding Another Series to a secondary Y axis with Chart Component

[Brian Hman]

I am using a Chart Office Web Component that has two series on the primary Y
axis and I need two series on the secondary (right hand) Y axis. I can add
the first series for the secondary Y Axis just fine with the following code:

dim oSeries1
Set oSeries1 = oChart.SeriesCollection.Add
oSeries1.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(7, 1)).Address
oSeries1.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,5),
oSheet.Cells(7,5)).Address
oSeries1.Caption = "Series 1 "
oSeries1.Ungroup True

dim oAxis3
Set oAxis3 = oChart.Axes.Add(oSeries1.Scalings(c.chDimValues))
oAxis3.Position = c.chAxisPositionRight
oAxis3.HasMajorGridlines = False
oAxis3.NumberFormat = "$0.00"

What doesn't seem obvious to me is how to add the second series to oAxis3.
I imagine I set up the series the same way, but then how to add it to oAxis3
isn't obvious to me. Any help is greatly appreciated!

Brian Hman



As soon as I add the second series, the scaling of the right hand

 

[Wei-Dong XU [MSFT]]

Hi Brian,

The Axis value is decided by the dimension setting at the ChAxes.Add
method. At your code, I found it is chDimValues
Set oAxis3 = oChart.Axes.Add(oSeries1.Scalings(c.chDimValues))

So the value used by the Axis will be the one in this dimension set by the
ChSeries.SetData. This is to say, the value in Axis is decided by your
series. We will need to set the data to one dimension and then specify this
to the Axis at the ChAxes.Add method.

Furthermore, please notice the dimension set in the Serious is decided by
different chart type. We will have to consider this, if not, the code will
report error for the unsupport dimension of the chart type currently used.
This article contains the meaning for each dimension:
SetData Method
http://msdn.microsoft.com/library/en-us/owcvba11/html/ocmthSetData.asp

Please feel free to let me know if you have any further question on this
matter.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of assistance.

 

[Brian Hman]

Hi Wei Dong,

I understand your reply, but I'm still not sure how to go code this. Do you
happen
to have an example of a c.chChartTypeLineMarkers possessing two series on
the right hand Y axis? The main problem I keep running into is that I can
get the two series on the right hand axis, but there each using their own
scale. That makes that chart really ugly.

Let me know if you want my entire code example.

Thanks,
Brian Hman

 

[Wei-Dong XU [MSFT]]

Hi Brian,

Now I write one sample for you on controlling the right axies scaling. We
could disable the auto magin of the right axis and then assign the max and
min value to this axis. Furthermore, we could obtain the axes from
chChart.Axes collection object; please modify them fitting your need.

If my sample doesn't satisfy your requirement, I'd like to have a test on
your code. It is suggested to paset the code in the post for benefitting
the community or send them to me in email address:
(e-mail address removed) (please remove online, used to avoid spam)
'---------------------------------------------------------------------------
--------------------------
Private Sub DrawChart()
Dim chspace As ChartSpace
Dim ch As ChChart
Dim axis As ChAxis
Dim series As ChSeries
Dim categories As String
Dim values As String

Set chspace = ChartSpace1
If chspace.Charts.Count >= 1 Then Exit Sub

Set ch = chspace.Charts.Add(0)

ch.HasLegend = True
ch.Legend.Position = chLegendPositionBottom
ch.Type = chChartTypeLineMarkers

Set series = ch.SeriesCollection.Add(0)
'-----build categories-----
categories = "Item1" & "," & "Item2" & "," & "Item3" & "," & "Item4" &
"," & "Item5" & "," & "Item6" & "," & "Item7"
'-----build values---------
values = Int(Rnd)
For i = 1 To 6
values = values & "," & CStr(Rnd * 1000)
Next
series.SetData chDimCategories, chDataLiteral, categories
series.SetData chDimValues, chDataLiteral, values

'-----second series-----------------
Set series = ch.SeriesCollection.Add(1)
'-----build values 2nd time---------
values = ""
values = CStr(Rnd * 1000)
For i = 1 To 6
values = values & "," & CStr(Rnd * 1000)
Next
series.SetData chDimCategories, chDataLiteral, categories
series.SetData chDimValues, chDataLiteral, values

Set axis = ch.Axes.Add(series.Scalings(chDimValues))
axis.HasAutoMajorUnit = False
axis.HasAutoMinorUnit = False

axis.Position = chAxisPositionRight

'specify the max and min value of this axis
axis.Scaling.Maximum = 1000
axis.Scaling.Minimum = 200

End Sub
'---------------------------------------------------------------------------
--------------------------

Please feel free to let me know if you have any further question on this
matter.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of assistance.

 

[Brian Hman]

Hi Wei-Dong,

Thank you for your continued patience. I have created an example that better
illustrates my problem and I will paste it below this message.

In this example, I want oSeries123 and oSeries124 to both be on the same
Y axis on the right hand side. This statement:
oChart.Axes.Add(oSeries123.Scalings(c.chDimValues))
ties the scale of the right hand Y axis to oSeries123. Notice that the
values in oSeries124 are quite a bit larger, but this is not reflected on the
scale. Is there a way to add this axis in such a way that the values of both
series affect the scale
of oAxis3? If you play with the values in the spreadsheet OWC you'll see
that values in the column represented by oSeries124 do not affect the scale
of the secondary Y axis--even if the values get really large.

Please let me know if you need further clarification,
Brian Hman

<OBJECT id="ChartSpace1" height="384" width="576"
classid="clsid:0002E55D-0000-0000-C000-000000000046" VIEWASTEXT><PARAM
NAME="XMLData" VALUE='<xml xmlns:x="urn:schemas-microsoft-comffice:excel">
<x:ChartSpace>
<x:OWCVersion>11.0.0.6255 </x:OWCVersion>
<x:Width>32094</x:Width>
<x:Height>11483</x:Height>
<xalette>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#000000</x:Entry>
<x:Entry>#8080FF</x:Entry>
<x:Entry>#802060</x:Entry>
<x:Entry>#FFFFA0</x:Entry>
<x:Entry>#A0E0E0</x:Entry>
<x:Entry>#600080</x:Entry>
<x:Entry>#FF8080</x:Entry>
<x:Entry>#008080</x:Entry>
<x:Entry>#C0C0FF</x:Entry>
<x:Entry>#000080</x:Entry>
<x:Entry>#FF00FF</x:Entry>
<x:Entry>#80FFFF</x:Entry>
<x:Entry>#0080FF</x:Entry>
<x:Entry>#FF8080</x:Entry>
<x:Entry>#C0FF80</x:Entry>
<x:Entry>#FFC0FF</x:Entry>
<x:Entry>#FF80FF</x:Entry>
</xalette>
<xefaultFont>Arial</xefaultFont>
</x:ChartSpace>
</xml>'><PARAM NAME="ScreenUpdating" VALUE="-1"><PARAM NAME="EnableEvents"
VALUE="-1"></OBJECT><br><OBJECT id="Spreadsheet1" height="300" width="576"
classid="CLSID:0002E559-0000-0000-C000-000000000046" VIEWASTEXT><PARAM
NAME="DataType" VALUE="XMLDATA"><PARAM NAME="AllowPropertyToolbox"
VALUE="-1"><PARAM NAME="AutoFit" VALUE="0"><PARAM NAME="Calculation"
VALUE="-4105"><PARAM NAME="Caption" VALUE="Microsoft Office
Spreadsheet"><PARAM NAME="DisplayColumnHeadings" VALUE="-1"><PARAM
NAME="DisplayGridlines" VALUE="-1"><PARAM NAME="DisplayHorizontalScrollBar"
VALUE="-1"><PARAM NAME="DisplayOfficeLogo" VALUE="-1"><PARAM
NAME="DisplayPropertyToolbox" VALUE="0"><PARAM NAME="DisplayRowHeadings"
VALUE="-1"><PARAM NAME="DisplayTitleBar" VALUE="0"><PARAM
NAME="DisplayToolbar" VALUE="-1"><PARAM NAME="DisplayVerticalScrollBar"
VALUE="-1"><PARAM NAME="DisplayWorkbookTabs" VALUE="-1"><PARAM
NAME="EnableEvents" VALUE="-1"><PARAM NAME="MaxHeight" VALUE="80%"><PARAM
NAME="MaxWidth" VALUE="80%"><PARAM NAME="MoveAfterReturn" VALUE="-1"><PARAM
NAME="MoveAfterReturnDirection" VALUE="-4121"><PARAM NAME="RightToLeft"
VALUE="0"><PARAM NAME="ScreenUpdating" VALUE="-1"><PARAM NAME="LockedDown"
VALUE="0"><PARAM NAME="ConnectedToChart" VALUE="0"><PARAM
NAME="DefaultQueryOnLoad" VALUE="-1"><PARAM NAME="EnableUndo"
VALUE="-1"></OBJECT>
<script type="text/vbscript">
Dim oSheet
Set oSheet = Spreadsheet1.ActiveSheet
oSheet.Cells.Clear
dim oChart
ChartSpace1.Clear
Set oChart = ChartSpace1.Charts.Add
' Set the Spreadsheet component as the data source for the chart
ChartSpace1.DataSource = Spreadsheet1
'Get the constants for the Chart component
dim c
set c = ChartSpace1.Constants
Dim range
'Add the data to the chart and set the series names
dim oSeries
oChart.Type = c.chChartTypeLineMarkers
oChart.HasLegend = True
oChart.Legend.Position = c.chLegendPositionBottom
oSheet.Cells(1,1).Value ="09/01/04"
oSheet.Cells(1,6).Value ="Measure1"
oSheet.Cells(1,2).Value ="1"
oSheet.Cells(1,3).Value ="2"
oSheet.Cells(1,7).Value ="Resale"
oSheet.Cells(1,4).Value ="10"
oSheet.Cells(1,5).Value ="1000"
oSheet.Cells(2,1).Value ="10/01/04"
oSheet.Cells(2,6).Value ="Measure1"
oSheet.Cells(2,2).Value ="1"
oSheet.Cells(2,3).Value ="2"
oSheet.Cells(2,7).Value ="Resale"
oSheet.Cells(2,4).Value ="10"
oSheet.Cells(2,5).Value ="1000"
oSheet.Cells(3,1).Value ="11/01/04"
oSheet.Cells(3,6).Value ="Measure1"
oSheet.Cells(3,2).Value ="1"
oSheet.Cells(3,3).Value ="2"
oSheet.Cells(3,7).Value ="Resale"
oSheet.Cells(3,4).Value ="10"
oSheet.Cells(3,5).Value ="1000"
oSheet.Cells(4,1).Value ="12/01/04"
oSheet.Cells(4,6).Value ="Measure1"
oSheet.Cells(4,2).Value ="1"
oSheet.Cells(4,3).Value ="2"
oSheet.Cells(4,7).Value ="Resale"
oSheet.Cells(4,4).Value ="0"
oSheet.Cells(4,5).Value ="0"
oSheet.Cells(5,1).Value ="01/01/05"
oSheet.Cells(5,6).Value ="Measure1"
oSheet.Cells(5,2).Value ="0"
oSheet.Cells(5,3).Value ="0"
oSheet.Cells(5,7).Value ="Resale"
oSheet.Cells(5,4).Value ="0"
oSheet.Cells(5,5).Value ="0"
oSheet.Cells(6,1).Value ="02/01/05"
oSheet.Cells(6,6).Value ="Measure1"
oSheet.Cells(6,2).Value ="0"
oSheet.Cells(6,3).Value ="0"
oSheet.Cells(6,7).Value ="Resale"
oSheet.Cells(6,4).Value ="0"
oSheet.Cells(6,5).Value ="0"
oSheet.Cells(7,1).Value ="03/01/05"
oSheet.Cells(7,6).Value ="Measure1"
oSheet.Cells(7,2).Value ="0"
oSheet.Cells(7,3).Value ="0"
oSheet.Cells(7,7).Value ="Resale"
oSheet.Cells(7,4).Value ="0"
oSheet.Cells(7,5).Value ="0"
oSheet.Cells(8,1).Value ="04/01/05"
oSheet.Cells(8,6).Value ="Measure1"
oSheet.Cells(8,2).Value ="0"
oSheet.Cells(8,3).Value ="0"
oSheet.Cells(8,7).Value ="Resale"
oSheet.Cells(8,4).Value ="0"
oSheet.Cells(8,5).Value ="0"
oSheet.Cells(9,1).Value ="05/01/05"
oSheet.Cells(9,6).Value ="Measure1"
oSheet.Cells(9,2).Value ="0"
oSheet.Cells(9,3).Value ="0"
oSheet.Cells(9,7).Value ="Resale"
oSheet.Cells(9,4).Value ="0"
oSheet.Cells(9,5).Value ="0"
oSheet.Cells(10,1).Value ="06/01/05"
oSheet.Cells(10,6).Value ="Measure1"
oSheet.Cells(10,2).Value ="0"
oSheet.Cells(10,3).Value ="0"
oSheet.Cells(10,7).Value ="Resale"
oSheet.Cells(10,4).Value ="0"
oSheet.Cells(10,5).Value ="0"
oSheet.Cells(11,1).Value ="07/01/05"
oSheet.Cells(11,6).Value ="Measure1"
oSheet.Cells(11,2).Value ="0"
oSheet.Cells(11,3).Value ="0"
oSheet.Cells(11,7).Value ="Resale"
oSheet.Cells(11,4).Value ="0"
oSheet.Cells(11,5).Value ="0"
oSheet.Cells(12,1).Value ="08/01/05"
oSheet.Cells(12,6).Value ="Measure1"
oSheet.Cells(12,2).Value ="0"
oSheet.Cells(12,3).Value ="0"
oSheet.Cells(12,7).Value ="Resale"
oSheet.Cells(12,4).Value ="0"
oSheet.Cells(12,5).Value ="0"
Set range = oSheet.Range(oSheet.Cells(1, 4), oSheet.Cells(13, 4))
range.NumberFormat = "$0.00"
Set range = oSheet.Range(oSheet.Cells(1, 5), oSheet.Cells(13, 5))
range.NumberFormat = "$0.00"
On Error Resume Next
dim oSeries121
Set oSeries121 = oChart.SeriesCollection.Add
oSeries121.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(12, 1)).Address
oSeries121.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,2),
oSheet.Cells(12,2)).Address
oSeries121.Caption = "Measure1_Wholesale "
dim oSeries122
Set oSeries122 = oChart.SeriesCollection.Add
oSeries122.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(12, 1)).Address
oSeries122.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,3),
oSheet.Cells(12,3)).Address
oSeries122.Caption = "Measure1_Retail "
dim oSeries123
Set oSeries123 = oChart.SeriesCollection.Add
oSeries123.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(12, 1)).Address
oSeries123.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,4),
oSheet.Cells(12,4)).Address
oSeries123.Caption = "Measure Value 2 "
oSeries123.Ungroup True
dim oAxis3
Set oAxis3 = oChart.Axes.Add(oSeries123.Scalings(c.chDimValues))
'Set oAxis3 = oChart.Axes.Add
oAxis3.Maximum = 3000
oAxis3.Minimum = 1
oAxis3.Position = c.chAxisPositionRight
oAxis3.HasMajorGridlines = False
oAxis3.NumberFormat = "$0.00"
oAxis3.Maximum = 5000
oAxis3.Minimum = 0
dim oSeries124
Set oSeries124 = oChart.SeriesCollection.Add
oSeries124.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(12, 1)).Address
oSeries124.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,5),
oSheet.Cells(12,5)).Address
oSeries124.Caption = "Measure Value 3 "
oSeries124.Ungroup True


dim oAxis
Set oAxis = ChartSpace1.Charts(0).Axes(c.chAxisPositionCategory)
oAxis.GroupingUnit = 1
oAxis.GroupingUnitType = c.chAxisUnitMonth
oAxis.TickLabelUnitType = c.chAxisUnitMonth
</script>

 

[Wei-Dong XU [MSFT]]

Hi Brian,

We could make oSeries123 with oSeries 124 groupped together to make them
using one scale. I modified your code as below:
'------------------------------------------------
dim oSeries123
Set oSeries123 = oChart.SeriesCollection.Add
oSeries123.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(12, 1)).Address
oSeries123.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,4),
oSheet.Cells(12,4)).Address
oSeries123.Caption = "Measure Value 2 "
oSeries123.Ungroup True
dim oSeries124
Set oSeries124 = oChart.SeriesCollection.Add
oSeries124.SetData c.chDimCategories,0, oSheet.Range(oSheet.Cells(1, 1),
oSheet.Cells(12, 1)).Address
oSeries124.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(1,5),
oSheet.Cells(12,5)).Address
oSeries124.Caption = "Measure Value 3 "
oSeries124.Ungroup True
' group 123 with 124
oSeries123.Group oSeries124

dim oAxis3
Set oAxis3 = oChart.Axes.Add(oSeries123.Scalings(c.chDimValues))
oAxis3.Position = c.chAxisPositionRight
oAxis3.HasMajorGridlines = False
oAxis3.HasAutoMajorUnit = False
oAxis3.HasAutoMinorUnit = False
oAxis3.NumberFormat = "$0.00"
'------------------------------------------------

The last line Group method combines the scale of 124 with 123. However,
since series 124 has a very large value than series 123, the scale in the
right side will appear too crowded to be black lines, which is really very
ugly. So from my view, it is the best way to create one new chart for the
Series124 which will make the chart very easy to use.

Please feel free to let me know if you have any further question on this
matter.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of assistance.

 

[Brian Hman]

Hi Wei-Dong,

Thank you for your reply. The Ungroup command is exactly what I needed.

Brian Hman

 

[Wei-Dong XU [MSFT]]

You are very welcome! :^)

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of assistance.