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

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

how to create chart by VBA coding? 6
Adding Chart - Methods Fail 1
Create a dynamic chart with code 1
Macro problems with range and naming issues 1
Help: runtime error "method seriescollection object_chart failed" 1
Excel VBA - Chart title problem 2
Excel macro to loop through worksheets and graph data from each worksheet 5
Excel 2000 Chart & VBA: why oh why?!? 2

Top