copy

Sunday, 19 April 2015



Automatically Adjust Secondary Y Axis Scale Through VBA


Introduction



Some days ago, while I was checking the discussions on an Excel-related group on LinkedIn, I bumped into an interesting question about charts. The question was how to automatically set the secondary Y axis scale via code, so that both primary and secondary axes share the same number of gridlines”.

I thought this question was interesting for two reasons: first, it’s quite common to include a secondary Y axis on a chart, just to display another series that have different scale than the first one. In COPERT Micro tool for example, I have many charts that show emissions (primary Y) and number of vehicles (secondary Y) as a function of hour of the day (X axis). Second, the question reminded me a similar need that I had some years ago. So, I found the code that I had written then (2012) and I made some minor changes in order to make it more generic, and, here it is!



The manual way



By the way, it’s not hard to adjust the scale of secondary Y axis manually. Just follow the instructions that follow (for Excel 2013).

Step 1: Set a data series to be plotted on secondary axis:
  1. Select a series on the chart by right clicking on it.
  2. On the pop up menu select Format Data Series.
  3. On the Format Data Series menu that will appear select the Series Options tab.
  4. Select the plot on secondary axis radio button.

Format Data Series Menu Plot Series On Secondary Axis

Step 2: Adjust the scale of the secondary Y axis:
  1. Select the secondary Y axis by right clicking on it.
  2. On the pop up menu select Format Axis.
  3. On the Format Axis menu that will appear select the Axis Options tab.
  4. Finally, set the desired values on Minimum/Maximum Bounds and on Major Unit text boxes.

Format Secondary Y Axis Set Secondary Y Axis Scale

In the last step you might need to make some (repeating) tests on the values that you will enter in the 3 text boxes (especially in the Major Unit text box) in order to achieve that both primary and secondary axes will share the same number of gridlines.



VBA code



And here is the “easy way”; no need to test the values on the text boxes, no worries about how the axes will look uniformly. It’s a short macro that can be customized according to your needs.

Option Explicit

Sub AdjustSecondaryYAxisScale()
    
    '--------------------------------------------------------------------------
    'Automatically adjusts the scale of the secondary Y axis, so that both
    'primary and secondary Y axes share the same number of (major) gridlines.
    
    'Written By:    Christos Samaras
    'Date:          14/09/2012
    'Last Update:   18/04/2015
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim ch      As Chart
    Dim Ymin    As Double
    Dim Ymax    As Double
    Dim Yscale  As Double
    Dim Ylines  As Integer
    Dim sYmin   As Double
    Dim sYmax   As Double
    Dim sYscale As Double
    
    'Set the ch variable to a specific chart on sheet 1 (in this example).
    'If you need to set the ch variable to active chart you can use the next line:
    'Set ch = ActiveChart
    Set ch = Sheet1.ChartObjects(1).Chart
    
    'A quick test if the ch variable is not empty.
    On Error Resume Next
    If ch Is Nothing Then
        MsgBox "The chart wasn't set!", vbCritical, "Empty Chart"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Set the minimum/maximum bound and the major unit to Auto for both primary and secondary axes.
    'For the primary Y axis this is NOT always necessary, so the corresponding 3 lines can be deleted/commented.
    With ch
        .Axes(xlValue).MinimumScaleIsAuto = True
        .Axes(xlValue).MaximumScaleIsAuto = True
        .Axes(xlValue).MajorUnitIsAuto = True
        .Axes(xlValue, xlSecondary).MinimumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MaximumScaleIsAuto = True
        .Axes(xlValue, xlSecondary).MajorUnitIsAuto = True
    End With
    
    'Get the mininmum bound of the primary Y axis.
    Ymin = ch.Axes(xlValue).MinimumScale
    
    'Get the maximum bound of the primary Y axis.
    Ymax = ch.Axes(xlValue).MaximumScale
    
    'Get the major unit of the primary Y axis.
    Yscale = ch.Axes(xlValue).MajorUnit
        
    'Calculate the number of major gridlines.
    Ylines = Round((Ymax - Ymin) / Yscale)
    
    'Get the mininmum bound of the secondary Y axis.
    sYmin = ch.Axes(xlValue, xlSecondary).MinimumScale
    
    'Get the maximum bound of the secondary Y axis.
    sYmax = ch.Axes(xlValue, xlSecondary).MaximumScale
    
    'Note that you can easily set the minimum/maximum bound of the secondary Y axis to any value you like.
    'In other words, you can make your secondary Y axis look exactly as you want.
    'For example if you uncomment the next 2 lines, the first gridline of the secondary Y axis will start at 0 and
    'the last one will end at the value of 30. The number of gridlines will be automatically calculated/adjusted.
    'sYmin = 0
    'sYmax = 30
    
    'Calculate the new major unit of the secondary Y axis.
    sYscale = Round((sYmax - sYmin) / Ylines)
    
    'Calculate the new maximum bound of the secondary Y axis.
    sYmax = sYmin + Ylines * sYscale
    
    'Set the minimum/maximum bound and the major unit of the secondary Y axis to their new values.
    With ch.Axes(xlValue, xlSecondary)
        .MinimumScale = sYmin
        .MaximumScale = sYmax
        .MajorUnit = sYscale
    End With
    
    'Release the chart object.
    Set ch = Nothing
    
End Sub

In the workbook that you will find in the downloads section below you can try the above code by experimenting on a chart that I created using random numbers. Just see how nice the scale of the secondary Y axis "follows" the scale of the primary Y axis.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info