Automating chart scale limits

Edit 22 Mar 2014:  Also see
for improved version with download link, and example of plotting a chart from a formula entered as text.

One of the more annoying things about Excel charts is that if you want to over-ride the automatic scale limits there is no built-in way to link the limits to a spreadsheet cell, so you have to go into the dialog box and change the numbers manually every time you want to change the scale.

Tushar Mehta recently posted a solution at Daily Dose of Excel, with an add-in providing this functionality, as well as other format chart adjustments.

In the following discussion John Walkenbach linked to an earlier solution that those who like to keep things simple may prefer.  This is a user defined function (UDF), that returns no data, but modifies the y-axis limits of any named chart.  It should be noted that this solution only works in Excel 2007 and later, is undocumented, and is not supposed to work at all, so use with caution.  I have taken the liberty of modifying John’s code so that the limits of both the X and Y axes can be linked to cell values, the axes limits can be re-set to automatic, and the status of each axis is returned by the function.  The revised code and a screen shot are given below.

Function ChangeChartAxisScale(CName As String, Optional Xlower As Double = 0, Optional Xupper As Double = 0, _
Optional Ylower As Double = 0, Optional YUpper As Double = 0) As Variant
Dim Rtn As String
'   Excel 2007 only

With ActiveSheet.Shapes(CName).Chart.Axes(1)
If Xlower = 0 Then
.MinimumScaleIsAuto = True
Rtn = "Xmin = auto"
.MinimumScale = Xlower
Rtn = "Xmin = " & Xlower
End If

If Xupper = 0 Or (Xupper < Xlower) Then
.MaximumScaleIsAuto = True
Rtn = Rtn & "; Xmax = auto"
.MaximumScale = Xupper
Rtn = Rtn & "; Xmax = " & Xupper
End If
End With

With ActiveSheet.Shapes(CName).Chart.Axes(2)
If Ylower = 0 Then
.MinimumScaleIsAuto = True
Rtn = Rtn & "; Ymin = auto"
.MinimumScale = Ylower
Rtn = Rtn & "; Ymin = " & Ylower
End If

If YUpper = 0 Or (Xupper < Xlower) Then
.MaximumScaleIsAuto = True
Rtn = Rtn & "; Ymax = auto"
.MaximumScale = YUpper
Rtn = Rtn & "; Ymax = " & YUpper
End If
End With

ChangeChartAxisScale = Rtn
End Function

ChangeChartAxisScale Function, Click for full size view

This entry was posted in Charts, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

8 Responses to Automating chart scale limits

  1. Mike says:

    great tip… is there a way to easily adjust this to work when there are 2 y-axes on the chart?



  2. dougaj4 says:

    Mike – I don’t know, but I’ll have a look when I have time.


  3. brian says:

    I changed the routine to accept an additional optional variable
    changeaxis(…, optional yaxis as integer=1)

    then change the yaxis bit to:
    ‘ add this —————————–
    If yaxis = 2 Then
    Set myaxis = ActiveSheet.Shapes(CName).Chart.Axes(2, xlSecondary)
    Set myaxis = ActiveSheet.Shapes(CName).Chart.Axes(2)
    End If
    ‘ ——————————————–
    With myaxis ‘ was just …. ActiveSheet.Shapes(CName).Chart.Axes(2)


  4. dougaj4 says:

    Thanks brian, I’ll post an update.


  5. Pingback: Automating chart scale limits – update | Newton Excel Bach, not (just) an Excel Blog

  6. Pingback: Charts and Charting | Newton Excel Bach, not (just) an Excel Blog

  7. Patibandla says:

    how to integrate this in Visual Studio and make it work on SharePoint


    • dougaj4 says:

      I can’t help with that one I’m afraid, I don’t even know if it is possible. It is not clear to me how SharePoint relates to OneDrive, and what facilities each offers.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s