Search code examples
vbaexcelchartsaxis-labels

How do I make sure chart axis tick labels are horizontal and correctly spaced?

I am using VBA to create charts from different data ranges. I want the x axis major units to be set such that the tick labels are automatically horizontal. However, using automatic major units and orientation/rotation, the labels will sometimes rotated and sometimes horizontal. If I set the orientation to 0 I get horizontal labels, but then they don't always fit my chart area. So what I need to do after I have created a new chart is to:

  1. Check if the automatic orientation has created horizontal tick labels
  2. If yes, do nothing. If not, increase the major units one step
  3. Repeat from 1.

The problem is that I don't know how to check if the automatic orientation has been set to horizontal. If I check the value of

ChartObjects(1).Chart.Axes(xlCategory).TickLabels.Orientation

I get -4105, which is the value of the automatic setting (ref https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlticklabelorientation-enumeration-excel) regardless of the actual rotation value.

Is there any way to check the actual rotation of the tick labels even though it is set automatically? Or is there any other way to solve this issue? I'm thinking that maybe there is a way to set the orientation to 0 and force the major units to change so that the labels fit inside the chart area?

Solution

  • The issue was solved by making the chart a little bit smaller than the ChartArea - that way the labels will always fit when they are set to horizontal.

    The issue of checking the actual orientation when the setting is automatic is however still unsolved...