Automatically Adjust Secondary Y Axis Scale Through VBA

Share this

April 19, 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 has a different scale than the first one. In the COPERT Micro tool, for example, I have many charts that show emissions (primary Y) and the number of vehicles (secondary Y) as a function of the hour of the day (X-axis). Second, the question reminded me of 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 the secondary Y axis manually. Just follow the instructions that follow (for Excel 2013).

Step 1: Set a data series to be plotted on the 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 the 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 for adjusting the secondary Y-axis


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:        [email protected]
    'Site:          https://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 with 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.

Page last modified: September 30, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>