Change chart font using VBA

Question

How do I change the font of an Excel chart using VBA?

If I manually select the chart, and record a macro while I manually change the font name and size, I get the macro below. But when I immediately replay the macro, it throws a run-time error: "The specified value is out of range." So it looks like the macro recorder has a bug. Which means I can't figure out the code to change the font myself.

Sub Macro6()
'
' Macro6 Macro
'

'
    With ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font
        .NameComplexScript = "Verdana"
        .NameFarEast = "Verdana"
        .Name = "Verdana"
    End With
    ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font.Size = 14
End Sub

I know that as an alternative, I could change the font of each individual element one at a time (title, axis titles, axes, ...) but this is tedious and leaves open the possibility of forgetting some elements (series point labels, trendline equations, ...).

I'm looking to change the "default" font of the chart, such that all its elements will have that font.

Answer 1

Indeed that a strange error... Did the same, but I went to the Object Browser (F2) with the objective to work around with Chart and not Shape.

After some trying, I got this one to work :

With ActiveSheet.ChartObjects("Graph").Chart.ChartArea.Format.TextFrame2.TextRange.Font
    .Name = "Verdana"
    .Size = 14
End With

It's pretty simple, I tried more curious things (as there is a .Count property in TextRange2 class)

It is working just fine and does change the font of the entire chart,
you just have to know the name of your graph.

Alternatively, make sure the chart is selected, and use ActiveChart instead of ActiveSheet.ChartObjects("Graph").Chart.

Answer 2

The simplest way is:

ActiveChart.ChartArea.Font.Name = "Verdana"

Answer 3

This was also recorded and works for me (while the chart is selected):

Sub Macro1()
    Selection.Format.TextFrame2.TextRange.Font.Size = 14
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Verdana"
        .NameFarEast = "Verdana"
        .Name = "Verdana"
    End With
End Sub

Not really a great difference to your erroneous code.