Chewjunnie
Chewjunnie

Reputation: 137

trouble plotting charts through vba

I want to plot a graph with an interval difference of 0.3. So the x-axis will look like it starts from 0.00, 0.03, 0.06....

However, my graph obtained is very different from what i wanted.

Sub plottinggraph()

    Dim LastRowOfA As Long, LastRowOfB As Long, LastRowOfG As Long
    Dim ColumnARngData As Range, ColumnBRngData As Range
    Dim xAxes As Axis, yAxes As Axis

    Dim cht As Shape
    Set cht = ActiveSheet.Shapes.AddChart
    With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "X VS Y"
        .ChartType = xlXYScatterLinesNoMarkers
        
        
        Set srsNew = .SeriesCollection.NewSeries
        .SeriesCollection(1).Name = "X, Y"
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 0)
        With srsNew
            .XValues = ColumnARngData
            .Values = ColumnBRngData
        End With
        
        Set xAxes = .Axes(xlCategory, xlPrimary)
        With xAxes
            .HasTitle = True
            .AxisTitle.Text = "X"
        End With
            
        Set yAxes = .Axes(xlValue, xlPrimary)
        With yAxes
            .HasTitle = True
            .AxisTitle.Text = "Y"
        End With
    End With
End Sub

Upvotes: 0

Views: 109

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

This works fine for me:

Sub plottinggraph()

    Dim ws As Worksheet
    Dim xVals As Range, yVals As Range
    Dim co As Shape, cht As Chart
    
    Set ws = ActiveSheet
    Set xVals = ws.Range("A4", ws.Cells(Rows.Count, "A").End(xlUp))
    Set yVals = xVals.Offset(0, 1) 'colB

    Set co = ws.Shapes.AddChart
    Set cht = co.Chart
    
    With cht
        'always remove any possible "autoplotted" series...
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        .HasTitle = True
        .ChartTitle.Text = "X VS Y"
        .ChartType = xlXYScatterLinesNoMarkers
        
        With .SeriesCollection.NewSeries
            .XValues = xVals
            .Values = yVals
            .Name = "X, Y"
            .Format.Line.ForeColor.RGB = RGB(0, 0, 0)
        End With
        
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = "X"
            .MinimumScale = 0
            .MaximumScale = Application.Max(xVals)
            .MajorUnit = 0.3
        End With
            
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = "Y"
        End With
    End With

End Sub

It's sometimes a bit of a pain working with charts but Ok once you get the hang of it. The macro recorder is your friend here - even if you just use it to figure out how to address a specific aspect/property of your chart.

Upvotes: 3

Related Questions