Dynamically scale the Y-axis values in the chart RRS feed

  • Question

  • Hi Team,

    I am dynamically adding charts to the sheet and setting the x-axis and y-axis values for the chart. For scaling purpose, I am taking the maximum values from the Y-axis and setting the maximum scale and minimum scale for the chart. But the scaling is not happening properly and horizontal grid lines are not properly aligned in the chart. I did tried to set the MajorUnit property of the chart, but this did not give the expected results.

    Below is the sample vba code:

    With chartObject.Chart.Axes(xlValue)
            .MaximumScale = Round(MaxScale, 2)
            .MinimumScale = Round(MinScale, 2)
            '.MajorUnit = Round((MaxScale / 5), 2)
    End With

    Would you please assist on how to scale the chart Y-axis dynamically? Please refer the screen shot for reference.

    Excel chart which we generated with VBA macro:

    Chart generated in web with other plug-in:



    Chandra Shekar Y

    Wednesday, May 31, 2017 6:58 AM

All replies

  • I think you would want to guarantee that the major units are an exact divisor of the scale, so try

        With ChartObject.Chart.Axes(xlValue)
            .MajorUnit = Round((MaxScale / 5), 2)
            .MaximumScale = .MajorUnit * 5
            .MinimumScale = -.MajorUnit * 5
        End With

    Wednesday, May 31, 2017 6:59 PM