Extracting maximum value from chart axis-CodePudding

I have a chart sheet with 2 axis, I need to extract the maximum value of the primary axis to set the secondary one accurately. I cant seem to get the function working that will simply read the primary axis max and place it in a cell, any ideas?

CodePudding user response：

Welcome to the board. Have a read of [ASK] and [MCVE].

Recording a macro while manually changing the axis will give you:

Sub Macro4()
'
' Macro4 Macro
'

'
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 60
    ActiveChart.Axes(xlValue, xlSecondary).Select
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 90
    Range("L2").Select
End Sub

The code shows that you need to reference the chart within the chart object within the sheet to get to the axis.

Sub Test()
    
    Sheet1.ChartObjects("Chart 2").Chart.Axes(xlValue, xlSecondary).MaximumScale = _
        Sheet1.ChartObjects("Chart 2").Chart.Axes(xlValue).MaximumScale

End Sub

Note - this code can be shorted using variables and a With...End With block.

CodePudding user response：

This will return the max value of the left axis which is usually <> max value of original data

Public Function getAxisMaxScale(cht As ChartObject) As Single

Dim ax As Axis
Set ax = cht.Chart.Axes(xlValue)

getAxisMaxScale= ax.MaximumScale


End Function

To adjust the secondary axes you can use


Public Sub adjustSecondaryAxis(cht As ChartObject)

Dim axPrimary As Axis, axSecondary As Axis
Set axPrimary = cht.Chart.Axes(xlValue)

Set axSecondary = cht.Chart.Axes(xlValue, xlSecondary)

With axSecondary
    .MajorUnit = axPrimary.MajorUnit
    .MaximumScale = axPrimary.MaximumScale
End With

End Sub

I adjust the MajorUnit as well ... to be aligned as well