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

 

[Jim Rech]

You're not going mad.<g> There were many, many bugs in beta XL2007 re
shapes and charts. Not all were fixed. I'm glad you have a workaround;
nothing wrong with using it even if it looks silly.

--
Jim
|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
|
|
|
|

 

[Bob Flanagan]

Try setting application.screenupdating to Truebefore excuting the commands.
I have found that screenupdating must be set to True for some commands to
work in Excel 2007. When Excel kicks into debug mode, screenupdating is
turned on, thus the reasons the commands are probably working when the user
steps throught them.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Jim Rech]

Try setting application.screenupdating to True


Did that work for you, Bob, with his posted code? Didn't for me.


--
Jim
| Try setting application.screenupdating to Truebefore excuting the
commands.
| I have found that screenupdating must be set to True for some commands to
| work in Excel 2007. When Excel kicks into debug mode, screenupdating is
| turned on, thus the reasons the commands are probably working when the
user
| steps throught them.
|
| Bob Flanagan
| Macro Systems
| 144 Dewberry Drive
| Hockessin, Delaware, U.S. 19707
|
| Phone: 302-234-9857, cell 302-584-1771
| http://www.add-ins.com
| Productivity add-ins and downloadable books on VB macros for Excel
|
| | >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
| >
| >
| >
| >
|
|

 

[Bob Flanagan]

Hi Jim. I didn't have a chance to try the code. I have 2007 on a
development machine, and haven't turned it on today. I'll play with it
later today and see what the cause is and possible solutions. I had run
into problems moving charts around until I turned on screen updating, and
thus his problem seemed like one I had solved.

You're right about the bugs in 2007. One that irritates me is
activesheet.chartobjects.count. If no chartobjects, it returns an error
instead of zero.

Bob