Fixed X Axis w/ Variable Range

[rtc123]

Here's one I haven't seen:

How do you, using VBA, create a fixed number of x-axis tick marks (say, 10), even if the data range varies?

I have the following, but it doesn't seem to work:

objChart.Axes(xlCategory).CategoryType = xlCategoryScale
objChart.Axes(xlCategory).FixedScale = 10

 

[thisoldman]

I think this is what you want.

Toggle between automatic and fixed upper and lower bounds. Let Excel find the axis range in automatic mode, then use the Excel set values in fixed mode. Something like this:

Sub SetTenDivsOnXAxis()
  
    With ActiveChart.Axes(xlCategory)
        ' Let Excel set the default scale and tick values.
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .MajorUnitIsAuto = True
        
        ' Fix the values used for upper and lower bounds.
        .MaximumScale = .MaximumScale
        .MinimumScale = .MinimumScale
        
        ' Set the tickmark to one-tenth of the scale range.
        .MajorUnit = (.MaximumScale - .MinimumScale) / 10
    End With
    
End Sub

 

[rtc123]

Thanks thisoldman. What you have written makes sense, but doesn't quite work on my end. I currently have the following within an If statement:

objChart.Axes(xlCategory).MinimumScaleIsAuto = True
objChart.Axes(xlCategory).MaximumScaleIsAuto = True
objChart.Axes(xlCategory).TickLabelSpacingIsAuto = True

I have the ticklabelspacing comment blocked, with your code directly after the End If. Thoughts?

 

[thisoldman]

Tick label spacing seems to just affect the labels, not necessarily the grid line or tickmarks. At least that's the way I read the description: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/axis-ticklabelspacing-property-excel

Toggling between M...imumScaleIsAuto = True and then to False should do the same as setting the max and min bounds the way I wrote my code. I don't think setting TickLabelSpacing is the same as setting the MajorUnit. I would experiment on a test chart, if I were you. I won't get a chance to run any test code till tomorrow at the earliest.

 

[thisoldman]

When I test, .TickLabelSpacing does not affect the number of divisions. To change the number of x-axis divisions, I have to change the .MajorUnit.

 

[rtc123]

Thanks thisoldman. I originally used .TickLabelSpacingIsAuto to try to limit the number of labels on the x-axis. On the line charts I'm creating, the time series data can get quite large, and when it does, the x-axis gets way too crowded. I'll test .MajorUnit now, and will get back.

 

[rtc123]

So after testing, using .MajorUnit does not seem to alter the number of x-axis labels at all, even if I set it to a specific number. I'm not exactly sure if I understand your explanation in toggling between MinimumScaleIsAuto = True/False, and how that would relate to the number of labels. The objective is to keep the x-axis range minimum and maximum flexible for various ranges, but maintain a fixed number of x-axis labels. For example a range from Jan 1, 2015 to Jan 1, 2017 with daily data points would have the same number of x-axis labels as a range from Jan 1, 1980 to Jan 1, 2017...

 

[thisoldman]

I have been away for the holidays. I'm sorry to have not posted my unavailability in this thread. Did you get this solved?

 

[rtc123]

Hi thisoldman,

Thanks for the inquiry. Unfortunately no, I have yet to solve it. TickLabelSpacingisAuto = True seems to help for data ranges that are small (less than 1000 rows); but for large data pulls, the x axis is still crowded. The code you supplied makes sense, but the number of axis labels doesn't seem to respond. I find it strange that in excel you can provide a fixed spacing between axis labels, but not a fixed number of labels for a various ranges.

 

[Jon Peltier]

The number of labels is a function of the axis min and max and the tick spacing. Nlabels = (Max - Min) / Spacing + 1.

Are you using an XY Scatter type of chart, or a line chart? The line chart does not treat the axis labels as numerical values. Your problems seem to indicate you're trying to set a line chart X axis using scatter chart X axis commands.