CODEWITHSUNDEEP
excelvba
Wren
Wren

Reputation: 79

Fixing range of y values for graph with VBA

I have written the following code to display a basic line graph.

Sub addchart()

    If ActiveSheet.ChartObjects.Count > 0 Then
        ActiveSheet.ChartObjects.Delete
    End If
    Dim ws As Worksheet
    Dim ch As chart
    Dim dt As Range
    Dim i As Integer
    
    i = Cells(Rows.Count, "M").End(xlUp).Row
    
    Set ws = ActiveSheet
    Set dt = Range(Cells(2, 14), Cells(i, 14))
    Set ch = ws.Shapes.AddChart2(Width:=1300, Height:=300, Left:=Range("a13").Left, Top:=Range("a13").Top).chart
    
    With ch
        .SetSourceData Source:=dt
        .ChartTitle.Text = "Deflection Curve"
        .ChartType = xlLineMarkers
        
End With
End Sub

But the trouble with this is that the range of Y axis adjusts itself according to the data automatically. I want to fix this range so that the change in the graph is noticeable.

For example, the following two graphs vary in the range of values they cover but they look basically the same because the y axis is adjusting itself. One goes from 0 to -9 and the other from 0 to -25. If I can fix the range to say 0 to -30, the difference in the two graphs would be more apparent.

graph1

graph2

Upvotes: 1

Views: 41

Answers (2)

Wren
Wren

Reputation: 79

Thanks to Spencer, the following code gets the result I want

Sub addchart()

    If ActiveSheet.ChartObjects.Count > 0 Then
        ActiveSheet.ChartObjects.Delete
    End If
    Dim ws As Worksheet
    Dim ch As chart
    Dim dt As Range
    Dim i As Integer
    
    i = Cells(Rows.Count, "M").End(xlUp).Row
    
    Set ws = ActiveSheet
    Set dt = Range(Cells(2, 14), Cells(i, 14))
    Set ch = ws.Shapes.AddChart2(Width:=1300, Height:=300, Left:=Range("a13").Left, Top:=Range("a13").Top).chart
    
    With ch
        .SetSourceData Source:=dt
        .ChartTitle.Text = "Deflection Curve"
        .ChartType = xlLineMarkers
    End With

    With ch.Axes(xlValue)                  'fixing range of values

    .MinimumScale = -30
    .MaximumScale = 0

    End With
End Sub

Upvotes: 0

Spencer Barnes
Spencer Barnes

Reputation: 2877

With ch.Axes(xlValue)
    .Minimumscale = -30
    .Maximumscale = 0
End with

Relevant links:

Upvotes: 1

Related Questions