Chart Gallery
Video Tutorials
Trend Analysis
Using R
Data Visualization
 Global Warming
Map Excel Data with Google Earth
Chart Doctor
Advanced Chart Techniques
What's New
 Search Site RSS
   04/4/2008 4:30 PM


        Set X Axis Min & Max Dates

This tutorial shows you how to set X Axis Minimum and Maximum dates as well as Date Format and Interval for an XY Trend Chart with VBA. It is based on example found in Trend_Control_Chrt_Basics.XLS.

1.  Controlling  X Axis Date Range, Interval and Date Format

There are four (4) X Axis properties that we frequently want to adjust in XY trend charts:

  1. MinimumScale
  2. MaximumScale
  3. MajorUnit
  4. Ticklabels.NumberFormat

We can develop a simple VBA procedure, X_Axis, to set these X Axis properties for the  Chart.Axes(xlCategory, xlPrimary) of ChartObject(1) on the ActiveSheet.

Public Sub X_Axis()
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory, xlPrimary)
        .MinimumScale = Range("start")
        .MaximumScale = Range("end")
        .MajorUnit = Range("major_unit")
        .TickLabels.NumberFormat = Range("date_format")
    End With
End Sub

The X_Axis procedure will set the MinimumScale, MaximumScale, MajorUnit and Ticklabels.NumberFormat for the X  axis using values in the range names: start, end, major_unit and date_format respectively.


 2. X Axis Data Table

A simple data table is one way to provide this information. In the Trend_Control_Chart_Basics example, the User enters the min and max dates in Cells B17:C17.The interval is entered in Cell B18 and the number Format is entered in Cell B19. The number format is entered by selecting from a data validation list of available data formats.


Another way to get user input on the 4 X axis parameters is with a userform. The Stacked_Trend_Chart.XLS example includes a userform for chart query dates:


3. Range Names

Range names have been defined for the four x Axis properties.








4. Dynamic Title

The dynamic chart title has been developed with the concatenation formula in Cell A31. As the dates change, the chart title will automatically change.