Chart UDF to Control Axis Scale

User Defined Function to Control Chart Axes?

Can you really use a chart UDF to control axis scales? That would be pretty cool. UDFs are VBA functions that you can use in your worksheet, but they only return values to cells, right?

Well, yes, that’s what the documentation says, but there’s a fortuitous bug/feature in Excel UDFs that extend their capabilities in unexpected ways.

Link Axis Scale to Worksheet Cells

For as long as I’ve been using Excel, people have wanted to be able to link the chart axis scale parameters to worksheet cells. Seriously, how hard could it be to link the axis scale boxes in the dialog to worksheet cells?

Microsoft tells us, if there’s a feature we really really want, post it in User Voice, and if it gets enough votes, we’ll work on it. And someone already has, back in 2015, Erik Svensen posted Link the min and max values of a chart axis to cell value. And in 2015, Microsoft said “Thanks for the suggestion!” As of this writing the suggestion has received 589 votes. If you haven’t voted, please do.

So there’s no built-in capability to do this (yet). It is possible to use VBA to link a chart’s axis to the worksheet, as I wrote about in Link Excel Chart Axis Scale to Values in Cells. Works well enough, if you’re comfortable with VBA, but it’s not easy to set up and maintain, and I’ve never felt it was too reliable. Former Excel MVP Tushar Mehta has his AutoChart add-in that sets it up for you, but the technique is still a bit wonky.

Excel User Defined Functions

I’ve written a few tutorials that describe specific UDFs, but I have no general tutorials on them. But there are hundreds of UDF articles on the internet.

User defined functions allow you to write functions which are not built into Excel. In Create custom functions in Excel, Microsoft starts a tutorial with

Although Excel includes a multitude of built-in worksheet functions, chances are it doesn’t have a function for every type of calculation you perform. The designers of Excel couldn’t possibly anticipate every user’s calculation needs. Instead, Excel provides you with the ability to create custom functions, which are explained in this article.

Another good introduction is Creating a UDF (User Defined Function) in Excel by my colleague Philip Treacy.

In general, a user defined function can only return the result of its calculations to the cell it is called from. The UDF cannot change cell formatting (other than changes based on the cell’s conditional formatting) nor affect another cell, other than through a formula in that other cell. The UDF cannot insert worksheets, change sheet names, create workbooks, or affect anything else in Excel except for the value in the cell it lives in.

Except that a UDF can create and modify shapes in Excel.

UDFs Can Modify Shapes

In 2006 on the Daily Dose of Excel blog, Rob Van Gelder posted in In Cell Charting that “you can create a Shape from a user-defined function.” Rob used this trick as a way to draw lines and shapes to make small sparkline charts in an Excel worksheet. At first this seemed like a neat parlor trick, but several of us contributed ideas and lines of code, both in the original post and in the follow-up, Scaled In Cell Charting. None of the code samples are reproduced here, as the procedures are all rather lengthy.

Eventually Fabrice Rimlinger turned this concept into Sparklines for Excel®, “a set of free User Defined Functions for Microsoft Excel® to create Sparklines.” Unfortunately, the Sparklines for Excel website and forum have not had any activity since 2016; they are still live, and you can still download add-ins which as far as I know still work.

However, the concept is interesting, and charts are in fact a special kind of Excel shape.

UDFs Can Modify Charts

Back in 2007, also on Daily Dose of Excel, John “Mr Spreadsheet” Walkenbach posted Modifying Shapes (And Charts) With UDFs. John showed three UDFs: the first changed the type of a shape (e.g., rectangle to ellipse), the second changed the chart type of a chart (e.g., column to line), and the third, most interesting, example, applied new axis scale limits:

Function ChangeChartAxisScale(CName, lower, upper)
    With ActiveSheet.Shapes(CName).Chart.Axes(xlValue)
        .MinimumScale = lower
        .MaximumScale = upper
    End With
End Function

Write a worksheet function in a cell to call this chart UDF, pass in the chart name and the new lower and upper limits…

=ChangeChartAxisScale("Chart 1",-5,5)

…and the chart updates.

UDFs Can Modify Chart Axes

While traveling in Australia, I read a post by Mark Proctor on the Excel Off The Grid blog, Set chart axis min and max based on a cell value. Mark has revisited the chart UDF concepts above with a function that will update the minimum or maximum of a chart axis:

=setChartAxis(chartName, MinOrMax, ValueOrCategory, PrimaryOrSecondary, Value)

Enter the chart name, indicate whether to update the min or max, whether it’s a value (Y) or category (X) axis, a primary or secondary axis, and finally provide the new value for the min or max. The function would look something like this:

=setChartAxis("Chart 1", "Max", "Value", "Primary", 15)

which will set the maximum of the primary value axis of the chart named “Chart 1” on the active sheet to 15.

Now that’s pretty cool. While waiting in the Virgin Australia departure lounge (thanks, Liam!), I whipped up my own version of this function, and shared it with colleagues Mynda Treacy and Gašper Kamenšek, who agreed that it’s pretty cool.

Immediately I thought of ways to build on Mark’s UDF. For example, why not modify all axis scale arguments (minimum, maximum, major unit, and minor unit) in one function? You can build in some error-proofing: what if you specify a secondary axis but the chart only has primary axes, or what if the entered minimum is greater than the entered maximum? Also, as I learned in one of my Unlock Excel conference sessions, modifying “Chart 1” on the active sheet will result in strange behavior if you have another chart named “Chart 1” on another sheet, and you activate this other sheet.

Mark has another interesting chart UDF tutorial, Create dynamic chart titles with custom formatting, which presents a UDF that builds a dynamic chart title using the contents and formats of a set of specified cells.

Advanced Chart UDFs

I’ve come up with the following user defined function, which will allow you to set all axis scale arguments for a given axis, in a specified chart on a specified sheet:

=PT_ScaleChartAxis(SheetName,ChartName,X_or_Y,Primary_or_Secondary,Minimum,Maximum,MajorUnit,MinorUnit)

To hard-code some values into the formula, use this:

=PT_ScaleChartAxis("Sheet1","Chart 1","Y","Primary",0,10,2,0.5)

To link the axis scale to cell values, as in the screenshot at the beginning of this article, use this:

=PT_ScaleChartAxis("Sheet1","Chart 1","Y","Primary",H3,H4,H5,H6)

Note that the worksheet name is specified as well as the chart name. The function accepts “x”, “X”, “category”, “cat”, or 1 for the category axis, and “y”, “Y”, “value”, “val”, or 2 for the value axis. It allows “primary”, “pri”, or 1 for the primary axis, and “secondary”, “sec”, or 2 for the secondary axis. For the axis scale arguments, the function accepts a numerical value, a valid cell reference, “auto”, “autoscale”, or “default” to let Excel apply its default scaling for that argument, and “null”, “skip”, “ignore”, “blank”, or a blank to make no change to that argument.

The function also returns informative feedback to the cell:

Sheet 'Sheet1' Chart 'Chart 1' Primary Y Axis {0, 10, 2, 0.5}

If the axis is successfully adjusted, the new parameters are provided; if an error is encountered (“Worksheet ‘Sheet1’ not found”, “Chart ‘Chart 1’ not found on worksheet ‘Sheet1′”, “Cannot scale a category-type axis”, etc.) then a message is returned.

The function’s prefix “PT” indicates that I like this function so much that I’ve added it to the Advanced Edition of my commercial Excel charting add-in, Peltier Tech Charts for Excel 3.0.

If you start typing the formula into a cell, Excel’s IntelliSense finds it for you:

and Excel’s Function Arguments dialog helps you fill in the arguments:

It’s proprietary, but I’ll share the UDF below (it’s long, because of error-proofing and the descriptive output):

Function PT_ScaleChartAxis(SheetName As String, ChartName As String, X_or_Y As Variant, Primary_or_Secondary As Variant, _
    Minimum As Variant, Maximum As Variant, MajorUnit As Variant, MinorUnit As Variant) As Variant
  
  Dim wks As Worksheet, cht As chart, ax As Axis
  Dim xyAxisGroup As XlAxisGroup
  Dim rCaller As Range
  Dim dMinimum As Double, dMaximum As Double
  Dim bSetMin As Boolean, bSetMax As Boolean
  Dim sError As String, iError As Long
  Dim vTestCategory As Variant
  
  Application.Volatile True
  
  If Len(SheetName) = 0 Then
    Set rCaller = Application.Caller ' cell containing UDF
    SheetName = rCaller.Parent.Name
  End If
  
  On Error Resume Next
  Set wks = Worksheets(SheetName)
  On Error GoTo 0
  If wks Is Nothing Then
    sError = "Worksheet '" & SheetName & "' not found"
    GoTo ErrorFunction
  End If
  If wks.ChartObjects.Count = 0 Then
    sError = "No charts found on worksheet '" & SheetName & "'"
    GoTo ErrorFunction
  End If
  
  If Len(ChartName) = 0 Then
    ChartName = wks.ChartObjects(1).Name
  End If
  
  On Error Resume Next
  Set cht = wks.ChartObjects(ChartName).chart
  On Error GoTo 0
  If cht Is Nothing Then
    sError = "Chart '" & ChartName & "' not found on worksheet '" & SheetName & "'"
    GoTo ErrorFunction
  End If
  
  Select Case LCase$(X_or_Y)
    Case "x", "1", "category", "cat"
      X_or_Y = xlCategory
      '' but not for non-value axes
    Case "y", "2", "value", "val"
      X_or_Y = xlValue
  End Select
  
  Select Case LCase$(Primary_or_Secondary)
    Case "primary", "pri", "1"
      Primary_or_Secondary = xlPrimary
    Case "secondary", "sec", "2"
      Primary_or_Secondary = xlSecondary
  End Select
  
  Set ax = cht.Axes(X_or_Y, Primary_or_Secondary)
  
  If ax.Type = xlCategory Then
    On Error Resume Next
    vTestCategory = ax.MinimumScale
    iError = Err.Number
    On Error GoTo 0
    If iError <> 0 Then
      sError = "Cannot scale a category-type axis"
      GoTo ErrorFunction
    End If
  End If
  
  If IsNumeric(Minimum) Or IsDate(Minimum) Then
    dMinimum = Minimum
    bSetMin = True
  Else
    Select Case LCase$(Minimum)
      Case "auto", "autoscale", "default"
        ax.MinimumScaleIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        Minimum = "null"
        ' make no change
    End Select
  End If
  
  If IsNumeric(Maximum) Or IsDate(Maximum) Then
    dMaximum = Maximum
    bSetMax = True
  Else
    Select Case LCase$(Maximum)
      Case "auto", "autoscale", "default"
        ax.MaximumScaleIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        Maximum = "null"
        ' make no change
    End Select
  End If
  
  If bSetMin And bSetMax Then
    If dMaximum <= dMinimum Then
      sError = "Maximum must be greater than Minimum"
      GoTo ErrorFunction
    End If
  End If
  
  If bSetMin Then
    ax.MinimumScale = dMinimum
  End If
  
  If bSetMax Then
    ax.MaximumScale = dMaximum
  End If
  
  If IsNumeric(MajorUnit) Then
    If MajorUnit > 0 Then
      ax.MajorUnit = MajorUnit
    End If
  Else
    Select Case LCase$(MajorUnit)
      Case "auto", "autoscale", "default"
        ax.MajorUnitIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        MajorUnit = "null"
        ' make no change
    End Select
  End If
  
  If IsNumeric(MinorUnit) Then
    If MinorUnit > 0 Then
      ax.MinorUnit = MinorUnit
    End If
  Else
    Select Case LCase$(MinorUnit)
      Case "auto", "autoscale", "default"
        ax.MinorUnitIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        MinorUnit = "null"
        ' make no change
    End Select
  End If
  
  PT_ScaleChartAxis = "Sheet '" & SheetName & "' Chart '" & ChartName & "' " _
      & Choose(Primary_or_Secondary, "Primary", "Secondary") & " " _
      & Choose(X_or_Y, "X", "Y") & " Axis " _
      & "{" & Minimum & ", " & Maximum & ", " & MajorUnit & ", " & MinorUnit & "}"
  
ExitFunction:
  Exit Function
  
ErrorFunction:
  PT_ScaleChartAxis = sError
  GoTo ExitFunction
End Function

Custom Autoscaling

People often would like their own axis autoscaling algorithms over Excel’s. For example, Excel uses 0 as the axis minimum if the minimum in the data is less than 5/6 of the maximum in the data (see How Excel Calculates Automatic Chart Axis Limits for details). You could write your own algorithms in the worksheet, based on data used in the chart, or you could use my own algorithm from Calculate Nice Axis Scales in Your Excel Worksheet, and use the results in your formula that calls the chart UDF above.

I’ve added another UDF to Peltier Tech Charts for Excel 3.0 which uses my axis scale algorithm from Calculate Nice Axis Scales in Excel VBA. It looks like this:

=PT_AutoScaleChart(SheetName,ChartName,PriX,PriY,SecX,SecY)

Specify the sheet and chart names, then for each axis, specify

• 1, “autoscale”, “auto”, or TRUE to apply the custom autoscaling
• 0, “ignore”, “skip”, or FALSE to make no change to the axis
• -1, “default”, or “reset” to apply Excel’s default autoscaling

This function is also integrated into IntelliSense and the Function Arguments dialog. PriX and PriY are required, while SecX and SecY are optional. The function returns a descriptive message, indicating success or error.