Ordering of bars in VBA charts created with ChartWizard and union()

C

CAI

Hello,

in my latest project, I have to create bar-charts using the
ChartWizard-Method in Excel VBA.

The problem is:
I add the data ranges for the chart in a certain order to the "main"
data range. I add new ranges using the union(...) function
successively. The I use chartWizard() in order to set the source data.

For Example: The names for the bars are KY,ES,JX,OC,HV,NB,FT,DR, so I
add the corresponding data ranges in that order.

BUT...the bars are not displayed in the same order that I used when
adding the data ranges. Instead, the bars are (from top to bottom) in
this order: OC,NB,KY,JX,HV,FT,ES,DR.

Obviously, they are in reverse alphabetical order, but I want them to
be ordered in my own order!

So, how do I set the source data using my own order?

My Source code for my CreateChart() sub is below.

Thanks

Malte


Sub createChart(itemLong As String, itemShort As String,
itemIn2QuestIdx As Integer, idxRotation As Integer, newWorkBook As
Workbook)
Dim rotationRange As Range
Dim conceptRange As Range
Dim foundConcept As Boolean
Dim rotationcell As Range

Dim chartRange As Range
Dim newChart As Chart
Dim neuesDiagramm As Chart

Dim currentConceptName As String
Dim foundConceptIndex As Integer
Dim i As Integer
Dim errors As Boolean

Set rotationRange = getRotationRange(idxRotation)
Set conceptRange = getConceptRange()
errors = False


'alle Konzepte in Rotation durchlaufen
For Each rotationcell In rotationRange

(...determine chartRange...)

If chartRange Is Nothing Then
Set chartRange =
getDataRangeForConceptItem(foundConceptIndex, itemIn2QuestIdx,
newWorkBook, errors)
Else
Set chartRange = Union(chartRange,
getDataRangeForConceptItem(foundConceptIndex, itemIn2QuestIdx,
newWorkBook, errors))
End If
Else
End If
Else
MsgBox "Für die gewählte Rotation (" &
frmRotations.lbxRotations.Value & ") kann keine Entsprechung in der
Liste aller Konzepte gefunden werden.", , "Fehler!"
errors = True
End If
End If
Next 'nächstes Konzept der Rotation

If Not errors Then
'an dieser Stelle besitzt chartRange die notwendigen
Diagrammbereiche für Item Nr. itemIn2QuestIdx
If newWorkBook.Charts.count = 0 Then
Set newChart = newWorkBook.Charts.Add()
Else
Set newChart =
newWorkBook.Charts.Add(after:=newWorkBook.Charts(newWorkBook.Charts.count))
End If



'Chart-Eigenschaften festlegen...
newChart.Name = itemShort

'Set newChart = newWorkBook.Charts(newWorkBook.Charts.count)

newChart.ChartWizard chartRange, xlBar, , xlRows, 1, 0, True,
itemLong

newChart.Name = itemShort
newChart.SeriesCollection(1).Name = getLegendDescrAverage()
newChart.SeriesCollection(2).Name = getLegendDescrStdev()

newChart.Legend.Font.Name = "Arial"
newChart.Legend.Font.Size = 12

newChart.ChartTitle.Font.Size = 18

'X-Achse
newChart.Axes(xlValue).MaximumScale = getXAxisMaxValue()
newChart.Axes(xlValue).MinimumScale = getXAxisMinValue()
newChart.Axes(xlValue).MinorUnitIsAuto = False
newChart.Axes(xlValue).MajorUnitIsAuto = False
newChart.Axes(xlValue).HasMajorGridlines = True
newChart.Axes(xlValue).HasMinorGridlines = False
newChart.Axes(xlValue).CrossesAt = 0

'Y-Achse
newChart.Axes(xlCategory).HasMajorGridlines = False
newChart.Axes(xlCategory).HasMinorGridlines = False
'newChart.Axes(xlCategory).ReversePlotOrder = True
newChart.Axes(xlCategory).Crosses = xlMinimum
newChart.Axes(xlCategory).TickLabelSpacing = 1
newChart.Axes(xlCategory).TickMarkSpacing = 1
newChart.Axes(xlCategory).AxisBetweenCategories = True

newChart.SizeWithWindow = True

newChart.PlotArea.Fill.ForeColor.SchemeColor = 2
newChart.PlotArea.Fill.BackColor.SchemeColor = 15
newChart.PlotArea.Fill.TwoColorGradient msoGradientHorizontal,
1

End If
End Sub
 
J

Jon Peltier

It doesn't matter what order the areas are added to construct the range.
When Excel creates the chart from the range, it moves from top to bottom or
left to right, creating series in order.

Create a chart with no series, then one by one add the series using a
methodology like this:

With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With

For more information, refer to this web page:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

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


Hello,

in my latest project, I have to create bar-charts using the
ChartWizard-Method in Excel VBA.

The problem is:
I add the data ranges for the chart in a certain order to the "main"
data range. I add new ranges using the union(...) function
successively. The I use chartWizard() in order to set the source data.

For Example: The names for the bars are KY,ES,JX,OC,HV,NB,FT,DR, so I
add the corresponding data ranges in that order.

BUT...the bars are not displayed in the same order that I used when
adding the data ranges. Instead, the bars are (from top to bottom) in
this order: OC,NB,KY,JX,HV,FT,ES,DR.

Obviously, they are in reverse alphabetical order, but I want them to
be ordered in my own order!

So, how do I set the source data using my own order?

My Source code for my CreateChart() sub is below.

Thanks

Malte


Sub createChart(itemLong As String, itemShort As String,
itemIn2QuestIdx As Integer, idxRotation As Integer, newWorkBook As
Workbook)
Dim rotationRange As Range
Dim conceptRange As Range
Dim foundConcept As Boolean
Dim rotationcell As Range

Dim chartRange As Range
Dim newChart As Chart
Dim neuesDiagramm As Chart

Dim currentConceptName As String
Dim foundConceptIndex As Integer
Dim i As Integer
Dim errors As Boolean

Set rotationRange = getRotationRange(idxRotation)
Set conceptRange = getConceptRange()
errors = False


'alle Konzepte in Rotation durchlaufen
For Each rotationcell In rotationRange

(...determine chartRange...)

If chartRange Is Nothing Then
Set chartRange =
getDataRangeForConceptItem(foundConceptIndex, itemIn2QuestIdx,
newWorkBook, errors)
Else
Set chartRange = Union(chartRange,
getDataRangeForConceptItem(foundConceptIndex, itemIn2QuestIdx,
newWorkBook, errors))
End If
Else
End If
Else
MsgBox "Für die gewählte Rotation (" &
frmRotations.lbxRotations.Value & ") kann keine Entsprechung in der
Liste aller Konzepte gefunden werden.", , "Fehler!"
errors = True
End If
End If
Next 'nächstes Konzept der Rotation

If Not errors Then
'an dieser Stelle besitzt chartRange die notwendigen
Diagrammbereiche für Item Nr. itemIn2QuestIdx
If newWorkBook.Charts.count = 0 Then
Set newChart = newWorkBook.Charts.Add()
Else
Set newChart =
newWorkBook.Charts.Add(after:=newWorkBook.Charts(newWorkBook.Charts.count))
End If



'Chart-Eigenschaften festlegen...
newChart.Name = itemShort

'Set newChart = newWorkBook.Charts(newWorkBook.Charts.count)

newChart.ChartWizard chartRange, xlBar, , xlRows, 1, 0, True,
itemLong

newChart.Name = itemShort
newChart.SeriesCollection(1).Name = getLegendDescrAverage()
newChart.SeriesCollection(2).Name = getLegendDescrStdev()

newChart.Legend.Font.Name = "Arial"
newChart.Legend.Font.Size = 12

newChart.ChartTitle.Font.Size = 18

'X-Achse
newChart.Axes(xlValue).MaximumScale = getXAxisMaxValue()
newChart.Axes(xlValue).MinimumScale = getXAxisMinValue()
newChart.Axes(xlValue).MinorUnitIsAuto = False
newChart.Axes(xlValue).MajorUnitIsAuto = False
newChart.Axes(xlValue).HasMajorGridlines = True
newChart.Axes(xlValue).HasMinorGridlines = False
newChart.Axes(xlValue).CrossesAt = 0

'Y-Achse
newChart.Axes(xlCategory).HasMajorGridlines = False
newChart.Axes(xlCategory).HasMinorGridlines = False
'newChart.Axes(xlCategory).ReversePlotOrder = True
newChart.Axes(xlCategory).Crosses = xlMinimum
newChart.Axes(xlCategory).TickLabelSpacing = 1
newChart.Axes(xlCategory).TickMarkSpacing = 1
newChart.Axes(xlCategory).AxisBetweenCategories = True

newChart.SizeWithWindow = True

newChart.PlotArea.Fill.ForeColor.SchemeColor = 2
newChart.PlotArea.Fill.BackColor.SchemeColor = 15
newChart.PlotArea.Fill.TwoColorGradient msoGradientHorizontal,
1

End If
End Sub
 
C

CAI

Hallo Jon,

that's a very good hint, thank you!

Ok now I'm able to create a new series. I need two series, they are
called "Average" and "Standard Deviation".

The problem is that I cannot add all the values for each series at
once. I have to do it in a loop.

So, here's my next question: How do I add data/value two a series that
already exists?

--> For example, I have the series "seriesMittelwert". How do I append
values to seriesMittelwert.Values ?

The only solution that I can think of is to collect all values in an
array first and in the end I use something like
NewSeries(collectedData).

Thanks

Malte
 
J

Jon Peltier

You could use this syntax to add to an existing series (recorded while I
copied cells, used Paste Special to add to the chart as new points):

ActiveSheet.Range("C13:C14").Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=False, _
CategoryLabels:=False, Replace:=False, NewSeries:=False

Or you could use the Extend method of the Series object. From help:

Extend Method

Adds new data points to an existing series collection. Variant



expression.Extend(Source, Rowcol, CategoryLabels)



expression Required. An expression that returns a SeriesCollection
object.



Source Required Variant. The new data to be added to the SeriesCollection
object, either as a Range object or an array of data points.



Rowcol Optional Variant. Ignored if Source is an array. Specifies whether
the new values are in the rows or columns of the given range source. Can be
one of the following XlRowCol constants: xlRows or xlColumns. If this
argument is omitted, Microsoft Excel attempts to determine where the values
are by the size and orientation of the selected range or by the dimensions
of the array.



CategoryLabels Optional Variant. Ignored if Source is an array. True to
have the first row or column contain the name of the category labels. False
to have the first row or column contain the first data point of the series.
If this argument is omitted, Microsoft Excel attempts to determine the
location of the category label from the contents of the first row or column.



Example

This example extends the series on Chart1 by adding the data in cells B1:B6
on Sheet1.



Charts("Chart1").SeriesCollection.Extend _
Source:=Worksheets("Sheet1").Range("B1:B6")
- Jon
 
C

CAI

Hello Jon,

it's long time ago :)

I have now solved the problem by re-ordering the data on which the
charts are based. I could not change the ordering of the series by
adding the data in different orders to the chart, no way.

Thanks for your help!

Malte
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extra series in charts when created using vba 1
Using VBA to create and format Chart 7
Help setting the gridlines on a XY Scatter chart with vba 2
Multiple Bars: How To Make Them All Side-By-Side? 9
Charting in VBA problem 1
Excel Charts & VBA 0
Chart Automation leaves hidden instance of Excel running 2
Problem w/Chart Axis labeling with VBA 0

Top