CODEWITHSUNDEEP
excelvba
Chewjunnie
Chewjunnie

Reputation: 137

Generating second axis in a chart vba

I wanted to create a second axis in my chart. There is no error when i run ".SeriesCollection(1).AxisGroup=2" but it does not create the second axis. I want to plot temperature at the LHS of the y-axis and power at RHS of the y-axis.

The x-axis is time.

Outcome: I want to see 2 graphs. A graph generated by Col H and Col F and another will be generated by Col R and Col F.

Sub plotGraph()
Dim LastRowOfH As Long, LastRowOfF As Long, LastRowOfR As Long
    Dim ColumnHRngData As Range, ColumnFRngData As Range, ColumnRRngData As Range
    Dim xAxes As Axis, yAxes As Axis, y2Axes As Axis
    Dim srsNew As Series

    LastRowOfH = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row
    LastRowOfF = ActiveSheet.Cells(Rows.Count, 6).End(xlUp).Row
    LastRowOfR = ActiveSheet.Cells(Rows.Count, 18).End(xlUp).Row
    Set ColumnHRngData = ActiveSheet.Range("H31:H" & LastRowOfH)
    Set ColumnFRngData = ActiveSheet.Range("F31:F" & LastRowOfF)
    Set ColumnRRngData = ActiveSheet.Range("R31:R" & LastRowOfR)
    
    Dim cht As Shape
    Set cht = ActiveSheet.Shapes.AddChart(Left:=Range("D1:S31").Left, Top:=Range("D1:S31").Top, Width:=Range("D1:S31").Width, Height:=Range("D1:S31").Height)
    With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "Temperature VS Time"
        .ChartType = xlXYScatterLinesNoMarkers
        
        Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
        Loop
        
        Set srsNew = .SeriesCollection.NewSeries
        .SeriesCollection(1).Name = "Power/W"          
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 0)           'change the color of the chart line
        .SeriesCollection(1).AxisGroup = 2
        With srsNew
            .XValues = ColumnFRngData               'xaxis of the graph refers to time in hr
            .Values = ColumnHRngData
        End With
        
        With srsNew
            .XValues = ColumnFRngData
            .Values = ColumnRRngData
        End With
        
        
        Set xAxes = .Axes(xlCategory, xlPrimary)
        With xAxes
            .HasTitle = True
            .AxisTitle.Text = "TIME (Hr)"
        End With
            
        Set yAxes = .Axes(xlValue, xlPrimary)
        With yAxes
            .HasTitle = True
            .AxisTitle.Text = "Temp (DegCel)"
        End With
        
        Set y2Axes = .Axes(xlValue, xlSecondary)
        With y2Axes
            .HasTitle = True
            .AxisTitle.Text = "Power (W)"
        End With  
    End With

Upvotes: 2

Views: 1061

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You're only plotting one series.

Try this

With cht.Chart
    .HasTitle = True
    .ChartTitle.Text = "Temperature VS Time"
    .ChartType = xlXYScatterLinesNoMarkers
        
    Do While .SeriesCollection.Count > 0
        .SeriesCollection(1).Delete  'remove any existing series
    Loop
         
    With .SeriesCollection.NewSeries
        .Name = "Power/W"          
        .Format.Line.ForeColor.RGB = RGB(0, 0, 0) 'change the color of the chart line
        .AxisGroup = 2
        .XValues = ColumnFRngData          'xaxis of the graph refers to time in hr
        .Values = ColumnHRngData
    End With
     
    With .SeriesCollection.NewSeries
        .Name = "Temperature"          
        .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'change the color of the chart line
        .AxisGroup = 1
        .XValues = ColumnFRngData          'xaxis of the graph refers to time in hr
        .Values = ColumnRRngData
    End With
        
    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        .AxisTitle.Text = "TIME (Hr)"
    End With
           
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .AxisTitle.Text = "Temp (DegCel)"
    End With
               
    With .Axes(xlValue, xlSecondary)
        .HasTitle = True
        .AxisTitle.Text = "Power (W)"
    End With  
End With

Upvotes: 1

Related Questions