Excel VBA Chart With Multiple Series But Same X Value

Question

I'm trying to put together a VB script in Excel which creates a 'Stacked Bar Chart'.

From examples found in this site, I did the following code:

Sub AddNewSeries()
    ActiveSheet.Shapes.AddChart2(297, xlBarStacked).Select
    With ActiveChart.SeriesCollection.NewSeries
        .Name = ActiveSheet.Range("$E$3")
        .Values = ActiveSheet.Range("IFPStart")
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$G$4")
        .Values = ActiveSheet.Range("IFPBC")
        .XValues = ActiveSheet.Range("IFPPN")
         .Name = ActiveSheet.Range("$H$4")
        .Values = ActiveSheet.Range("IFPBT")
        .XValues = ActiveSheet.Range("IFPPN")
         .Name = ActiveSheet.Range("$I$4")
        .Values = ActiveSheet.Range("IFPEC")
        .XValues = ActiveSheet.Range("IFPPN")
         .Name = ActiveSheet.Range("$J$4")
        .Values = ActiveSheet.Range("IFPFin")
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$K$4")
        .Values = ActiveSheet.Range("IFPIMS")
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$L$4")
        .Values = ActiveSheet.Range("IFPInf")
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$M$4")
        .Values = ActiveSheet.Range("IFPPT")
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$N$4")
        .Values = ActiveSheet.Range("IFPSols")
        .XValues = ActiveSheet.Range("IFPPN")
    End With
End Sub

You'll see that rather than using cell references, I use named ranges because the data will be dynamic.

The code does run without any errors displayed, but the last series used i.e.

.Name = ActiveSheet.Range("$N$4")

overwrites the previously plotted data.

What am I doing wrong?

Floris · Accepted Answer · 2013-12-31 16:51:08Z

3

You are not adding the series one at at time. You are adding one series, and keep changing (overwriting) the properties of that series. Thus you end up with just one series, with the properties that you specified.

The following might work better:

Sub AddNewSeries()
    ActiveSheet.Shapes.AddChart2(297, xlBarStacked).Select
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$E$3")
        .Values = ActiveSheet.Range("IFPStart")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$G$4")
        .Values = ActiveSheet.Range("IFPBC")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
         .Name = ActiveSheet.Range("$H$4")
        .Values = ActiveSheet.Range("IFPBT")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
         .Name = ActiveSheet.Range("$I$4")
        .Values = ActiveSheet.Range("IFPEC")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
         .Name = ActiveSheet.Range("$J$4")
        .Values = ActiveSheet.Range("IFPFin")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$K$4")
        .Values = ActiveSheet.Range("IFPIMS")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$L$4")
        .Values = ActiveSheet.Range("IFPInf")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$M$4")
        .Values = ActiveSheet.Range("IFPPT")
    End With
    With ActiveChart.SeriesCollection.NewSeries
        .XValues = ActiveSheet.Range("IFPPN")
        .Name = ActiveSheet.Range("$N$4")
        .Values = ActiveSheet.Range("IFPSols")
    End With
End Sub

It is possible that you need a

ActiveChart.SeriesCollection(1).Delete

Right after the AddChart2 statement - on my machine, it tried to "guess" a series and got it very very wrong… but the above removed it and left me just the "good" series.

Also - I found that on the Mac, I could not use AddChart2 but had to use AddChart instead. Not sure if that matters for you.

edited Dec 31, 2013 at 16:51

answered Dec 31, 2013 at 16:38

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IRHM Over a year ago

Hi @Floris, thank you for taking the time to reply to my post. Your solution worked great and also highlighted an error in my original recorded macro. All the best and kind regards

Jon Peltier Over a year ago

AddChart2 replaced AddChart in Excel 2013 for Windows and Excel 2016 for Mac, though for backwards compatibility you can still use AddChart. AddChart2 makes it easier to use the newer, nicer chart styles introduced in Excel 2013.

Collectives™ on Stack Overflow

Excel VBA Chart With Multiple Series But Same X Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related