Macro takes forever after upgrading from excel 20007 to 2010

[Yigal]

I just upgraded from Office 2007 to 2010 and it looks like the macro that I was using for years takes now forever.
When I debugged it, it seems that the time spent on this line consumes a lot of run time:

Please Login or Register  to view this content.

Here is the subroutine that includes the line:

Please Login or Register  to view this content.

Any idea why is that?

[stnkynts]

Try this instead:

Please Login or Register  to view this content.

[Yigal]

I tried. and it's the same.
Could it be the problem is with the data in the sheets?

[Yigal]

Just to make it clear, the long time is spent on the line:

Please Login or Register  to view this content.

Same thing with my original code:

Please Login or Register  to view this content.

The rest of the routine goes fast.

So the problem is with the line insertion.
What can cause such a long run time on line insertion?
How can I debug such a command?

[Marc L]

Hi,

try on a new sheet after pasting values only, not format neither MFC, … Just data only.

If it's quicker, retry after pasting data normaly.
It it stills quicker, just have to save the workbook but if it remains slower,
maybe too many MFC for example which consume a lot of ressources …

If it's a xlsx or xlsm workbook, try to save it in xlsb (binary compressed) …

Regards !

[Yigal]

Hi,

try on a new sheet after pasting values only, not format neither MFC, … Just data only.

If it's quicker, retry after pasting data normaly.
It it stills quicker, just have to save the workbook but if it remains slower,
maybe too many MFC for example which consume a lot of ressources …

If it's a xlsx or xlsm workbook, try to save it in xlsb (binary compressed) …

Regards !

Thanks for the idea. I followed your advice:
I opened a new sheet, renamed the old sheet to xxold, and gave the new sheet the name of the previous sheet.
The issue remained: long run time on that line.

PS
What is MFC?
The only non-data in the sheet is a DATE function

[stnkynts]

He is referring to formulas. If you have a lot of formulas that have to calculate differently after the paste, that will slow things down. I would suggest you submit a copy of your workbook.

[Yigal]

there is a function:
=DATE(YEAR(A8),MONTH(A8)+1,DAY(A8))

And it appears once in every row, about 250 rows.

I never thought calculating it will take so long, but I can certainly replace it with a link to another sheet which does the same calculation.

[stnkynts]

Probably not that then. I would submit a copy of your workbook because at this point we are just guessing.

[Yigal]

Sorry, but I would kindly ask you to keep guessing, as I can't submit the workbook.

But let me try the idea and replace the function with a fixed value and see if it is changing the situation.
Looking at my code, it looks almost as recursive:
Date of line 7 depends on the date of line 8, which depends on line 9, which depends on line 10... going back to line 250.
Perhaps they are all being calculated, which occupies a lot of memory.
I will also monitor the PC memory, to see if there is an increase.

[Yigal]

I changed the vales to fixed ones - and no change. tra
I also monitored the task manager:
CPU (3 cores) is running at 35%-50%
and physical memory is changing slightly to 50% utilization when I start executing this line.

Could it be there is something in the work book level and not in the sheet level?
Is there a "profiler" or any other tool I could check coherency/other issues that may affect the situation?

[Marc L]

Sorry for "MFC", it's in local language for Conditional Format …

But if you try values only, it's not the issue …

Any event macro in workbook / worksheets modules class ?

[Yigal]

no events macro.
Simply a routine (see above) that gets a sheet name as a parameter, inserting line above line 7.

[Yigal]

My excel file is still VERY SLOW.
I left it because I was clueless, and I suspect that my old PC was just too slow to execute.
Since then I upgraded my PC to I7, with lots of memory, so the CPU can's complain on missing resources.
I also upgraded to office 2010 (previous was 2007) - and the situation got even worst.

I got another hint:
Besides the one simple macro, I have in each cell a "color the cell": if the number is bigger than the below cell, color it with green, if it is lower, color it with red, and if the values are the same: keep it black.
Could it be this is the reason for the extra slow response?