Large Excel 2010 file works badly in Excel 2007

Do you need the macros? If not, you can try saving it as an xlsx and see if that helps.

I have just tried doing this, and the performance does not appear to be improved.

Difficult to say without more information.  

Does it access any external files or links or websites?

If you turn off calculations does it go slow?

Is it posting any errors from VBA code that work for 2010 but error out in 2007?  Have these been suppressed by an on error resume next?  

Hard to say without looking at the file.

If you run a short bit of vba code turning off events does that speed things up?

If it is looking for external links it can't find that will grind it to a halt.

-SA

You can also upload the file (without confidential information) for us to take a look.

* Does it access any external files or links or websites?

I don't know, but both the 2007 and the 2010 machines are on the same network, so I suspect that the access would be the same for both.

* If you turn off calculations does it go slow?

How do I do this?

* Is it posting any errors from VBA code that work for 2010 but error out in 2007?  Have these been suppressed by an on error resume next?  

How do I check this?

* If you run a short bit of vba code turning off events does that speed things up?

How do I do this?

I'll have to seek permission to send you this file, as it has a lot of information in it. I will ask the client.

To turn off calculations:
click Formulas
click Calculation Options
click Manual

It sounds like the code is not a problem, if saving it as an xlsx does not help.

Also try this:

Click Data, Connections

See if there is anything in the list, and let us know.

Just use the Excel 2010 machine :)



* Does it access any external files or links or websites?

I don't know, but both the 2007 and the 2010 machines are on the same network, so I suspect that the access would be the same for both.

Then you might want to make sure that they have the same trust center macro settings.  
file>>Trustcenter>Trust Center Settings> Macro settings  

I'd pay special attention to the trust VBA object model checkmark.


* If you turn off calculations does it go slow?
file>Options>Formulas>workbook calculations manual (by presing F9)  You'll want to change this back after testing.  Although I think it does save the setting by workbook.

How do I do this?

* Is it posting any errors from VBA code that work for 2010 but error out in 2007?  Have these been suppressed by an on error resume next?  

Open the developer ribbon  (you'll have to turn  this one on using customise)  And then go into the vba editor and look at the code.  If you put a ' in front of on error resume next you'll see any errors that are occuring.  Some errors are designed to occur, so if you aren't familiar with VBA this might not help much.

How do I check this?

* If you run a short bit of vba code turning off events does that speed things up?

Again,  if you aren't familiar with VBA this might not be a good option for you.


'  the marker at the beginning of this line indicates a comment  by changing the commented line below you can toggle if  this code will turn events on or off.  This will deactivate any VBA Event triggers that might be used in the workbook.

Sub NoEvents()
Application.EnableEvents = False
'Application.EnableEvents = True
End Sub

How do I do this?

There are no links in the connections window.

Setting the calculation option to manual does not appear to help.

Try copying all the data in the worksheet (or sheets) into a new excel file.

(you can do this by selecting all the cells (control-a), and then doing control-c to copy, control-n to open a new file, and control-v to paste.

When I do the copy and paste into the new spreadsheet it tells me that some formulas will be pasted as values.

The pasted spreadsheet looks very different, but contains the same data.

I do not know enough about the operation of the sheet to work out what does or doesn't work, but the performance is fine on 2007.

Any idea where we go from here?

In the original file (not the one you just created), click Data, and click Edit Links.

Is there anything in the list? If so, it's telling you that it's pulling in information from other workbooks.

I'm not at my desk now to check this, but I am guessing not as it was emailed to me in isolation, and it works fine on my Excel 2010 computer.

It may be worth double checking.

Hi, Richard.

IIRC, there were two areas in particular which had speed issues in 2007 that Microsoft significantly improved in 2010 - Charts and Shapes. Does your file have either of these?

Regards,
Brian.

No to charts or shapes!

Edit Links is greyed out, so I assume that means there aren't any.

Thanks, rpm.

Could you post a redacted copy of the file here, please? Ideally the version should give the same problems, but even if it doesn't that will give us valuable leads.

Thanks,
Brian.

I have obtained permission to send out the redacted spreadsheet.

If any experts would like to look at the spreadsheet, please send an email to richard@domain-name.uk and I'll email the spreadsheet back.

Richard

**Email address removed per EE policies.**-JARmod101

(Note the hypen is part of my address, as the address has been split over 2 lines!)

Richard,

I got the file, thank you.

Good news - I seem to be seeing the same issues. The virtual PC I'm opening the file in is currently running at 100% cpu.

At first sight, your conditional formatting "Applies To" has a complicated range. I'll tidy that up and see if it makes a difference.

Regards,
Brian

That's great news, who'd have thought that conditional formatting rules could have such different effects in 2007 and 2010.

If possible, can you remove the attached sheet from you post now please.

Thanks,

Richard

Sorry, I didn't think. I'll request that immediately.

Thanks, Kevin.

Thanks!

JARmod101,

Once again my apologies about needing to have the file removed - that was as a result of a stupid oversight on my part, rather than a deliberate plan!

I agree completely about being fair to everyone, however I don't see that this infringed that - any expert who bothered to email Richard would have received a copy of the file.

Finally, even without the original or modified files, this PAQ is still useful for anyone who may have a similar problem.

Regards,
Brian.

Netminder,

I don't give a hoot about the points, but I do care about the fact that my mistake is causing such a fuss. Sincere apologies to all concerned.

Brian.

For what it is worth, should anyone be reading this question, the solution that Brian found was correct, and is well worth looking into if anyone comes across a similar problem.

The original spreadsheet turned oput to have literally pages of conditional formatting rules. In Excel 2010 this was not a problem and the sheet performed fine. In Excel 2007 the same file was unworkably slow.

Removing the conditional formatting rules (and replacing them with a simplified set) fixed the problem.

I hope this summary returns this question to a useful state, event without the spreadsheet itself!

My thanks to Brian for solving a problem that was causing me a lot of confusion, he is a real Excel expert!

Thanks, Richard.

zorvek,

There have been a number of emails between Netminder and myself and, as a result, I am content to let things remain as they are.

Many thanks for your support,
Brian.