Excel 2007 Running Slow (vvvery slow!)

[abbey-abbey]

I have written a moderately complex project in Excel 2000 VBA and it works a treat. Nifty and reliable. Running it in Excel 2007 is however a nightmare. Besides 2007 mangling up all my cell color settings and crashing on line codes it ought not to (all of which I've fixed or can fix), my major problem is that 2007 runs frustratingly slow - it's taking a few seconds up to minutes to run. I do not have such issue when the project is run in Excel 2000.

Is this to be expected (I hope not) or can anyone help to point me in the right diirection? Converting the .xls file to .xlsx or .xlsm doesn't make any difference.

When I step through any procedure one at a time all the codes run perfectly well in 2007 just as it does in 2000. However anytime I press F5 to run through a procedure, Excel 2007 suddenly decides to take an eternity running it.

It's doing my head in. Some please help!

[Hack]

Moved From The Codebank

[TerryTee]

Have you tried to isolate the bits that takes so long? You can insert print statements to print the time in areas you suspect and use this to find the difficult areas. Makes it easier to ask more precise questions afterwards.

-Terry

[abbey-abbey]

Hi Terry,

Tell me more about this Print trick. How can it help me and have you got an example? It's worth a try as I have nothing else to try. I must point out that the long delay seems to affect every procedure I've tried so far in my project. Which leads me to conclude it's something systemic and not peculiar to a particular routine.

In any case, as I've run out of ideas, I'm not going to refuse any offer of help.

Thanks for looking at the issue.

[TerryTee]

Hi.
Debug.Print prints to the immidiate window. You can display the Immidiate windows through the View menu in your VBA coding window VBE.
This code simply prints the current time.

Debug.Print Now

If you insert this several places you will see time differences. It’s probably a good idea to add some explanations to the printout as well to understand the result. Could look something like this:

Sub SomeBigLoop()
    Debug.Print Now & "Start SomeBigLoop"
    'Lots of code here that may or may not take a long time
    Debug.Print Now & "SomeBigLoop 1"
    'More code
    Debug.Print Now & "SomeBigLoop 2"
    'Yet more code
    Debug.Print Now & "End SomeBigLoop"
End Sub

Other timing options are availible. A quick search will probably give you plenty of examples
-Terry

[abbey-abbey]

Thanks Terry. I'll give this a try.