Excel VBA: Histogram made of different series

Question

I am trying to achieve a simple task in VBA: get a histogram made of different series. As "XValues" I have strings. As "Values" I have floats.

The problem is, that when I add new series to the SeriesCollection of my xlColumnClustered chart, it does not consider the "XValues" and adds the data (columns) automatically from the top left, and the data (columns) of different series mix. This instead of adding the data of the new serie right after the data of the preceding serie.

The code I used is as follows:

Dim MyEmbeddedChart As Chart
Set MyEmbeddedChart = ActiveSheet.Shapes.AddChart.Chart
With MyEmbeddedChart

    'Data
    .ChartType = xlColumnClustered

    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "PTV = CTV + 5mm"
    .SeriesCollection(1).XValues = "=" & ActiveSheet.Name & "!" & PAi & ":" & PAf
    .SeriesCollection(1).Values = "=" & ActiveSheet.Name & "!" & Ai & ":" & Af

    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = "PTV = CTV + 7mm"
    .SeriesCollection(2).XValues = "=" & ActiveSheet.Name & "!" & PBi & ":" & PBf
    .SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!" & Bi & ":" & Bf

    .SeriesCollection.NewSeries
    .SeriesCollection(3).Name = "PTV = CTV + ?"
    .SeriesCollection(3).XValues = "=" & ActiveSheet.Name & "!" & PCi & ":" & PCf
    .SeriesCollection(3).Values = "=" & ActiveSheet.Name & "!" & Ci & ":" & Cf

    .SeriesCollection.NewSeries
    .SeriesCollection(4).Name = "PTV of pre-treated patients"
    .SeriesCollection(4).XValues = "=" & ActiveSheet.Name & "!" & PDi & ":" & PDf
    .SeriesCollection(4).Values = "=" & ActiveSheet.Name & "!" & Di & ":" & Df

    'Titles
    .HasTitle = True
    .ChartTitle.Characters.Text = "V95% of CS-PTV(%) (Patients)"
    .ChartTitle.Characters(Start:=2, Length:=3).Font.Subscript = True
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Patients"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "V95%(%) of CS-PTV(%)"
    .Axes(xlValue, xlPrimary).AxisTitle.Characters(Start:=2, Length:=3).Font.Subscript = True
    .Axes(xlCategory).HasMajorGridlines = True

    'Formatting
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = True
    .HasLegend = True

    'Position
    .ChartArea.Left = 725
    .ChartArea.Top = 70
    .ChartArea.Height = 500
    .ChartArea.Width = 750

End With

Where the local variables involved in the definition of the different series during the running time are equal to:

The sheet that I am working with and the histogram that I get look like this:

As you can see I don't want the columns belonging to different categories to mix but to appear after an other.

Has somebody an idea how I can get what I want. I thank your very much in advance.

It wont work this way. Excel chart wont fetch values of the X axis, it only fetches the names and displays them. Simple as that. To achieve your goal you will need to replicate your values row on different rows, one for each series, in a way that for each series, only the values that it displays are present, the others cells set to null. You *can* generate these rows with VBA. — A.S.H, Apr 20 '17 at 13:05
Thank you very much for your answer. I already thought about that possibility: I did it exactly the way you are suggesting and it worked fine. It's only hard to believe that Excel can't fetch those X-axis values, as you said: it seems to be a basic request and this feature would be so useful! The thing is, I feel that the solution of creating that sort of matrix filled with 0 makes the sheet a bit "dirty" and it's not really an elegant solution. Best regards, R. — rubebop, Apr 20 '17 at 13:46
FWIW, there's an alternative that avoids you the creation of those rows. You can caluclate the array of values in VBA (from the appropriate ranges) and then `series.Values = theArray`. The drawback is the values will be static, you will need to re-run the macro if the initital values in the cells are edited/changed. — A.S.H, Apr 20 '17 at 13:52
I also thought about that possibility, and I think that would be already nicer. I am glad that you confirmed my consideration. Thank you again for your help! — rubebop, Apr 20 '17 at 14:04
rubebop... post your solution via self-answering (also worth a badge if all criteria are met ;-). This way it is also removed from the "unanswered" SO question queue and you may catch a few voting rep for the solution if someone else solves their issue via your solution ;-) — ZF007, Jan 17 '20 at 10:48
@ZF007 I am not involved in that project anymore, but I will do as you suggested fron now on. Thank you for the information! — rubebop, Jan 17 '20 at 11:41

Excel VBA: Histogram made of different series

0 Answers0