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:
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.
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.