DoEvents does not do the trick to solve a run time error but that works in debug mode

[akewi_fatc]

Hi all,
I have Google, tried many hours with fix and trix but still I'm stuck.

I have a very large macro there one Sub creates charts. The macro is developed for a very long time and each change results in a new macro version.

The tricky part is that the macro survived many Excel versions and often works. But sometimes completely random and very seldom a new macro version get a run time error at red colour marked row below. It works if I run in debug mode.
The change in the macro does not even to be in the "chart sub" it could be anywhere. And for the moment I'm stuck with a non-working macro version.

I have tried with DoEvents to get Excel, VBA and Windows in sync, Application.Wait and Screen.Updating True/False all over in the macro but nothing solves the problem in this version of the macro.

Below red coloured row is just an example, but it always hit some chart property (not always in case 2).

The code below is just an example and it will probably work on you PC. This is more a theoretical problem than a test and try problem.

Anyone out there that have some idea ?

Thanks in advance,
Ake

Sub mySub()
 
        Workbooks(myWorkBook).Activate
        Sheets(mySheet).Select
 
        For ii = 1 To myNbOfCharts
 
            Charts.Add
            ActiveChart.Location Where:=xlLocationAsNewSheet
            ActiveChart.Name = "myChartNb" + Str(ii)
            ActiveChart.HasTitle = True
            ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
            ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
            ActiveChart.HasLegend = True
            ActiveChart.Legend.Select
            Selection.Position = xlBottom
            ActiveChart.PlotArea.Interior.ColorIndex = 15
 
            Select Case ii
 
                Case 1
                    ActiveChart.ChartType = xlColumnClustered
                    ActiveChart.SetSourceData Source:=Sheets(mySheet).Range("A1:B10"), PlotBy:=xlColumns
                    ActiveChart.HasTitle = True
                    ActiveChart.ChartTitle.Characters.Text = "myChartTitle1"
                    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "myXaxesTitle"
                    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "myYaxesTitle"
                    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
                    ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
 
                Case 2
                    ActiveChart.ChartType = xlColumnClustered
                    ActiveChart.SetSourceData Source:=Sheets(mySheet).Range("C1:D10"), PlotBy:=xlColumns
                    ActiveChart.HasTitle = True
 ActiveChart.ChartTitle.Characters.Text = "myChartTitle2"
  ' The error is "This object has no title"
 ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "myXaxesTitle"
                    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "myYaxesTitle"
                    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
                    ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
 
                Case 3
                    ActiveChart.ChartType = xlColumnClustered
                    ActiveChart.SetSourceData Source:=Sheets(mySheet).Range("E1:F10"), PlotBy:=xlColumns
                    ActiveChart.HasTitle = True
                    ActiveChart.ChartTitle.Characters.Text = "myChartTitle3"
                    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "myXaxesTitle"
                    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "myYaxesTitle"
                    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
                    ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
 
                Case Else
                               MsgBox "Not implemented for Chart Nb" + Str(ii)
                                                                                                                                                                                End
 
            End Select
 
            ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, myxpos, myypos, mylength, myheight).Select
            Selection.Characters.Text = my_text
            Selection.AutoScaleFont = False
            With Selection.Characters(Start:=1, Length:=Len(my_text)).Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = my_font_size
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
            ActiveChart.ChartArea.Select
 
        Next ii
   
End Sub

 

[Damon Ostrander]

Hi Ake,

Try ActiveChart.ChartTitle.Text = "myChartTitle2"

(i.e., eliminate ".Characters")

Damon

 

[akewi_fatc]

Sorry Damon
done that but no difference.
Ake

 

[RoryA]

Perhaps try:

ActiveChart.ChartTitle.Select
Selection.Text = "myChartTitle2"

 

[akewi_fatc]

Sorry Rory
that will not do the trick.
Ake

 

[RoryA]

Will not or doesn't?

Which version of Excel are you having the issue with (2007?), or is it several?

 

[akewi_fatc]

I tried all tricks in Excel 2003, 2007 and now in 2010 that we are using now.
Nothing seems to solve this problem.
I have struggled with this for several years.
Ake