CODEWITHSUNDEEP
vbaexcel
Jack
Jack

Reputation: 329

Get Chart Axis Value

I realise you can set the axis of a graph in VBA using 

.MaximumScale = 
.MinimumScale =

Is there a way to get the axis value?

I ask this because it would make it easier to automate a process that gets the axis of a chart, then adds a month to it (without setting the graph axis to automatic).

P.S.

I recorded a macro of changing the axis dates and it set the date values as a number like 40148 or 41609. What is this?

Upvotes: 0

Views: 3752

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Try to step through the following snippet of code. It shows how to find Y axis value and how to change it. See some comments inside.

1st attempt for chart embedded in worksheet

Sub test_chart()

'get the chart for activesheet
    Dim myCHR As Chart
    Set myCHR = ActiveSheet.ChartObjects(1).Chart

'get Y axis of the chart
    Dim myYA As Axis
    Set myYA = myCHR.Axes(XlAxisType.xlValue)

'get the value
    Debug.Print myYA.MaximumScale
    Debug.Print myYA.MinimumScale

'the same in almost one line
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
        Debug.Print .MaximumScale
        Debug.Print .MinimumScale
        'change the value
        .MaximumScale = 10
    End With
End Sub

2nd attempt for chart being separate sheet

Sub test_sheet_chart()

'get the chart for activesheet
    Dim myCHR As Chart
    Set myCHR = Sheets("All SIN 10 Pubs - Unique Users")

'get Y axis of the chart
    Dim myYA As Axis
    Set myYA = myCHR.Axes(XlAxisType.xlValue)

'get the value
    Debug.Print myYA.MaximumScale
    Debug.Print myYA.MinimumScale

'the same in almost one line
    With Sheets("All SIN 10 Pubs - Unique Users").Axes(xlValue)
        Debug.Print .MaximumScale
        Debug.Print .MinimumScale
        'change the value
        .MaximumScale = 10
    End With
End Sub

Upvotes: 1

Related Questions