locked
Display 3 series of graph in a single chart [excel vba] RRS feed

  • Question

  • I have 3 data ranges. table1 - "Q4:Q1793", table2 - "Q4:Q1793", table3 - "J3:J1794" 3 line graph to display in a single chart for comparing. I'm new to series. From the examples I've search over the net, so far my code only able to show 1 graph of one of my data range. The other 2 is not working. Please enlighten me of what am I lacking in my series to display the remaining graph. Thanks very much.

    Sub AdjClose1() 
        Dim mychart As Chart 
        Set mychart = ActiveWorkbook.Charts.Add 
        mychart.SeriesCollection.Add _ 
        Source:=Worksheets("table1").Range("Q4:Q1793") 
        mychart.SeriesCollection.Add _ 
        Source:=Worksheets("table2").Range("Q4:Q1793") 
        mychart.SeriesCollection.Add _ 
        Source:=Worksheets("table1").Range("J3:J1794") 
        With ActiveChart 
            .HasTitle = True 
            .ChartTitle.Text = "Adj Close" 
            .Axes(xlCategory, xlPrimary).HasTitle = True 
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Years" 'x value
            .Axes(xlValue, xlPrimary).HasTitle = True 
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume" ' y value
        End With 
        mychart.ChartType = xlLine 
    End Sub 
    

    Tuesday, February 21, 2012 1:53 AM

Answers

  • The code looks ok to me. Check the data you have in each range. Perhaps pick slightly smaller ranges for debugging.

    You might check that you are picking up data points correctly like this.

        Dim mychart As Chart
        Set mychart = ActiveWorkbook.Charts.Add
        
        Dim ser1 As Series, ser2 As Series, ser3 As Series
        
        Set ser1 = mychart.SeriesCollection.Add _
        (Source:=Worksheets("table1").Range("Q4:Q1793"))
        
        Debug.Print ser1.Points.Count

    Ed Ferrero
    www.edferrero.com

    • Proposed as answer by danishani Thursday, February 23, 2012 4:36 PM
    • Marked as answer by danishani Saturday, February 25, 2012 7:02 PM
    Tuesday, February 21, 2012 5:46 AM
  • Try to use the NewSeries to add a new serie to the collection.

    I used a Macro to see how it works, and the solution was NewSeries.

    Recording a Macro comes in handy, when you can't figure it out. ;)


    So try to change the code into the following:

    Sub AdjClose1() 
    
    Dim mychart As Chart
        Set mychart = ActiveWorkbook.Charts.Add
       
        
        With ActiveChart
            .SeriesCollection.NewSeries 
            .SeriesCollection(1).Name = "Data1"
            .SeriesCollection(1).Values = "='table1'!Q4:Q1793" 'Worksheets("table1").Range("Q4:Q1793")
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Name = "Data2"
            .SeriesCollection(2).Values = "='table2'!Q4:Q1793" 'Worksheets("table2").Range("Q4:Q1793")
            .SeriesCollection.NewSeries
            .SeriesCollection(3).Name = "Data3"
            .SeriesCollection(3).Values = "='table1'!J3:J1794" 'Worksheets("table1").Range("J3:J1794")
        End With
        
    
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = "Adj Close"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Years" 'x value
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume" ' y value
        End With
        mychart.ChartType = xlLine
    
    
    End Sub 



    Hope this helps,

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"



    • Edited by danishani Thursday, February 23, 2012 2:41 PM
    • Proposed as answer by danishani Thursday, February 23, 2012 4:36 PM
    • Marked as answer by danishani Saturday, February 25, 2012 7:02 PM
    Thursday, February 23, 2012 4:21 AM

All replies

  • The code looks ok to me. Check the data you have in each range. Perhaps pick slightly smaller ranges for debugging.

    You might check that you are picking up data points correctly like this.

        Dim mychart As Chart
        Set mychart = ActiveWorkbook.Charts.Add
        
        Dim ser1 As Series, ser2 As Series, ser3 As Series
        
        Set ser1 = mychart.SeriesCollection.Add _
        (Source:=Worksheets("table1").Range("Q4:Q1793"))
        
        Debug.Print ser1.Points.Count

    Ed Ferrero
    www.edferrero.com

    • Proposed as answer by danishani Thursday, February 23, 2012 4:36 PM
    • Marked as answer by danishani Saturday, February 25, 2012 7:02 PM
    Tuesday, February 21, 2012 5:46 AM
  • Try to use the NewSeries to add a new serie to the collection.

    I used a Macro to see how it works, and the solution was NewSeries.

    Recording a Macro comes in handy, when you can't figure it out. ;)


    So try to change the code into the following:

    Sub AdjClose1() 
    
    Dim mychart As Chart
        Set mychart = ActiveWorkbook.Charts.Add
       
        
        With ActiveChart
            .SeriesCollection.NewSeries 
            .SeriesCollection(1).Name = "Data1"
            .SeriesCollection(1).Values = "='table1'!Q4:Q1793" 'Worksheets("table1").Range("Q4:Q1793")
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Name = "Data2"
            .SeriesCollection(2).Values = "='table2'!Q4:Q1793" 'Worksheets("table2").Range("Q4:Q1793")
            .SeriesCollection.NewSeries
            .SeriesCollection(3).Name = "Data3"
            .SeriesCollection(3).Values = "='table1'!J3:J1794" 'Worksheets("table1").Range("J3:J1794")
        End With
        
    
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = "Adj Close"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Years" 'x value
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume" ' y value
        End With
        mychart.ChartType = xlLine
    
    
    End Sub 



    Hope this helps,

    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"



    • Edited by danishani Thursday, February 23, 2012 2:41 PM
    • Proposed as answer by danishani Thursday, February 23, 2012 4:36 PM
    • Marked as answer by danishani Saturday, February 25, 2012 7:02 PM
    Thursday, February 23, 2012 4:21 AM
  • Thanks, it works. You guys are awesome !!!

    Thursday, February 23, 2012 9:22 AM
  • Hi kio1314,

    What worked for you?

    Please mark the answer and vote the answer as Helpful to close your thread.

    Many thanks,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Thursday, February 23, 2012 2:46 PM