Add series to chart in VBA

[derthorsten]

Hi,

I cant find a solution to the following problem:

I have a macro that creates a chart from data with one series "total". Its not the most efficient piece of code I guess but it does the job (if you have an idea how to shorten the code be by guest ).

yy_total is my complete data range consisting of 2 columns with X and Y values.

    Charts.Add
    With ActiveChart
        .ChartType = xlLine
        .HasTitle = True
        .HasLegend = False
        .ChartTitle.Text = "YY Total"
        .SetSourceData Source:=yy_total
        .Location Where:=xlLocationAsObject, Name:=ShName1
    End With
        With ActiveChart.Axes(xlCategory)
        .TickLabels.NumberFormat = "MMMMM/YY"
        End With
        With ActiveChart.Axes(xlValue)
        .MaximumScale = max_yytotal + 1000
        .MinimumScale = min_yytotal - 1000
        End With
            With ActiveChart.Parent
                .Top = Cells(4, 6).Top
                .Left = Cells(4, 6).Left
            End With

How can I add a second series to the chart? All my attempts have failed so far

 

[Domenic]

Try...

    Dim oShape As Shape    

    Set oShape = ActiveSheet.Shapes.AddChart( _
        XlChartType:=xlLine, _
        Left:=Cells(4, 6).Left, _
        Top:=Cells(4, 6).Top)
        
    With oShape
        .Name = ShName1
        With .Chart
            .HasTitle = True
            .HasLegend = False
            .ChartTitle.Text = "YY Total"
            .SetSourceData Source:=Range("yy_total")
            .Axes(xlCategory).TickLabels.NumberFormat = "MMMMM/YY"
            With .Axes(xlValue)
                .MinimumScale = min_yytotal - 1000
                .MaximumScale = max_yytotal + 1000
            End With
        End With
    End With

Hope this helps!

 

[derthorsten]

thanks, but correct me if I m wrong, I don't see a second series.

 

[Domenic]

That's probably because of the values I'm assuming you've set for min_yytotal and max_yytotal and consequently for the minimum and maximum scales.

 

[derthorsten]

That's probably because of the values I'm assuming you've set for min_yytotal and max_yytotal and consequently for the minimum and maximum scales.

I was thinking that there is only

.SetSourceData Source:=Range("yy_total")

and yy_total contains a date column and a value column. But where is the second series such as "zz_total"? That's what I m looking for.

 

[Domenic]

Sorry, but I'm a little confused. Do you want "zz_total" included in the present code? Or do you want to be able to run a separate code just to add "zz_total"?

 

[derthorsten]

Sorry, but I'm a little confused. Do you want "zz_total" included in the present code? Or do you want to be able to run a separate code just to add "zz_total"?

I would like to have it included in the present code

 

[Domenic]

In that case, try...

.SetSourceData Source:=Range("yy_total,zz_total")

Or, you can define a name for the entire range of data, let's say you name it "ChartRange", and assign it as follows..

.SetSourceData Source:=Range("ChartRange")

 

[derthorsten]

In that case, try...

.SetSourceData Source:=Range("yy_total,zz_total")

Or, you can define a name for the entire range of data, let's say you name it "ChartRange", and assign it as follows..

.SetSourceData Source:=Range("ChartRange")

and how can I select one of the two series separately then? Such as series yy_total should be blue, and series zz_total orange?

 

[Domenic]

Try...

    Dim oShape As Shape
    
    Set oShape = ActiveSheet.Shapes.AddChart( _
        XlChartType:=xlLine, _
        Left:=Cells(4, 6).Left, _
        Top:=Cells(4, 6).Top)
        
    With oShape
        .Name = ShName1
        With .Chart
            .HasTitle = True
            .HasLegend = False
            .ChartTitle.Text = "YY Total"
            .SetSourceData Source:=Range("yy_total,zz_total")
            .Axes(xlCategory).TickLabels.NumberFormat = "MMMMM/YY"
            With .Axes(xlValue)
                .MinimumScale = min_yytotal - 1000
                .MaximumScale = max_yytotal + 1000
            End With
            [COLOR=#ff0000].SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192)[/COLOR] 'blue
           [COLOR=#ff0000] .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 192, 0)[/COLOR] 'orange
        End With
    End With