Making axis title in Excel chart with VBA

Question

I create an excel chart with VBA and then format the axis titles and fonts' size. The following code works well for the Horizontal axis

cht.SetElement msoElementPrimaryCategoryAxisTitleAdjacentToAxis
cht.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Factor of Safety"
cht.Axes(xlCategory, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

However, the similar code for the Vertical axis

cht.SetElement msoElementPrimaryValueAxisTitleAdjacentToAxis
cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Depth [mCD]"
cht.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

leads to the error 424 "Object required". Could you please tell me what happened ?

Answer 1

In recent versions of Excel, you can use SetElement with a named constant to add features to the chart. This seems easier, but it's less intuitive in terms of what it actually does, and it can be unreliable.

So instead of this:

cht.SetElement msoElementPrimaryValueAxisTitleAdjacentToAxis
cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Depth [mCD]"
cht.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

do this:

cht.Axes(xlValue, xlPrimary).HasTitle = True
cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Depth [mCD]"
cht.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15