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 FunctionWrite 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.
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:
Scaling of Embedded Chart Axes
The first routine controls scaling of axes in charts which are embedded in worksheets. 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 FunctionScaling of Chart Sheet Axes
Several readers have asked about modifications which would allow this routine to work for standalone chart sheets, not charts embedded in worksheets. So here is the adjusted code, which works for either type of chart. Use the function as above for a chart ‘ChartName’ embedded in a worksheet ‘SheetName’, or for a chart sheet ‘SheetName’ (leave ChartName blank).
Function ScaleChartAxis(SheetName As String, ChartName As String, X_or_Y As Variant, _
Primary_or_Secondary As Variant, Optional Minimum As Variant, _
Optional Maximum As Variant, Optional MajorUnit As Variant, _
Optional MinorUnit As Variant) As Variant
' Text values for Minimum, Maximum, MajorUnit, MinorUnit:
' auto, autoscale, default --> use Excel default autoscaling
' null, blank, ignore, skip --> don't change the parameter
Application.Volatile True
If IsMissing(Minimum) Then Minimum = vbNullString
If IsMissing(Maximum) Then Maximum = vbNullString
If IsMissing(MajorUnit) Then MajorUnit = vbNullString
If IsMissing(MinorUnit) Then MinorUnit = vbNullString
If Len(SheetName) = 0 Then
Dim rCaller As Range
Set rCaller = Application.Caller
SheetName = rCaller.Parent.Name
End If
On Error Resume Next
Dim wks As Worksheet
Set wks = Worksheets(SheetName)
On Error GoTo 0
If wks Is Nothing Then
On Error Resume Next
Dim cht As Chart
Set cht = Charts(SheetName)
On Error GoTo 0
If cht Is Nothing Then
Dim sError As String
sError = "Sheet '" & SheetName & "' not found"
GoTo ErrorFunction
End If
Dim sReturn As String
sReturn = "Chart '" & SheetName & "'" '' jp chart sheet
Else
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
sReturn = "Sheet '" & SheetName & "' Chart '" & ChartName & "'"
End If
Select Case LCase$(X_or_Y)
Case "x", "1"
X_or_Y = xlCategory
'' but not for non-value axes
Case "y", "2"
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
Dim ax As Axis
Set ax = cht.Axes(X_or_Y, Primary_or_Secondary)
If ax.Type = xlCategory Then
On Error Resume Next
Dim vTestCategory As Variant
vTestCategory = ax.MinimumScale
Dim iError As Long
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 Len(Minimum) = 0 Then
Minimum = "null"
ElseIf IsNumeric(Minimum) Or IsDate(Minimum) Then
Dim dMinimum As Double
dMinimum = Minimum
Dim bSetMin As Boolean
bSetMin = True
Else
Select Case LCase$(Minimum)
Case "auto", "autoscale", "default"
ax.MinimumScaleIsAuto = True
Case "null", "skip", "ignore", "blank"
' make no change
Case vbNullString
Minimum = "null"
' make no change
End Select
End If
If Len(Maximum) = 0 Then
Maximum = "null"
ElseIf IsNumeric(Maximum) Or IsDate(Maximum) Then
Dim dMaximum As Double
dMaximum = Maximum
Dim bSetMax As Boolean
bSetMax = True
Else
Select Case LCase$(Maximum)
Case "auto", "autoscale", "default"
ax.MaximumScaleIsAuto = True
Case "null", "skip", "ignore", "blank"
' make no change
Case vbNullString
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 Len(MajorUnit) = 0 Then
MajorUnit = "null"
ElseIf 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 vbNullString
MajorUnit = "null"
' make no change
End Select
End If
If Len(MinorUnit) = 0 Then
MinorUnit = "null"
ElseIf 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 vbNullString
MinorUnit = "null"
' make no change
End Select
End If
ScaleChartAxis = sReturn & " " _
& Choose(Primary_or_Secondary, "Primary", "Secondary") & " " _
& Choose(X_or_Y, "X", "Y") & " Axis " _
& "{" & Minimum & ", " & Maximum & ", " & MajorUnit & ", " & MinorUnit & "}"
ExitFunction:
Exit Function
ErrorFunction:
ScaleChartAxis = sError
GoTo ExitFunction
End FunctionCustom 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 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.
More Axis Scale Articles
- Calculate Nice Axis Scales with LET and LAMBDA
- Calculate Nice Axis Scales in Your Excel Worksheet
- Calculate Nice Axis Scales in Excel VBA
- Chart UDF to Control Axis Scale
- How Excel Calculates Automatic Chart Axis Limits
- Reciprocal Chart Axis Scale
- Custom Axis Labels and Gridlines in an Excel Chart
- Custom Axis, Y = 1, 2, 4, 8, 16
- Logarithmic Axis Scales
- Link Excel Chart Axis Scale to Values in Cells
- Consistent Axis Scales Across Multiple Charts
- Gantt Chart with Nice Date Axis
- Select Meaningful Axis Scales
- Bar Chart Value Axis Scale Must Include Zero
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 = dMinimuminPT_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.
NOTE: I initially left out a few lines that actually applied the axis min and max.
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
DoEventsright 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.
Kris says
Hi Jon,
I have a small issue with the UDF. I run a vba macro for specific worksheet, all values in worksheet updates and seems that UDF is referring to correct cells…Values in cells (my min/max range) updates without any problem, but the axis range not. I’m not sure if it is a problem because I’m running macro written for my worksheet or something else I do wrong. Thanks for your help!
Kris says
It looks like, one excel formula is influencing other opened workbooks…any suggestions?
Gurs says
Love this UDF concept but I can’t get it to work. The worksheet cell with the UDF returns the error “Cannot scale a category-type axis”. My chart has a horizontal (X) axis that is a range of dates with a custom format (“dd-mmm”). Is there anything I can change about my chart or the UDF so that the UDF will modify the X-axis bounds and units? Thanks!
Gurs says
I should add that I can manually modify the horizontal axis bounds and units of this chart by right-clicking on the axis and selecting Format Axis.
Gurs says
OK, silly mistake above, but still not working. The chart’s horizontal access is the X-axis, which is not a category-type axis. When I changed the input to X-axis, the error goes away and the ScaleChartAxis UDF returns a value (Sheet ‘Timeline’ Chart ‘TimelineChart’ Primary Y Axis {43644, 43714, 15, 3}). But the chart’s bounds and units do not update, even after clicking into and entering the ScaleChartAxis cell. If I run a macro to update the chart bounds and range (per your earlier post), the chart updates fine.
Gurs says
OK, silly mistake above, but still not working. The chart’s horizontal access is the Y-axis (Value), which is not a category-type axis. When I changed the input to Y-axis, the error goes away and the ScaleChartAxis UDF returns a value (Sheet ‘Timeline’ Chart ‘TimelineChart’ Primary Y Axis {43644, 43714, 15, 3}). But the chart’s bounds and units do not update, even after clicking into and entering the ScaleChartAxis cell. If I run a macro to update the chart bounds and range (per your earlier post), the chart updates fine.
Barbara Oliveira says
Hi John,
thank you for sharing your UDF. It works like a treat.
I’m not fluent in VBA, but I was wondering if it would be possible to have the function work on graphs that are in chartsheets as opposed to worksheets?
I have a workbook with a worksheet that controls several graphs, each displayed in its own chartsheet, and it would be ideal to be able to control them from the worksheet.
Many thanks!
Jon Peltier says
Barbara –
In the comments, see my reply to Sudhakar’s question on September 13, 2018. I modified the function to accommodate chart sheets and embedded charts.
Jon Peltier says
Gurs –
I just built a simple example of a Gantt chart, stacked bars with a horizontal Y axis that displays dates. I entered my axis scale values into some cells and my UDF into another cell. The chart updated instantly. When I changed any of the cells used for inputs into the UDF, the UDF and the chart updated instantly.
Can you share your workbook?
Steven Scott says
This took some work to implement but I finally got it to work with my dataset (I’m no VB expert…). Problem is, while it works initially, when I re-sort my dataset via a filter, it causes a crash. I assumed it was memory issue (my file is 36mb), however, removing the module allows the re-sort to complete with no issues.
I updated your simpler code to only adjust for max and major unit, however, your simpler code does not re-run when the input values change. I’m not savvy enough to adjust your complex code, however, I may have to give it a try…
Glen Koehler says
Much appreciation for your sharing your expertise for this Jon.
1) There is typo in the stand alone Chart version.
Where it says
“If bSetMin And bSetMax Then
If dMaximum 0 Then
ax.MajorUnit = MajorUnit
End If” etc.
THE = sign is missing.
2) There is second typo in the stand along chart version
If ax.Type = xlCategory Then
On Error Resume Next
vTestCategory = ax.MinimumScale
iError = Err.Number
On Error GoTo 0
If iError 0 Then ”
THE is missing
3) Also when using the new function in Excel 365, if I leave an entry blank it does not work, I have to enter “”
then it does work. Maybe that’s common knowledge, but it had me scratching my head.
4) I should not complain given that there are experts like you providing this solution, but … why doesn’t Microsoft make this a built-in function? There must be MANY people who want dynamic control of chart axis ranges.
Jon Peltier says
Hi Glen –
Thanks for the detailed report.
1 & 2: Those aren’t mere typos, there’s some incorrect logic as well. I suspect I pasted in the code and made some “minor” changes in the blog editor. I went back to my commercial software, copied the production version of the code, and pasted it here in place of the faulty routine.
3: Notice that the scale parameters are Optional in the new code.
4. We have been asking, nay, begging, for this for years. How hard would it be to replace the existing text boxes in the Format Axis task pane with the edit boxes that allow us to enter values or ranges in other dialogs?
Kevin Murphy says
Hi Jon,
Apologies for resurrecting this UDF article, but nearly four years on and it remains excellent!
I have a related question that I’m hoping you can help with. I have two different data sets, and I switch between them using a slicer. The axis now scales perfectly and all data is displayed. However, the axis labels are not as tidy as I would like. i.e. one data series is in % and the other is integers up to 50; thanks to your UDF, the axis scales to fit in the larger data, but the axis labels remain in % terms (i.e. they read 5,000% rather than 50). Is there a way to have the formatting update at the same time as the min/max?
Thank you,
KM
Jon Peltier says
Hi Kevin –
Try this. Check the box so the axis number format matches the data, and make sure the data is formatted the way you want it to appear in the chart. When you click the slicer, the data in the pivot changes and so should the format, and the matching data and format in the chart should also change.
Kevin Murphy says
Appreciate you taking the time to try and help Jon. I’m afraid that doesn’t quite work, but it’s because my use case is pretty niche. My slicer changes the column header of the data for the Y axis, and an Xlookup uses the ‘sliced’ title to fetch the required data from a large number of potential columns in a ‘data warehouse’. Whilst I can format each of the columns in the data warehouse appropriately, the raw data isn’t what feeds the chart. The data comes into the xlookup column, and it’s the format of the xlookup column that feeds the chart if it’s linked. As the format of the xlookup column doesn’t change with the slicer (the slicer only does the header) the axis doesn’t update based on the data.
No need for you to investigate further if it’s not easy, but thought it would be polite to at least reply and to show my appreciated for the axis scaling which is 95% of what I needed.
Thank you!
Glen W Koehler says
At risk of flaunting my ignorance in public — Given that the UDF can change the scaling based on input data but not the chart formatting, I wonder if you could create separate charts for each potential formatting scenario. Then have the final chart dynamically pull the chart desired for each situation. For example, create chart “A” that is set to properly display the data as percent values, and another chart “B” set to display absolute amounts. Then a final chart that is dynamically defined to pull either A or B depending on whether the desired data display is percent or absolute values. The slicer on a dashboard would be linked to the final chart, and thus shows the desired formatting which has been selected at a previous level.
I would also like to thank Jon Peltier for sharing the UDF solution. Baffles me why Microsoft would not make this a built-in Excel function.
Jon Peltier says
As a matter of fact, the scaling is part of a chart’s formatting, and so is the number format. This UDF approach can be extended as far as you like, including the number format. Here’s an updated UDF that includes number format as another optional argument.
Kevin Murphy says
Firstly, thanks to Glen K for taking the time to try and resolve my problem. The internet can be amazing place full of kind people who go out of their way to help others. It’s much appreciated. Jon’s updated UDF is, however, a little cleaner and works perfectly. Thank you so much for your help Jon, it works perfectly.
For anyone that isn’t an excel black belt (like me) that is trying to get the number formatting to work properly, the answer is almost definitely an extra #. https://www.exceltactics.com/definitive-guide-custom-number-formats-excel/ is useful, as the codes excel displays in custom within the “format cells” dialog are insufficient. e.g. to get a percentage, you need to use #%, rather than %, “percentage” or 0.0% etc. etc.
This will be obvious to many of you, but it wasn’t to me, so it may be helpful for anyone struggling.
Thank you!
Franziska Peters says
Thank you for giving us all these functions and trying to help everyone.
I have implemented (an older version, I think) of your function. Which is great as long as the sheet is not locked. I’ve found some posts mentioning the same problem, but I have not found any answer on how this problem could be solved. Do you know if anyone has found a solution?
Is there any way to lock sheets and still use the function?
Would possibly changing from function to sub work?
Jon Peltier says
Franziska –
This is a problem with Excel and charts. Whether in a function or in a sub, certain properties are not available to VBA if the parent worksheet is protected. Such properties include the axis scale.
This is true even if I unlock the chart, or I allow the user to edit objects when I protect the worksheet, or if I use UserInterfaceOnly:=True when protecting the worksheet with VBA.
Your code can unprotect the worksheet first, make the changes, then reprotect the worksheet, but if the code is a UDF called by the worksheet, it will ignore the Unprotect command, because a UDF is prohibited from changing anything except the parent cell’s value. The fact that a UDF can change the chart is itself a bug, since it violates this prohibition.
If you use the WorksheetChange event to detect a change in a cell to trigger the chart modification (see Link Excel Chart Axis Scale to Values in Cells), the unprotect and reprotect commands are allowed, and everything is okay. Provided the code knows the worksheet’s password.