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
DoEvents
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.
Dave Hockin says
Thanks for the article and code Jon. Can you explain the benefits of the UDF as compared to a Sub routine.
As I see it the UDF enables you put values in cells to drive the outcomes. And since the UDF requires a code module you have to have a VBA enabled file. Why not simply drive the outcomes by running a macro?
Is there a user benefit to UDF, or some other logic benefits? To be clear I am not critical of the innovative approach, just trying to understand any limitations of either approach. Thanks
Jon Peltier says
Dave –
A regular behind-the-scenes VBA procedure is not as easy for the user, especially a less-savvy user, and those famous VBA error messages will not help. A UDF, on the other hand, is much easier to use. With IntelliSense, the user can see what to enter, like any other function. And with the smart responses from the UDF, it’s easier to see what’s wrong.
Bob says
Hi Jon,
I’ve been using the simpler versions to control date scales with XY charts for nice Gantt charts with milestones and labels.
Your versions are very comprehensive. Wow.
Cheers
Bob
Mark - Excel Off The Grid says
Hi Jon,
This is really good stuff. The error checking certainly helps users to understand how to use the UDF better. I wish all Excel functions had error messages like this.
Having everything in a single formula keeps the spreadsheet impact to a minimum. This is definitely an improvement on my original post :-)
Hopefully all your add-in users will find this useful too.
I have used your Custom Scaling Macro for years, but it didn’t occur to me to UDF this too, that is a great add.
Mark
Dave Dudus says
Nice work John. How can I update my version of Advanced 3.0 to incorporate these additions ?
Jon Peltier says
Dave –
I’ve got a new build coming very soon that has these functions plus some other goodies. I’ll post a link here when it’s available.
Dave D says
Great! Thanks for the update, Jon!
Chris Rodrigues says
Why make the function volatile? It doesn’t look like it needs to be. It only needs to run when one of the axis values changes, and you can let Excel’s smart recalculation handle it from there. If the UDF is volatile it will do a lot of unnecessary manipulation of the chart and potentially slow things down.
Jon Peltier says
Chris –
That’s a good point. I think I copied that from Matt’s version, and I did wonder whether I really needed it. In fact, I have a comment about that right in the code:
But I haven’t removed it yet.
rozani says
Please make it fully volatile.
this function is useless when cell is lock.
please help.
Jon Peltier says
Rozani –
That’s a good point. When I protect the worksheet, the UDF results in a #VALUE! error, even if the cells containing the UDF and the chart are unlocked. Even if I check Edit Objects when protecting the worksheet. The code fails the first time it tries to manipulate the chart (
ax.MinimumScale = dMinimum
inPT_ScaleChartAxis
).I don’t know why this happens. But in fact, it happens in the UDF called from a worksheet cell, and it happens when the same command is executed in a regular VBA procedure with protection on.
Sudhakar says
HI
Nice tip to get the scales linked to cell values. But how to utilise this if the chart is a separate chartsheet and not present in a worksheet.
thanks
Jon Peltier says
Sudhakar –
That’s a good question. I haven’t used chart sheets since I stopped using Excel 2003, so I completely forgot.
I have made some adjustments to the code. Please note that I have not tested this extensively, but only a handful of cases. If you find an error or if I’ve left something out, let me know.
The sheet name entered in the function is the worksheet that contains the embedded chart or it is the chart sheet. If no sheet name is entered, the function uses the worksheet that contains the cell with the formula.
The chart name when entered it the name of the chart object which is embedded in the worksheet or in the chart (yes, you can embed a chart object in a chart sheet).
If the chart name is not entered, the code will format the first chart object on the worksheet, or the chart sheet itself.
If a chart name is entered which does not correspond to a chart object on the sheet, if the sheet is a worksheet, the function returns an error message; if the sheet is a chart sheet, the function formats the chart sheet itself.
Paulo Giolo says
This is brilliant! Thank you for sharing.
David Chang says
I used your UDF to adjust the axis properties on a bar chart and it works great. However, when I add a couple of option buttons and link them to a cell, my worksheet crashes when selecting one of the option buttons. Why is that?
I tried to use the 2 option buttons without your UDF and it works fine. As soon as I add your UDF, selecting an option button crashes excel. I’m simply going into Visual Basic, doing Add Module and pasting your code into the text window.
Is there something else I should be doing?
Jon Peltier says
David –
When I get a chance, I’ll check into this. What were you using the option buttons for? Are they Form Controls or ActiveX Controls?
Jon Peltier says
David –
Here’s what I got:
When I clicked OK, Excel quit.
I’ll see what else I can find out.
David Chang says
Jon,
I don’t know how to reply to the e-mail I was sent, but I got your responses. I’m using Forms option buttons and I got the exact same error about not enough resources and try using the 64-bit version of Excel. Then, based on your other response, I tried the Active-X option buttons and they work fine. So, I’m good. Thanks for the fantastic information and sharing your knowledge.
Jon Peltier says
David –
I was able to crash Excel about half a dozen times.
I tried a remedy: I added
DoEvents
right before
Application.Volatile True
(see the code listing above).
This seems to have stopped the error from happening.
Jon Peltier says
David –
Glad to hear the ActiveX controls work. Keep in mind though that ActiveX controls have their own flaky behaviors, and won’t work on a Mac. The DoEvents approach seems stable enough.