Excel Macro for Primary Axis Labeling

Question

I'm trying to write a macro to format charts for uniformity. I'm stuck on what I thought would be the easiest part. Labeling the axes. It won't label the Primary Value axis (y-axis). I've recorded this macro many times on 2010 and 2013. What is wrong? I run the macro and it debugs on line 3 almost as if the label didn't exist. Running just the first 2 lines proved this. Any help?

    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Primary Y-Axis"
      With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
      End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 14).Font
      .BaselineOffset = 0
      .Bold = msoTrue
      .NameComplexScript = "+mn-cs"
      .NameFarEast = "+mn-ea"
      .Fill.Visible = msoTrue
      .Fill.ForeColor.RGB = RGB(0, 0, 0)
      .Fill.Transparency = 0
      .Fill.Solid
      .Size = 10
      .Italic = msoFalse
      .Kerning = 12
      .Name = "+mn-lt"
      .UnderlineStyle = msoNoUnderline
      .Strike = msoNoStrike
    End With

Answer 1

An obvious source of runtime errors is relying on the recorded macros. They usually require some tweaking. In your case, I believe the problem may be that the axis' .HasTitle property is still set to False, which would raise that error when you try to access the .AxisTitle properties.

NOTE: You can read this to learn about why the Select and Activate methods are problematic.

In this code, I define some variables to represent the chart and the axis, and make sure that the .HasTitle property is true. No errors encountered. :)

Sub foo()
Dim cht As Chart
Dim ax As Axis

Set cht = Sheet1.ChartObjects(1).Chart '# modify as needed

cht.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

'# set your axis in a variable
Set ax = cht.Axes(xlValue, xlPrimary)
'# Make sure your axis HAS a title
ax.HasTitle = True
With ax.AxisTitle.Format.TextFrame2.TextRange
    .Characters.Text = "Primary Y-Axis"
    With .Characters(1, 14).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
End With

End Sub

Answer 2

I know this is old, but I had the same problem (for the actual axis label, not the title) and found this solution: instead of using msoElementPrimaryValueAxisTitleAdjacentToAxis in the SetElement statement use the number 307.

In my case, msoElementPrimaryValueAxisTitleAdjacentToAxis was being interpreted as 306, which corresponds to msoElementPrimaryValueAxisTitleNone (setting axis to none). So, after turning off the axis, when you attempt to modify it, you get an error since you can't modify something that is not there.

See here for reference: MsoChartElementType enumeration

Answer 3

Recorded macros will lead you down some nasty rabbit holes. For example:

cht.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

SetElement is poorly documented and is buggy. The recorder gives you unnecessary parentheses around the argument, which is only a named constant anyway.

Another problem with recorded macros is you get all the defaults unnecessarily listed, plus some useless stuff, like

  .NameComplexScript = "+mn-cs"
  .NameFarEast = "+mn-ea"

Wut?

It's better to just use the element-by-element syntax and do what you need explicitly, instead of relying on some magical, possibly misnamed, catch-all constant.

Use the Obect Browser, but first, right click on it and select Show Hidden Members. It shows some supposedly deprecated members, but come on, they're not going to actually remove AxisTitle.Font.Bold in favor of AxisTitle.Format.TextFrame2.TextRange.Font.Bold.

So all you need to get your axis title formatted the way you want is this:

Sub FormatAxis()
  With ActiveChart
    .HasAxis(xlValue, xlPrimary) = True
    With .Axes(xlValue, xlPrimary)
      .HasTitle = True
      With .AxisTitle
        .Text = "Primary Y Axis"
        With .Font
          .Bold = True
          .Size = 10
        End With
      End With
    End With
  End With
End Sub