How to set label alignment on chart axes?

4
votes

I have to make column charts in Excel using VBA only (no user input).

I want to format the labels of the x-axis so that the alignment for every label becomes -270 degrees. This can be done manually by changing the "Custom angle" property in the "Alignment" tab of the "Format Axis" Dialog.

I recorded a macro but Excel does not seem to be recording the alignment step.

3
excelvbachartsaxis

3 Answers

4
votes

If you are using Excel 2007, try using an earlier version because 2007's macro recorder is a bit crippled.

This is what I got:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.Orientation = xlUpward
1
votes
ActiveChart.Axes(xlCategory).TickLabels.Orientation = 67
0
votes

About .Activate

Old school VBA macros often lead people to think you have to "activate" and object to make it "current". Like you click a graphical representation and everything you do with mouse and keyboard is received by the activated object. The "macro generator" for the Microsoft Office products is to blame for this coding style.

However, in the world of VBA programming or using the Office API;s from C# or other popular languages — that is not how it should be done.

To modify a chart object, line etc, you can operate on the object itself by its object reference. Once you obtained it you can access all the methods and properties and "activation" is not part of the concept. If you by some reason actually want to have the object receive some focus for the user to see it — visual objects normally have an "Activate()" function to call.

In this example the line width of multiple series in a chart is set to 1:

Sub Change_all_charts()
    Dim ch As chart
    Dim ws As Worksheet
    Dim ChtObj As ChartObject
    Dim srs As Series

    'Get a reference to the active worksheet
    Set ws = ActiveSheet

    'Loop each chart object in worksheet ws
    For Each ChtObj In ws.ChartObjects

        'Operate on 'ChtObj.chart' object using . notation
        With ChtObj.chart
            Debug.Print "Chart being modified: '" & .Name & "'"
            'Loop through all series in the chart .Series collection
            For Each srs In .SeriesCollection
                Debug.Print "  Series being modified: '" & srs.Name & "'"
                'Set the series line width
                srs.Format.Line.Weight = 1
            Next
        End With
    Next
End Sub

In the old days (or even still) financial people do Excel programming and make their worksheets flash and animate like crazy because they think and object needs to receive "focus" to be modified.

Well now it has been said as it has been "bothering" me for the last 40 years.