none
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:

    Thanks,

    Chandra

    Tuesday, May 30, 2017 8:44 AM

All replies

  • Hello,

    Normally, the MinimumScale and MaximumScale of Axes value would be automatically calculated by Excel. 

    It would be:

    If you want to specify its value, we could use 

        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Axes(xlValue).Select
        ActiveChart.Axes(xlValue).MinimumScale = -0.9
        ActiveChart.Axes(xlValue).MaximumScale = 0.9
        ActiveChart.Axes(xlValue).MajorUnit = 0.18
        Selection.TickLabels.NumberFormat = "#,##0.00"

    Then it would be:

    The max Y- axis value for your generated chart is 0.79, i think it is because the variable MaxScale is 0.79XXX. 

    If you want to generate the same chart as the web, please check the "MaxScale".

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 31, 2017 10:16 AM
    Moderator
  • Hello,

    Thank You for the reply, but in your post you have hard coded the MaxScale, MinScale and MajorUnit.

    Q1. How do you calculate the MaJorUnit is 0.18 in your values.

    Q2. How do we calculate the MajorUnit, if it is a dynamic values as shown in the Picture? i.e. MaxScale is 0.81

    Thanks


    Chandra Shekar Y

    Thursday, June 1, 2017 9:21 AM
  • Hello,

    In fact, the value starts from MinScale. If you want a Zero point, the possible MajorUnit would be MajorUnit =|MinScale|/n. n could be 1,2,3,4,5,... etc.

    So the MajorUnit could be

    If you want to dynamically scale, you just need to follow the logic to get a 0 point. It would depend on the space number.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, June 2, 2017 9:57 AM
    Moderator