Excel 2007 Bug

[WiredUK]

I have run this code (stripped down code shown below) for over 4 years now
in both Excel 97 and 2003. I am now trying out 2007 but the VBA code to
create my charts is failing. I have found 2 workarounds but it seems like
madness for me to use them. Am I doing something wrong? I think I may be
going mad...



Option Explicit
' Excel 2007 VBA Bug example
'
' Execute BuggedExcel()

Public Sub BuggedExcel()
Dim chtChart As Chart

Dim FixLevel As Integer

FixLevel = 0 'Toggles whether to work-around the Excel 2007 VBA bug.

Set chtChart = ActiveSheet.ChartObjects.Add(0, 0, 184, 95).Chart

Select Case FixLevel
Case 0
'No fix

Case 1
Dim x As Integer
x = chtChart.PlotArea.Top

Case 2
'This fix will only work if you press F5 and not the play button on
the toolbar!
DoEvents

End Select

'The following method (x.Top = 0) fails in Excel 2007 unless the 2 lines
above are executed. Works fine in all previous Excel versions.
'When this fails in 2007, if the User clicks Debug they can step
through/re-run the exact same lines and the code runs without issues.
chtChart.PlotArea.Top = 0

Set chtChart = Nothing

End Sub

 

[Jon Peltier]

If you do not add data to the chart, either through Chart.SetSourceData,
Chart.SeriesCollection.NewSeries, or Chart.SeriesCollection.Add, there is no
data in the chart, and therefore no chart elements besides the ChartArea.
The fact that

x = chtChart.PlotArea.Top

prevents an error on

chtChart.PlotArea.Top = 0

is, I believe, the error in Excel 2007. If there is no data in the chart,
the "x =" line fails in prior versions of Excel.

What makes Excel 2007 different is that it never creates a chart with
populated data series using ChartObjects.Add, while by default, earlier
versions of Excel will use the current region of the active cell. It is best
always to assign your data explicitly, rather than let Excel to add what it
thinks you want.

- Jon

 

[WiredUK]

Jon,

Perhaps I should have explained a bit more, but the code I posted was
stripped down completely. The original code does actually add the data, it
goes something like this:

Call chtChart.SeriesCollection.Add(rngSource)

I just took my code and stripped every line out that did not have an effect
on the end result causing the error.

Thanks,
David

 

[Jon Peltier]

Funny, now it fails for me even if the chart is populated with data.

I can look it over and perhaps submit it as a bug.

- Jon