Coding series collections in a chart

[troy_lee]

I need some help with the code to assign values to a series collection for charts. This is the first chart I have tried to do with VBA.

My first question is how do I properly set the SetSourceData Source? This line is commented out below. All of the range arguments on this line are verified to be correct (I created them in another piece of code not shown here).

How do I treat the X axis? This should be SeriesCollection(1), right?

Also, am I assigning the range correctly for the values statements for the series collections?

Finally, I want the 4th series collection (EOH) to be on a secondary Y axis. The last line of code is what the macro recorder came up with for this. Is this correct?

Any ideas are welcomed, including a more efficient way to do this. Thanks for the help in advance.

 Set chtYTY = ActiveSheet.ChartObjects.Add(Left:=250, Width:=375, Top:=75, Height:=225)
    
    With chtYTY.Chart
        .ChartType = xlLineMarkers
        '.SetSourceData Source:=Sheets("Data").Range(rngXvalues, rngShipped, rngIn, rngEOH),
        'PlotBy:=xlColumns
    End With
        
    'Clear out all Series that Excel may have created by default.
    With chtYTY.Chart
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
        
    'Set up the X axis values
        With .SeriesCollection.NewSeries
            .XValues = "rngXvalues"
            .Name = "Week Number"
        End With
        
    'First Y axis series.
        With .SeriesCollection.NewSeries
            .Name = "=Shipped"
            .Values = "=Data!Range(rngShipped)"
            .XValues = "=Data!rngXvalues"
        End With
    
    'Second Y axis series.
       With .SeriesCollection.NewSeries
            .Name = "In"
            .Values = "rngIn"
            .XValues = "rngXvalues"
        End With
    
    'Third Y axis series.
        With .SeriesCollection.NewSeries
            .Name = "EOH"
            .Values = "rngEOH"
            .XValues = "rngXvalues"
        End With
            
        '.Location Where:=xlLocationAsObject, Name:="Scorecard"
        .HasTitle = True
        .ChartTitle.Characters.Text = "Year to Year Ships"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week Number"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Number of Units"
        .HasDataTable = False
        
    'This is code for the secondary axis
        .SeriesCollection(4).AxisGroup = 2
        
    End With

 

[Andrew Poulsom]

In a chart each series has a Values property (the values to plot) and an XValues property (the values to place on the x axis). The scale of the y axis is determined by the Values.

The macro recorder is quite useful for getting the correct syntax. Also see:

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

 

[troy_lee]

Thanks for the feedback Andrew. I am familiar with Peltier's site. In fact, that was the page I used to get started on this.

I understand the distinction between values and Xvalues. My question is does the X axis need both values and Xvalues? For instance, my X axis is the Week Number, which is Column A on my datasheet. Isn't it redundant to define both when they both are set to the same range?

Thanks.

 

[Andrew Poulsom]

The x axis doesn't have a Values or XValues property. What appears on it is determined by the first series' XValues property.

 

[troy_lee]

OK. That makes sense. Do I need to assign anything to the Values property for the first series? Also, is the first series always the X axis and must I define the Xvalues for each subsequent series in the chart?

Thanks.

 

[Andrew Poulsom]

The x axis is an axis not a series. Each series has a Values property. You only need to set the XValues property for the first series. Other series added will inherit that property.

 

[troy_lee]

Thanks for clearing this up.