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:

MinimumScale
MaximumScale
MajorUnit
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.