“Not again…” I think to myself every time I change the minimum and maximum values of a chart axis. It’s such a tedious task, and I know I’ll be doing it again at a future point. It only takes a few seconds, but all that time starts to add up. There are various chart objects we can link to worksheet cells; source data, chart titles and data labels can all be linked to cells, but the chart axis is set by hardcoding a number into the Format Axis options window.
Well… I’m not so easily defeated. I decided to build a more dynamic solution. I turned to my old friend VBA, and started to tinker. Below you’ll find the result of that tinkering; a formula which exists on the worksheet to control the min and max values of a chart axis. Link that formula to a cell and suddenly it is possible to set the chart axis based on a cell value.
Hopefully, by the end of this post, you too can share in the automatic cell linked bliss I now experience.
If you’re not familiar with VBA, don’t worry, I’ll talk you through it step-by-step..
I wanted a solution which:
- Updates automatically whenever data changes
- Does not require user interaction – i.e. no button clicking, but updates automatically when the worksheet recalculates
- Easily portable between different worksheets
I think I managed to achieve this.
User Defined Functions (UDFs for short) are just like normal Excel functions, such as VLOOKUP or SUM, but they have been created using VBA. UDF’s are intended to be custom worksheet functions to calculate a cell value. Whether on purpose or by accident, Microsoft have made it possible to control various objects with UDFs. For example, it is possible to change a worksheet’s tab color, or to change a chart title (next week’s post). Thankfully, the minimum and maximum values of the chart axis are controllable using a UDF. Horray!
The solution in action
The animated gif below shows the solution in action. The values in the cell are automatically applied to the chart. Whether these values are typed in the cell or created using formulas, they will update the chart.
Right-click on a blank part of the ribbon and select Customize the Ribbon… from the menu
From the Excel Options window click Customize Ribbon, enable the Developer option then click OK.
Creating the User Defined Function
To create the UDF click Developer -> Visual Basic (or shortcut ALT + F11).
The Visual Basic Editor window will open, click Insert -> Module
Add the code below to the Module as shown. The code for UDFs must be within a standard module to work.
The VBA Code
Here is the VBA code to copy into the Module.
Function setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _ ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant) 'Create variables Dim cht As Chart Dim valueAsText As String 'Set the chart to be controlled by the function Set cht = Application.Caller.Parent.Parent.Sheets(sheetName) _ .ChartObjects(chartName).Chart 'Set Value of Primary axis If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ And PrimaryOrSecondary = "Primary" Then With cht.Axes(xlValue, xlPrimary) If IsNumeric(Value) = True Then If MinOrMax = "Max" Then .MaximumScale = Value If MinOrMax = "Min" Then .MinimumScale = Value Else If MinOrMax = "Max" Then .MaximumScaleIsAuto = True If MinOrMax = "Min" Then .MinimumScaleIsAuto = True End If End With End If 'Set Category of Primary axis If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _ And PrimaryOrSecondary = "Primary" Then With cht.Axes(xlCategory, xlPrimary) If IsNumeric(Value) = True Then If MinOrMax = "Max" Then .MaximumScale = Value If MinOrMax = "Min" Then .MinimumScale = Value Else If MinOrMax = "Max" Then .MaximumScaleIsAuto = True If MinOrMax = "Min" Then .MinimumScaleIsAuto = True End If End With End If 'Set value of secondary axis If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ And PrimaryOrSecondary = "Secondary" Then With cht.Axes(xlValue, xlSecondary) If IsNumeric(Value) = True Then If MinOrMax = "Max" Then .MaximumScale = Value If MinOrMax = "Min" Then .MinimumScale = Value Else If MinOrMax = "Max" Then .MaximumScaleIsAuto = True If MinOrMax = "Min" Then .MinimumScaleIsAuto = True End If End With End If 'Set category of secondary axis If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _ And PrimaryOrSecondary = "Secondary" Then With cht.Axes(xlCategory, xlSecondary) If IsNumeric(Value) = True Then If MinOrMax = "Max" Then .MaximumScale = Value If MinOrMax = "Min" Then .MinimumScale = Value Else If MinOrMax = "Max" Then .MaximumScaleIsAuto = True If MinOrMax = "Min" Then .MinimumScaleIsAuto = True End If End With End If 'If is text always display "Auto" If IsNumeric(Value) Then valueAsText = Value Else valueAsText = "Auto" 'Output a text string to indicate the value setChartAxis = ValueOrCategory & " " & PrimaryOrSecondary & " " _ & MinOrMax & ": " & valueAsText End Function
That’s all we need to do to create the UDF, now it’s ready to use.
Using the function
The function we have created uses the following syntax:
=setChartAxis(sheetName, chartName, MinOrMax, ValueOrCategory, PrimaryOrSecondary, Value)
- sheetName = the name of the worksheet which contains the chart.
- chartName = the name of the chart as a text string, or a cell reference to a text string. If you’re not sure of a chart’s name, click on the chart and look at the NameBox (the box to the left of the formula bar).
- MinOrMax = a text string of “Min” or “Max” to select whether the function will control the Minimum or Maximum axis value.
- ValueOrCategory = a text string of “Value” or “Category” to indicate which axis to adjust (will also accept “X” or “Y”)
- PrimaryOrSecondary = a text string of “Primary” or “Secondary” to indicate which axis to adjust.
- Value = a number, or a text string. A number will adjust the axis to that value, “Auto” will reset the axis to Automatic.
For the function the work, the chart must be in the same workbook as the function.
Unlike normal functions, UDF’s do not display the order of the arguments when typed into the formula bar. Therefore, the best options are:
- Start typing the UDF then press Ctrl + Shift + A after typing the first bracket, the arguments will appear.
- Click on the fx button in the formula bar. In the Insert Function window select the function under the User Defined list, then click OK.
The Function Arguments window will show the order and names of the arguments.
The screenshot below shows how the UDF is used:
The formula in Cell G12 is:
Resetting back to default axis
If you decide to revert back to Excel’s automatic axis calculation, that is no problem. Any non-numeric value entered as the Value (or in the cell reference linked to the value) will cause the chart to be reset to automatic.
Download the example file
Sometimes it is easier to understand by seeing the UDF in action.
Making the function available in all workbooks
If you wish to make the UDF available in all your workbooks:
- Include the code above within a new workbook
- Save the workbook as an Excel Add-in (.xlam file type).
- Install the Add-in you have created by following these instructions.
The function is now available in all your workbooks. If others open the workbook they will be able to see the function, but it will not work. If you want other users to use the function save it in each individual file.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: