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