Books at Amazon.com
Link Chart Axis Scale Parameters to Values in Cells.
Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. Wouldn't it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? This page shows how to use VBA to accomplish this.
There are a few pieces you need to make this technique work. You need a chart, a set of values for the scaling parameters, and some VBA code to change the axis scales. The code can be either linked to a button, or run from a Worksheet_Change event procedure.
The actual mechanics of creating this chart are unimportant to the discussion. For this example, you need a simple XY Scatter chart, which has the primary X and Y axes to be changed by the code below.
Preparing the Scaling Parameters in the Worksheet
In this example, the range D1:F4 is used to hold primary X and Y axis scale parameters for the embedded chart object named "Chart 1". This example can be expanded to include secondary axes, or to change other charts as well.
The cells E2:F4 can contain static values which the user can manually change, or they can contain formulas with your favorite axis scaling algorithms.
Changing Chart Axes in VBA
The parts of Excel's charting object model needed here are the .MinimumScale, .MaximumScale, and .MajorUnit properties of the Axis object (the .MinorUnit property could also be controlled, but I usually do not show minor tick marks). These properties can be set equal to constant values, to VBA variables, or to worksheet range or named range values.
With ActiveChart.Axes(xlValue, xlPrimary) .MaximumScale = 6 ' Constant value .MinimumScale = dYmin ' VBA variable .MajorUnit = ActiveSheet.Range("A1").Value ' Worksheet range value End With
If you have a Line, Column, or Area chart with a category-type X axis, you can't use the properties shown above. The maximum and minimum values of a category axis cannot be changed, and you can only adjust .TickLabelSpacing and .TickMarkSpacing. If the X axis is a time-scale axis, you can adjust .MaximumScale, .MinimumScale, and .MajorUnit. You should turn on the macro recorder and format an axis manually to make sure you use correct syntax in your procedure. Any chart's Y axis is a value axis, and this code will work as is.
The Worksheet_Change Event
The Worksheet_Change event procedure fires whenever a cell in the worksheet is changed. If you right click on a worksheet tab and select View Code from the pop up menu, you will be directed to the Visual Basic Editor (VBE), and the code module for the worksheet is opened. Select Worksheet from the left hand dropdown, and Change from the right hand dropdown, and the following procedure skeleton appears. This is the Worksheet_Change event procedure, and you insert the relevant code into it. The argument Target within parentheses tells the procedure which cell has been edited.
Private Sub Worksheet_Change(ByVal Target As Range) End Sub
The completed event procedure, shown below, uses Select Case to determine which cell was changed, then changes the appropriate scale parameter of the appropriate axis.
Run as a Regular Macro
You can adapt the code under 'Changing Chart Axes in VBA' above to run within a regular macro, which can be triggered from a button or from the Macro submenu of the Tools menu. This macro should not be in the Worksheet module, but in a regular code module; use an existing regular code module, or select Module from the VBE's Insert menu. Here is an example of such a macro:
Peltier Technical Services, Inc., Copyright © 2013. All rights reserved.