Apparent Excel 2007 chart HasTitle race condition

[Graham F]

The macro below works correctly, i.e. creates a chart without a title, with
Excel 2003, but the chart has a title with Excel 2007 SP2 and the Excel 2010
beta. (The macro requires numeric data in the range A1:B10 on the first
worksheet.)

The macro *does* work correctly with Excel 2007 if I step through it in the
debugger (thus introducing delays between each statement), or if I introduce
additional statements before the ch.HasTitle statement (in particular *two*
DoEvents calls seems to work).

My company's application that uses Excel for reports has larger macros
containing similar code that must run correctly on many PCs. Does anyone
know of a safe fix or work-around for this problem?

Sub Macro1()
Dim ws As Worksheet
Dim ch As Chart
Dim s As Series

' Add chart.
Set ws = Worksheets(1)
Set ch = ws.ChartObjects.Add(100, 100, 400, 400).Chart
ch.ChartType = xlXYScatterLines
' Add series to chart.
Set s = ch.SeriesCollection.NewSeries
s.Name = "My series"
s.Values = ws.Range(ws.Cells(1, 2), Cells(10, 2))
s.XValues = ws.Range(ws.Cells(1, 1), Cells(10, 1))
' No title.
ch.HasTitle = False
End Sub

Graham

 

[Peter T]

That's ridiculous isn't it!
Couple more workarounds, though not sure if any better than your pair of
Doevents, which also work for me

Application.ScreenUpdating = True
ch.HasTitle = False

or
' at module level
Private mCht as chart

' code
ch.HasTitle = False
Set mCht = ch
Application.OnTime Now, "noTitle"
End Sub

Sub noTitle()
mCht.HasTitle = False
End Sub

If you've making several charts this would need to be done differently but
basically same idea

or stick with your pair of DoEvents

Regards,
Peter T

 

[Herbert Seidenberg]

Excel 2007
Add embedded chart with macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_29_10.xlsm

 

[Martin Brown]

The macro below works correctly, i.e. creates a chart without a title, with
Excel 2003, but the chart has a title with Excel 2007 SP2 and the Excel 2010
beta. (The macro requires numeric data in the range A1:B10 on the first
worksheet.)

The macro *does* work correctly with Excel 2007 if I step through it in the
debugger (thus introducing delays between each statement), or if I introduce
additional statements before the ch.HasTitle statement (in particular *two*
DoEvents calls seems to work).

My company's application that uses Excel for reports has larger macros
containing similar code that must run correctly on many PCs. Does anyone
know of a safe fix or work-around for this problem?

Sub Macro1()
Dim ws As Worksheet
Dim ch As Chart
Dim s As Series

' Add chart.
Set ws = Worksheets(1)
Set ch = ws.ChartObjects.Add(100, 100, 400, 400).Chart
ch.ChartType = xlXYScatterLines
' Add series to chart.
Set s = ch.SeriesCollection.NewSeries
s.Name = "My series"
s.Values = ws.Range(ws.Cells(1, 2), Cells(10, 2))
s.XValues = ws.Range(ws.Cells(1, 1), Cells(10, 1))
' No title.
ch.HasTitle = False
End Sub

Yes I know Tragic isn't it. Bodging in extra delays or DoEvents to
give the chart time to instantiate its structure fully before the other
statements execute is the fix. It is worse on multicore machines and
XYscatter graphs are badly behaved in this respect. I am a bit surprised
it fails here for such a small amount of data plotted.

The whole of the XL2007 graphic code is riddled with these. That is why
drawing charts is so glacially slow there must be lots of these spurious
delays bodged in by MickeySoft "engineers" to make it work at all

You will enjoy similar problems if you try to alter the axes of a newly
created graph too soon.

Use XL2003 if you wish to remain sane.

Regards,
Martin Brown

 

[Andy Pope]

Hi,

For me, setting the HasTitle property to true allowed the setting of it
to False to actually remove it.

Sub Macro1()

Dim ws As Worksheet
Dim ch As Chart
Dim s As Series
Dim lngIndex As Long

' Add chart.
Set ws = Worksheets(1)
Set ch = ws.ChartObjects.Add(100, 100, 400, 400).Chart
ch.ChartType = xlXYScatterLines
' Add series to chart.
Set s = ch.SeriesCollection.NewSeries
s.Name = "My series"
s.Values = ws.Range(ws.Cells(1, 2), Cells(10, 2))
s.XValues = ws.Range(ws.Cells(1, 1), Cells(10, 1))

' No title.
ch.HasTitle = True
ch.HasTitle = False

End Sub

Cheers
Andy

 

[Peter T]

Typical Andy Pope to figure that one <g>

Also, if a second series is added no need to do anything special,

' ch.HasTitle = True
ch.HasTitle = False

Set s = ch.SeriesCollection.NewSeries ' the 2nd series
s.Name = "My series2"
s.Values = ws.Range(ws.Cells(1, 3), Cells(10, 3))
s.XValues = ws.Range(ws.Cells(1, 1), Cells(10, 1))

Regards,
Peter T