Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default EXCEL macros running much more slowly in EXCEL 2007 vs. 2003

I'm experiencing exactly the same problem.

When you said you solved the problem by using "specified ranges", do you mean that you used the format ($B:$C) for entire columns OR did you specify a specific range such as "$B$1:$c$2579" ?

Thanks.



Pei-Jean Chang wrote:

Do you by any chance have any formulas or conditionally formatting in place?
08-Apr-09

Do you by any chance have any formulas or conditionally formatting in place? I had the same problem where each copy and paste took what seemed like forever. It turns out that my conditional formats were slowing everything down AND making the file HUGE (15,000K). Instead of specifying ranges in my formats like I did in Excel 2003 ($b$1:$b$500), I decided to take advantage of 2007's new ability to name entire columns ($b:$b). I had about 10 formats written this way. Therefore, each time I copy/pasted a cell, Excel had to work through all 1,048,576 rows before it could finish thinking and paste. To fix this, I specified ranges in all my conditional formats and now the copy/paste works in less than a second!

Hope that helps!

Previous Posts In This Thread:

On Wednesday, November 07, 2007 6:03 PM
Debbie wrote:

copy and paste in Excel 2007 slow
When I use the office clipboard to copy numerous individual cells
(containing only text) it slows down the more times I use it. I copy about
12 items at a time and then clear the clipboard after I have pasted all of
them. By about the 3rd or 4th set it takes so long to copy each cell, 10 to
15 seconds...pasting is still quick. I did not have this problem in Excel
2003. Is there some reason for this slowdown?
Thank you.
Debbie
I am using a fairly new computer with lots of RAM and HD space, Windows
XP.

On Thursday, November 08, 2007 7:11 PM
david_t_toky wrote:

Hi Debbie,I have the same problem and have had it ever since I first
Hi Debbie,

I have the same problem and have had it ever since I first installed
Office 2007. A few months ago I scoured the Internet for more
information on this and eventually found an article that said that
there was a particular problem with copying too much data in Excel
2007. I'm not that technical so apologies if this isn't precisely
correct but the inference in the article was that if you copy a lot of
data at some point Excel 2007 will not clear the cache / buffer /
clipboard / temporary file (whatever...) and effectively all that
information (or that process) then becomes a part of the
spreadsheet. If you try to copy it again, you're just filling it up
even more. The article said it was obviously a bug, that it was
unique to Office 2007 and that Microsoft had acknowledged it and would
put out a fix. To date I've seen no such fix and the problems
persists. The article also suggested that a way around it was to
save the file regularly so you would have a "clean" version to go back
to.

I've looked again for the article but, apologies, I can't find it. As
I say, it was a few months ago but I do find it a bit curious that
there is no mention of it elsewhere. What I can find, however, are
many, many messages very similar to yours.

It's very easy to replicate this bug. Create a spreadsheet, fill up a
page with data, equations and formatting (the more you format the
sheet, the quicker the bug will appear) and then start copying rows
and columns from that page in bulk across a number of new pages in the
spreadsheet. At some point the copying process will inevitably grind
to a halt - and there's your bug. What's worrying is that it seems
that if you copy data from one spreadsheet that has the bug, to
another that hasn't - the one that you've copied it to will also have
the bug. In that respect it really is rather like a virus.

One more curious element. If I do this enough, Excel fails
completely, goes transparent, hunts for solutions before it shuts down
and restarts itself. I then get a splendid screen saying that a
solution is available as part of the Windows / Office update.
Great! The problem is that everytime I try to go and get it via
Windows Update, I find it doesn't exist....

If anyone from Microsoft is reading this - any comments???

On Thursday, November 15, 2007 4:46 PM
Ox wrote:

I'm having the same problem.
I'm having the same problem. It seems to have something to do with how long
Excel has been open, in terms of the number of operations that have preceded
the Copy command. Even if I've closed all previous worksheets and saved
current work, I've had it hang up for 5 minutes. I finally shut down Excel
thru Task Manager. Then I'll start it back up and the operation takes place
instantaneously.

I've had similar slow downs when deleting large blocks of data, even when
there are no formulas in, or dependent on, the cells being cleared.

I'm running Excel 2007 on Windows XP with 2 gb of RAM.

On Wednesday, April 08, 2009 11:06 AM
Pei-Jean Chang wrote:

Do you by any chance have any formulas or conditionally formatting in place?
Do you by any chance have any formulas or conditionally formatting in place? I had the same problem where each copy and paste took what seemed like forever. It turns out that my conditional formats were slowing everything down AND making the file HUGE (15,000K). Instead of specifying ranges in my formats like I did in Excel 2003 ($b$1:$b$500), I decided to take advantage of 2007's new ability to name entire columns ($b:$b). I had about 10 formats written this way. Therefore, each time I copy/pasted a cell, Excel had to work through all 1,048,576 rows before it could finish thinking and paste. To fix this, I specified ranges in all my conditional formats and now the copy/paste works in less than a second!

Hope that helps!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Are you infected by Sony
http://www.eggheadcafe.com/tutorials...d-by-sony.aspx
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,203
Default EXCEL macros running much more slowly in EXCEL 2007 vs. 2003

Sounds to me like he used explicitly defined ranges like $B$1:$C$2579 in his
conditional formatting statements, since his complaint occurred after he'd
started using whole column references for some functions. But I could be
wrong.

"Eric Grove" wrote:

I'm experiencing exactly the same problem.

When you said you solved the problem by using "specified ranges", do you mean that you used the format ($B:$C) for entire columns OR did you specify a specific range such as "$B$1:$c$2579" ?

Thanks.



Pei-Jean Chang wrote:

Do you by any chance have any formulas or conditionally formatting in place?
08-Apr-09

Do you by any chance have any formulas or conditionally formatting in place? I had the same problem where each copy and paste took what seemed like forever. It turns out that my conditional formats were slowing everything down AND making the file HUGE (15,000K). Instead of specifying ranges in my formats like I did in Excel 2003 ($b$1:$b$500), I decided to take advantage of 2007's new ability to name entire columns ($b:$b). I had about 10 formats written this way. Therefore, each time I copy/pasted a cell, Excel had to work through all 1,048,576 rows before it could finish thinking and paste. To fix this, I specified ranges in all my conditional formats and now the copy/paste works in less than a second!

Hope that helps!

Previous Posts In This Thread:

On Wednesday, November 07, 2007 6:03 PM
Debbie wrote:

copy and paste in Excel 2007 slow
When I use the office clipboard to copy numerous individual cells
(containing only text) it slows down the more times I use it. I copy about
12 items at a time and then clear the clipboard after I have pasted all of
them. By about the 3rd or 4th set it takes so long to copy each cell, 10 to
15 seconds...pasting is still quick. I did not have this problem in Excel
2003. Is there some reason for this slowdown?
Thank you.
Debbie
I am using a fairly new computer with lots of RAM and HD space, Windows
XP.

On Thursday, November 08, 2007 7:11 PM
david_t_toky wrote:

Hi Debbie,I have the same problem and have had it ever since I first
Hi Debbie,

I have the same problem and have had it ever since I first installed
Office 2007. A few months ago I scoured the Internet for more
information on this and eventually found an article that said that
there was a particular problem with copying too much data in Excel
2007. I'm not that technical so apologies if this isn't precisely
correct but the inference in the article was that if you copy a lot of
data at some point Excel 2007 will not clear the cache / buffer /
clipboard / temporary file (whatever...) and effectively all that
information (or that process) then becomes a part of the
spreadsheet. If you try to copy it again, you're just filling it up
even more. The article said it was obviously a bug, that it was
unique to Office 2007 and that Microsoft had acknowledged it and would
put out a fix. To date I've seen no such fix and the problems
persists. The article also suggested that a way around it was to
save the file regularly so you would have a "clean" version to go back
to.

I've looked again for the article but, apologies, I can't find it. As
I say, it was a few months ago but I do find it a bit curious that
there is no mention of it elsewhere. What I can find, however, are
many, many messages very similar to yours.

It's very easy to replicate this bug. Create a spreadsheet, fill up a
page with data, equations and formatting (the more you format the
sheet, the quicker the bug will appear) and then start copying rows
and columns from that page in bulk across a number of new pages in the
spreadsheet. At some point the copying process will inevitably grind
to a halt - and there's your bug. What's worrying is that it seems
that if you copy data from one spreadsheet that has the bug, to
another that hasn't - the one that you've copied it to will also have
the bug. In that respect it really is rather like a virus.

One more curious element. If I do this enough, Excel fails
completely, goes transparent, hunts for solutions before it shuts down
and restarts itself. I then get a splendid screen saying that a
solution is available as part of the Windows / Office update.
Great! The problem is that everytime I try to go and get it via
Windows Update, I find it doesn't exist....

If anyone from Microsoft is reading this - any comments???

On Thursday, November 15, 2007 4:46 PM
Ox wrote:

I'm having the same problem.
I'm having the same problem. It seems to have something to do with how long
Excel has been open, in terms of the number of operations that have preceded
the Copy command. Even if I've closed all previous worksheets and saved
current work, I've had it hang up for 5 minutes. I finally shut down Excel
thru Task Manager. Then I'll start it back up and the operation takes place
instantaneously.

I've had similar slow downs when deleting large blocks of data, even when
there are no formulas in, or dependent on, the cells being cleared.

I'm running Excel 2007 on Windows XP with 2 gb of RAM.

On Wednesday, April 08, 2009 11:06 AM
Pei-Jean Chang wrote:

Do you by any chance have any formulas or conditionally formatting in place?
Do you by any chance have any formulas or conditionally formatting in place? I had the same problem where each copy and paste took what seemed like forever. It turns out that my conditional formats were slowing everything down AND making the file HUGE (15,000K). Instead of specifying ranges in my formats like I did in Excel 2003 ($b$1:$b$500), I decided to take advantage of 2007's new ability to name entire columns ($b:$b). I had about 10 formats written this way. Therefore, each time I copy/pasted a cell, Excel had to work through all 1,048,576 rows before it could finish thinking and paste. To fix this, I specified ranges in all my conditional formats and now the copy/paste works in less than a second!

Hope that helps!


Submitted via EggHeadCafe - Software Developer Portal of Choice
Are you infected by Sony
http://www.eggheadcafe.com/tutorials...d-by-sony.aspx
.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
running excel 2003 macros Terese Excel Discussion (Misc queries) 0 January 25th 08 11:32 PM
Running macros direct from toolbar in Excel 2007 ribi999 Setting up and Configuration of Excel 0 November 28th 07 06:37 PM
Running Macros in Excel 2003 mickey Excel Worksheet Functions 3 May 7th 07 10:42 AM
Anyone have troubles running 2003 macros in 2000 version of Excel Jim Excel Discussion (Misc queries) 0 April 19th 06 02:15 PM
Macros running slow in Excel 2003 ebeltran Excel Discussion (Misc queries) 0 March 8th 05 09:30 PM


All times are GMT +1. The time now is 09:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"