excelvbaexcel-chartsoffice-2016

Setting chart title only works while debugging in Excel 2016


The problem can be summarized like this: enter image description here This problem only occurs if I run the code, not when I step through the chrt.HasTitle-line. In that case, it correctly creates a title and I can edit it however I want.


I'll first give the relevant source code. This simply includes the code that distinguishes Excel 2003 from newer versions (since Excel 2003 crashes on the 'newer' code and vice versa).

Sub EnableChartTitle(chrt As Chart)
    If Application.Version = "11.0" Then
        EnableChartTitle_2003 chrt
    Else
        EnableChartTitle_Post2003 chrt
    End If
End Sub

Sub EnableChartTitle_2003(chrt As Chart)
    chrt.HasTitle = True
End Sub

Sub EnableChartTitle_Post2003(chrt As Chart)
    chrt.SetElement msoElementChartTitleAboveChart
End Sub

The use of chrt.SetElement previously bypassed this exact issue, but now it seems to be back with Office 2016. Using the old chrt.HasTitle results in the same issue with 2016.

This code worked perfectly for all versions of Office until Office 2016. Now it suddenly refuses to enable the title unless I step through the code which is of course not the intended use.

Anyone knows what is up with this and a possible fix? VBA really gets on my nerves with this kind of stuff and it's very hard to Google too. This is where I got the previous solution from.


Here is the code that creates the chart:

Dim chrt As Chart
RI.rSheet.Activate
Set chrt = Charts.Add.Location(xlLocationAsObject, RI.rSheet.Name)

Then its location is set, any series that were auto-added are removed and new data is added using chrt.SeriesCollection.Add <range>.

Notice: The exact same code does work in another script. This is because here, there is only one series added. When setting the name of the series, Excel automatically enables the title. In this script, multiple series are added and as soon as the second set of data is added, the title is automatically removed again. Then it won't enable it again anymore.


Solution

  • I found an answer. You have to tell VBA twice what he has to do before he listens. Changing the code simply to

    Sub EnableChartTitle_Post2003(chrt As Chart)
        chrt.SetElement msoElementChartTitleAboveChart
        chrt.SetElement msoElementChartTitleAboveChart
    End Sub
    

    fixed the problem.

    This answer was not at all satisfactory...