XL2007 - Known problem? - Driving me crazy.

[michael.beckinsale]

Hi All,

I have developed a workbook in XL2003 which is 24.27MB in size which
is heavily populated with formulas but nothing particularly complex.
If, Iserror, Sumif being the mainstay.

There is a small amount of VBA code triggered by the Workbook_Open
event (posted below)

The problem is this:

When l open the workbook in XL2007 everything appears fine, the
workbook calculates (showing that 2 processors are being used) then
when the calculation has ended l get a message stating that XL has
encountered a problem with the workbook and is trying to recover the
information!

I have tried various ways of opening / saving / transferring this file
into the XL2007 environment but without success. Only on one occasion
did the recovery message successfully complete stating that it had
found 'invalid conditional formatting' but without any details. I am
not convinced this is the cause, surely any conditional format
statement that works with XL2003 will work with XL2007, and
additionally how am l meant to locate the offending cells(s) in a
workbook of this size?

The original file is not corrupted as l have successfully opened it on
another machine running XL2003

Is there a site anywhere that lists all known XL2007 issues /
problems?

Can anybody shed any light on this problem?

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call MsgBox("This workbook has been set to manual calculation to
enhance its operation." _
& vbCrLf & "" _
& vbCrLf & "Pressing F9 to calculate the workbook will
ensure that the latest results are displayed." _
, vbInformation, "Information")

Filename = ActiveWorkbook.Name
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.DisplayAutomaticPageBreaks = False
Sht1.EnableAutoFilter = True
If Sht1.Name Like "*Volumes" Or Sht1.Name Like "*Outputs" Then
Sht1.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1
Else
Sht1.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End If
Sht1.Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Sht1.Range("A1").Select
Next Sht1
Sheets("Start Here").Select

End Sub

Regards

Michael Beckinsale

 

[Jim Cone]

Michael,
If you posted your entire code...
1. You did not declare the Filename and Sht1 variables.
2. It is not clear as to whether "ActiveWorkbook" is the workbook that contains
the code. If it is, I would replace ActiveWorkbook with "ThisWorkbook".
3. Filename is a property of the Assistant object and the FileSearch object
and should not be used as a variable name.
Furthermore, the FileSearch object was removed from XL2007 and
who knows what Excel 2007 thinks it has found when it sees that variable.

Of course, fixing all of the above may not cure your problem. <g>
(some people refer to XL2007 (12) as the beta for version 14)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"michael.beckinsale"
wrote in message
Hi All,
I have developed a workbook in XL2003 which is 24.27MB in size which
is heavily populated with formulas but nothing particularly complex.
If, Iserror, Sumif being the mainstay.

There is a small amount of VBA code triggered by the Workbook_Open
event (posted below)

The problem is this:

When l open the workbook in XL2007 everything appears fine, the
workbook calculates (showing that 2 processors are being used) then
when the calculation has ended l get a message stating that XL has
encountered a problem with the workbook and is trying to recover the
information!

I have tried various ways of opening / saving / transferring this file
into the XL2007 environment but without success. Only on one occasion
did the recovery message successfully complete stating that it had
found 'invalid conditional formatting' but without any details. I am
not convinced this is the cause, surely any conditional format
statement that works with XL2003 will work with XL2007, and
additionally how am l meant to locate the offending cells(s) in a
workbook of this size?

The original file is not corrupted as l have successfully opened it on
another machine running XL2003

Is there a site anywhere that lists all known XL2007 issues /
problems?

Can anybody shed any light on this problem?

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call MsgBox("This workbook has been set to manual calculation to
enhance its operation." _
& vbCrLf & "" _
& vbCrLf & "Pressing F9 to calculate the workbook will
ensure that the latest results are displayed." _
, vbInformation, "Information")

Filename = ActiveWorkbook.Name
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.DisplayAutomaticPageBreaks = False
Sht1.EnableAutoFilter = True
If Sht1.Name Like "*Volumes" Or Sht1.Name Like "*Outputs" Then
Sht1.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1
Else
Sht1.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End If
Sht1.Activate
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Sht1.Range("A1").Select
Next Sht1
Sheets("Start Here").Select

End Sub

Regards

Michael Beckinsale

 

[michael.beckinsale]

Hi Jim,

Thanks for responding.

I have 'dimmed' the filename - copy / paste error.

This is basic VBA code that l have used for many years to loop thru
sheets in workbook and l have never declared Sht1 as a variable, and
have always used filename.

However taking on board your comments l have changed ActiveWorkbook to
ThisWorkbook and changed 'filename' to 'MyWB'

Result - No change! it still crashes.

It will be VERY disappointing if basic VBA code like this needs to be
amended to run in XL2007 (SP1)

After further tests I now believe that it is the VBA code that is
causing the problem because when l strip out the Workbook_Open event l
can open the workbook fine & save the file as XL2007.xlsx

All l can do now is try to isolate the the lines causing the
problem(s) and amend as necessary.

Regards

Michael

 

[Jon Peltier]

Michael -

Open the workbook in 2003, enter 'Stop' as the first line within the sub,
and save the workbook. Reopen in 2007, the code will be interrupted at
'Stop', then step through using the F8 key. This may help you find the
offending code.

- Jon

 

[michael.beckinsale]

Jim & Jon,

Thanks for your input so far.

Now l really am confused.

1) When l take out the 'If' statement containing the 'Like' functions
the workbook saves & calculates properly. I have closed, opened &
calculated the file several times without error.

2) When l leave in the 'If' statement using an 'On Error Resume Next'
the workbook will save. When l re-open it the Workbook_open event runs
correctly (l put a msgbox in to tell me it was at the end of the
code). However when l press F9 to calculate l still get the dreaded
'Excel has encountered a problem' message.

I have run the full diagnostics facility within Office 2007 and no
errors were reported.

I am wondering if it has anything to do with the calculation options
set in Excel.

Any idea's

Regards

Michael.

 

[Jim Cone]

Michael,
1. Did you ever declare the Sht1 variable?
Dim Sht1 as Excel.Worksheet

2. Enter "Option Explicit" as the first line of your code!

3. Change "DisplayAutomaticPageBreaks" to DisplayPageBreaks
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"michael.beckinsale"
<[email protected]>
wrote in message
Jim & Jon,
Thanks for your input so far.
Now l really am confused.

1) When l take out the 'If' statement containing the 'Like' functions
the workbook saves & calculates properly. I have closed, opened &
calculated the file several times without error.

2) When l leave in the 'If' statement using an 'On Error Resume Next'
the workbook will save. When l re-open it the Workbook_open event runs
correctly (l put a msgbox in to tell me it was at the end of the
code). However when l press F9 to calculate l still get the dreaded
'Excel has encountered a problem' message.

I have run the full diagnostics facility within Office 2007 and no
errors were reported.

I am wondering if it has anything to do with the calculation options
set in Excel.

Any idea's

Regards

Michael.

 

[michael.beckinsale]

Hi All,

I have managed to overcome the problem but l'm not sure if it is
coincidence or not. You know how it is when you try numerous things
and suddenly things work as they should.

I had the 'Addintools - Classic Menu for Excel' COM add in installed.
When l de-activated the add-in l could save & calculate the file as
required. I then re-activated the add-in and the workbook behaved
correctly.

When l get time l will try to replicate the problem and isolate to the
add-in being the cause.

Regards

Michael

 

[Jon Peltier]

I've seen some recent COM and VSTO add-ins cause strange problems like this,
related to the add-in not correctly dealing with references within the Excel
application. I have no special knowledge of that add-in, but I would not be
surprised if it were the culprit.

In general, while these add-ins seem like a good way to remain comfortable
in the new user interface, I think it's probably better to just bite the
bullet and use the ribbon unadorned with third party clutter.

- Jon

 

[Jon Peltier]

Jim -

While points 1 & 2 reflect best programming practices, I doubt they have
anything to do with Michael's problem. The code should not have failed the
way it was.

- Jon

 

[Jim Cone]

Jon,
Yes, I agree, but he is dealing with XL2007 and with it anything is possible. <g>
Jim Cone
(blocking and tackling comes first)


"Jon Peltier"
wrote in message
Jim -
While points 1 & 2 reflect best programming practices, I doubt they have
anything to do with Michael's problem. The code should not have failed the
way it was.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jim Cone"
wrote in message

 

[Jon Peltier]

True, I've seen some code which worked for years hang in 2007. Usually
reordering the statements is all it needs to work correctly. I've never seen
a problem in 2007 with undeclared variables in Option Explicit is not
displayed.

- Jon

 

[Jim Cone]

Jon,
Try using Sht1 and Sht in the same loop.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Jon Peltier"
wrote in message
True, I've seen some code which worked for years hang in 2007. Usually
reordering the statements is all it needs to work correctly. I've never seen
a problem in 2007 with undeclared variables in Option Explicit is not
displayed.

- Jon

 

[Jon Peltier]

Sub SheetStuff()
Dim sht As Object
Dim sht1 As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name = "Sheet3" Then
Set sht1 = sht
End If
Next
sht1.Activate
End Sub

No problem. You have an example in mind?

- Jon

 

[Jim Cone]

Jon,
Re: "I've never seen a problem in 2007 with undeclared
variables in Option Explicit is not displayed."
Re: "You have an example in mind?"

Let me try again.
Sht1 was not declared in Michael's original posted code.
So using "Sht" anywhere in his loop instead of Sht1 would have caused problems.
An example of that could have been...
'--
For Each Sht1 In Workbooks(Filename).Worksheets
Sht1.DisplayAutomaticPageBreaks = False
Sht.EnableAutoFilter = True
'--
A good share of posts show incomplete code, so it can be difficult to know
where the problem lies. So the lesson for posters, I hope, is...
Declare all variables and use Option Explicit before complaining "it doesn't work".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jon Peltier"
wrote in message
Sub SheetStuff()
Dim sht As Object
Dim sht1 As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name = "Sheet3" Then
Set sht1 = sht
End If
Next
sht1.Activate
End Sub

No problem. You have an example in mind?

- Jon

 

[michael.beckinsale]

Hi All,

Many thanks for your responses. From my point of view it is comforting
to know that such well known and frequent posters take a real interest
in what at first glance must seem to be implausable problem. I am
pretty sure that as more & more users start to take up 2007 there will
be many more posts similar to this.

Jim - I usually use Option Explicit at the begining of my code and
only removed it trying to debug. l have taken on board your comments
re the variable declarations and have amended my code snippet
accordingly. When l use it again it will be 'fully defined'.

Jon - Your comments are comforting, they indicate that l have indeed
found the problem, and of course you are quite correct in that l need
to bite the bullet and find my way around the ribbon but sometimes one
just does not have adequate time and these kind of aids do enhance
productivity.

My real worry now is that existing workbooks written in earlier
versions of Excel will not function correctly in 2007. I thought the
Microsoft policy was that upgrading to newer versions would be
seamless. Perhaps l am being naive.

Do you know if anyone has put together some sort of strategy for
debugging code that works in previous versions of Excel but not 2007?
Is there a list of known techniques that dont work in 2007?

Regards

Michael

 

[Jon Peltier]

Yeah, that's why one should use Option Explicit, but it's not
version-specific. I thought you had a special Excel 2007-only error you
wanted to share.

- Jon

 

[Jon Peltier]

Most of the issues are minor, that is, they only need minor changes,
although their effects may be widespread. Offhand I don't have an example,
but I've seen a few.

One big change is that FileSearch has been deprecated.

Other changes relate to changes in Excel itself: larger grid (can't use an
integer for column number!!), enhanced conditional formatting, upgraded
pivot tables, changed shapes, etc.

- Jon

 

[michael.beckinsale]

Hi Jon,

Thanks for the info.

From a personal point of view l think that the fact that you cannot
reference cells as say Cells(1,1) it a huge drawback. How does this
affect R1C1 notation?

l frequently use functions such as 'GetOpenFilename' . Can l assume
from your comments that these type of functions have now changed?

Regards

Michael.

 

[Jon Peltier]

... you cannot reference cells as say Cells(1,1) ...

Are you referring to my statement "can't use an integer for column number"?
I meant that you have to use a long. Of course, I made an error here (my
bad!), because the number of columns in 2007 is 16384, and integers go up to
32767. So integers are still barely okay.

Also, I said nothing about GetOpenFileName. It works just fine. You have to
test each bit of code, because while 99.9% is fine, that little bit that
changed will need your attention.

- Jon