Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
I have 2 or 3 macros that I created in Excel 2003 that worked great....
When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
I know this sounds like an odd question, but what CPU is in the machine
you're running 2007 on? If it's a multi-core CPU such as an AMD X2 or Intel Core-Duo then the slowdown could be because of multi-threading issues in 2007 on multi-core CPUs. One test, if you do have a multi-core CPU, would be to turn off multi-threading in Excel and run the macros a few times to see if there's a speed improvement. Here's the path to the setting: [Office Button] | [Excel Options] | [Advanced] Scroll down to the [Formulas] section and uncheck the "Enable Multi-threaded calculation" setting. This has the disadvantage of turning off multi-threading in Excel on your computer for all workbooks. The problem(s) with multi-threading probably don't affect all functions that may be used within the VBA code. If turning off this feature improves performance, then one or more of your macros may be using a function that is affected by the problem. I do have some software code that you can wrap the offending process(es) in to selectively turn multi-threading off and on to try to give you optimum performance. If you decide this is the way to go, reply asking for the code and I'll post it. "Dennis" wrote: I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
I've found that macros take about twice as long, best case, in Excel 2007.
But then I've seen some macros, particularly ones that manipulate "shapes", become almost unusably slow. I think this is the way Excel 2007 is and not the result of some setting. -- Jim "Dennis" wrote in message ... I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
Excel 2007 does run macros slower, particularly with shapes and charts.
One thing to try is to make sure that you have the Google Desktop Office Addin and Google Desktop Search Office Addin unloaded: Office button--Excel Options--Addins--Com Addins--Uncheck all the Google stuff Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "Dennis" wrote in message ... I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
ActiveWorkbook.Save
Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Delete Shift:=xlToLeft Columns("E:E").Select Selection.Delete Shift:=xlToLeft Columns("A:A").ColumnWidth = 25.43 Columns("B:B").ColumnWidth = 27.57 Columns("D:D").ColumnWidth = 30.14 Range("a1:a200").Select Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("A2").Select ActiveWorkbook.Save Above is my macro.... My processor is a P$ running at 2.4GHz. I turned Multithreading off, but it didn't make any difference. I just ran this macro on a 38 row dataset, and it took 8 Minutes and 21 Seconds. I am really at a loss. "JLatham" wrote: I know this sounds like an odd question, but what CPU is in the machine you're running 2007 on? If it's a multi-core CPU such as an AMD X2 or Intel Core-Duo then the slowdown could be because of multi-threading issues in 2007 on multi-core CPUs. One test, if you do have a multi-core CPU, would be to turn off multi-threading in Excel and run the macros a few times to see if there's a speed improvement. Here's the path to the setting: [Office Button] | [Excel Options] | [Advanced] Scroll down to the [Formulas] section and uncheck the "Enable Multi-threaded calculation" setting. This has the disadvantage of turning off multi-threading in Excel on your computer for all workbooks. The problem(s) with multi-threading probably don't affect all functions that may be used within the VBA code. If turning off this feature improves performance, then one or more of your macros may be using a function that is affected by the problem. I do have some software code that you can wrap the offending process(es) in to selectively turn multi-threading off and on to try to give you optimum performance. If you decide this is the way to go, reply asking for the code and I'll post it. "Dennis" wrote: I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
Thanks for the help, what do you class shapes as? Fill colors?
That is the only thing, my dataset has other than ordinairy letter and numbers. I just did some timing, and it takes 1 minute and 49 seconds to delete 4 individual columns and set the width of 4 columns. Seems excessive to me..... Faster to do it by hand. "Jim Rech" wrote: I've found that macros take about twice as long, best case, in Excel 2007. But then I've seen some macros, particularly ones that manipulate "shapes", become almost unusably slow. I think this is the way Excel 2007 is and not the result of some setting. -- Jim "Dennis" wrote in message ... I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
Hi
Try sprinkling your code with the line DoEvents It speeded up some Excel charting for me in 2007 (from a situation where the chart would simply not update without it). I think anything happening to the GUI is very slow in 2007, so deleting columns would be a problem?? Does anyone not dislike Vista and Excel 2007??? regards Paul On Mar 3, 8:28*am, Dennis wrote: Thanks for the help, what do you class shapes as? *Fill colors? That is the only thing, my dataset has other than ordinairy letter and numbers. I just did some timing, and it takes 1 minute and 49 seconds to delete 4 individual columns and set the width of 4 columns. *Seems excessive to me..... Faster to do it by hand. "Jim Rech" wrote: I've found that macros take about twice as long, best case, in Excel 2007. But then I've seen some macros, particularly ones that manipulate "shapes", become almost unusably slow. *I think this is the way Excel 2007 is and not the result of some setting. -- Jim "Dennis" wrote in message ... I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl..... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
Have you tried
Application.ScreenUpdating = False and setting excel Calculation to Manual to see if these make any difference? Brian Murphy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
"Shapes" is the collective programming "object" for pictures, charts,
rectangles, etc. Sounds like you don't have any. Whenever something is slower by macro than manually you have a problem. Obviously something is amiss and you'd think there would be a solution. I couldn't venture a guess what it is though without seeing the problem first hand and playing around with it. -- Jim "Dennis" wrote in message ... | Thanks for the help, what do you class shapes as? Fill colors? | That is the only thing, my dataset has other than ordinairy letter and | numbers. | I just did some timing, and it takes 1 minute and 49 seconds to delete 4 | individual columns and set the width of 4 columns. Seems excessive to me..... | Faster to do it by hand. | | "Jim Rech" wrote: | | I've found that macros take about twice as long, best case, in Excel 2007. | But then I've seen some macros, particularly ones that manipulate "shapes", | become almost unusably slow. I think this is the way Excel 2007 is and not | the result of some setting. | | -- | Jim | "Dennis" wrote in message | ... | I have 2 or 3 macros that I created in Excel 2003 that worked great.... | When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... | I tried recreating them using Excel 2007, but no different. | I was wondering if there are security settings that need to be changed to | let the macros run faster....My macros run, just very very slowly. | | | |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
2007 Macro's Painfully slow
And now for the rest of the story......
The task I am trying to complete is: 1. My distant Head Office Publishes data on our private website. 2. I copy and paste one of the reports into Excel to manipulate the data before placing it into my own database. Given the answers that you have all provided, I got to thinking that maybe the data that I am scraping from the Website is full of "stuff" that Excel doesn't like. I.E. HTML code? I downloaded the data to NOTEPAD, and then with a few quick "replace" steps, was able to make the data pure text, and comma delimited. This data moves quickly to my Access Database, so problem solved.... Thanks again. PS. Once I had my data as pure text, I did place it back into Excel, just as a test, and discovered that Excel is very slow at "Maximizing the width of Columns". and "Deleting entire Columns", so should avoid those features in a Macro. The "Find and Replace" function worked very quickly once the data had been purified in NOTEPAD. Thanks Everyone for your Help. "Dennis" wrote: I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I found my solution to this problem
I was suffering of the same propblem (now much better) and never found a clear answer to that. What I found however is the following:
1- My file size in Excel 2007 is around 4 MB and that is about the same size I had when I was using Excel 2003. 2- One day I happened to delete some sheets from my file and recreated them again and saved the file and I was surprised to see that my file size has dropped down to 400 KB from 4 MB. 3- I tried the macros that were taking forever and they were executed fairly quickly (30 sec as opposed to infinity before the change). The macros were still slower than in 2003 but at least they were useful again. I'm not sure why would this happen. The file that I used was a template and after each use the template clears itself completely of all data so I'm not sure why it was having such big file (4 MB) and why deleting the sheet and then recreating it make such a big difference in the size. Anyway, it was satisfying to see that excel 2007 can actually run macros in reasonable times. M Denni wrote: 2007 Macro's Painfully slow 02-Mar-08 I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. Previous Posts In This Thread: On Sunday, March 02, 2008 4:52 PM Denni wrote: 2007 Macro's Painfully slow I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. On Sunday, March 02, 2008 5:10 PM HelpFrom wrote: I know this sounds like an odd question, but what CPU is in the machine you're I know this sounds like an odd question, but what CPU is in the machine you're running 2007 on? If it's a multi-core CPU such as an AMD X2 or Intel Core-Duo then the slowdown could be because of multi-threading issues in 2007 on multi-core CPUs. One test, if you do have a multi-core CPU, would be to turn off multi-threading in Excel and run the macros a few times to see if there's a speed improvement. Here's the path to the setting: [Office Button] | [Excel Options] | [Advanced] Scroll down to the [Formulas] section and uncheck the "Enable Multi-threaded calculation" setting. This has the disadvantage of turning off multi-threading in Excel on your computer for all workbooks. The problem(s) with multi-threading probably don't affect all functions that may be used within the VBA code. If turning off this feature improves performance, then one or more of your macros may be using a function that is affected by the problem. I do have some software code that you can wrap the offending process(es) in to selectively turn multi-threading off and on to try to give you optimum performance. If you decide this is the way to go, reply asking for the code and I'll post it. "Dennis" wrote: On Sunday, March 02, 2008 6:23 PM Jim Rech wrote: I've found that macros take about twice as long, best case, in Excel 2007. I've found that macros take about twice as long, best case, in Excel 2007. But then I've seen some macros, particularly ones that manipulate "shapes", become almost unusably slow. I think this is the way Excel 2007 is and not the result of some setting. -- Jim "Dennis" wrote in message ... On Monday, March 03, 2008 3:13 AM Charles Williams wrote: Excel 2007 does run macros slower, particularly with shapes and charts. Excel 2007 does run macros slower, particularly with shapes and charts. One thing to try is to make sure that you have the Google Desktop Office Addin and Google Desktop Search Office Addin unloaded: Office button--Excel Options--Addins--Com Addins--Uncheck all the Google stuff Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "Dennis" wrote in message ... On Monday, March 03, 2008 3:20 AM Denni wrote: 2007 Macro's Painfully slow ActiveWorkbook.Save Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Delete Shift:=xlToLeft Columns("E:E").Select Selection.Delete Shift:=xlToLeft Columns("A:A").ColumnWidth = 25.43 Columns("B:B").ColumnWidth = 27.57 Columns("D:D").ColumnWidth = 30.14 Range("a1:a200").Select Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("A2").Select ActiveWorkbook.Save Above is my macro.... My processor is a P$ running at 2.4GHz. I turned Multithreading off, but it didn't make any difference. I just ran this macro on a 38 row dataset, and it took 8 Minutes and 21 Seconds. I am really at a loss. "JLatham" wrote: On Monday, March 03, 2008 3:28 AM Denni wrote: Thanks for the help, what do you class shapes as? Fill colors? Thanks for the help, what do you class shapes as? Fill colors? That is the only thing, my dataset has other than ordinairy letter and numbers. I just did some timing, and it takes 1 minute and 49 seconds to delete 4 individual columns and set the width of 4 columns. Seems excessive to me..... Faster to do it by hand. "Jim Rech" wrote: On Monday, March 03, 2008 9:31 AM Jim Rech wrote: "Shapes" is the collective programming "object" for pictures, charts, "Shapes" is the collective programming "object" for pictures, charts, rectangles, etc. Sounds like you don't have any. Whenever something is slower by macro than manually you have a problem. Obviously something is amiss and you'd think there would be a solution. I couldn't venture a guess what it is though without seeing the problem first hand and playing around with it. -- Jim "Dennis" wrote in message ... me..... 2007. "shapes", not crawl.... to On Wednesday, March 05, 2008 1:39 AM Denni wrote: 2007 Macro's Painfully slow And now for the rest of the story...... The task I am trying to complete is: 1. My distant Head Office Publishes data on our private website. 2. I copy and paste one of the reports into Excel to manipulate the data before placing it into my own database. Given the answers that you have all provided, I got to thinking that maybe the data that I am scraping from the Website is full of "stuff" that Excel doesn't like. I.E. HTML code? I downloaded the data to NOTEPAD, and then with a few quick "replace" steps, was able to make the data pure text, and comma delimited. This data moves quickly to my Access Database, so problem solved.... Thanks again. PS. Once I had my data as pure text, I did place it back into Excel, just as a test, and discovered that Excel is very slow at "Maximizing the width of Columns". and "Deleting entire Columns", so should avoid those features in a Macro. The "Find and Replace" function worked very quickly once the data had been purified in NOTEPAD. Thanks Everyone for your Help. "Dennis" wrote: On Wednesday, March 05, 2008 6:08 AM paul.robinso wrote: HiTry sprinkling your code with the lineDoEventsIt speeded up some Excel Hi Try sprinkling your code with the line DoEvents It speeded up some Excel charting for me in 2007 (from a situation where the chart would simply not update without it). I think anything happening to the GUI is very slow in 2007, so deleting columns would be a problem?? Does anyone not dislike Vista and Excel 2007??? regards Paul On Mar 3, 8:28=A0am, Dennis wrote: e..... 7. s", d not ..... to de quoted text - On Wednesday, March 05, 2008 6:08 AM xlrotor wrote: Have you triedApplication. Have you tried Application.ScreenUpdating = False and setting excel Calculation to Manual to see if these make any difference? Brian Murphy On Wednesday, June 25, 2008 10:19 AM Norma Dowell wrote: Found a workaround I have also been struggling with macros for some time and found a workaround. It takes a few steps, but it worked and I am happy enough. Load your excel spreadsheet in Compatability Mode, or save it to begin with as an Excel 2003 file. If you have a large file and are not sure if there are hyperlinks or other hypertext in them, copy the spreadsheet. Open a new spreadsheet, click on Paste Special... under the paste icon. In the popup box, click the "Paste Link" button inthe lower left-hand corner. This will paste the sheet in a plain text, unformatted environment, similar to the previous post using Notepad, but you can stay in Excel to do this. My Macros work beautifully as before. Norma Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Custom Validation Using the Enterprise Library http://www.eggheadcafe.com/tutorials...ation-usi.aspx |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is my story
I was suffering of the same propblem (now much better) and never found a clear answer to that. What I found however is the following:
1- My file size in Excel 2007 is around 4 MB and that is about the same size I had when I was using Excel 2003. 2- One day I happened to delete some sheets from my file and recreated them again and saved the file and I was surprised to see that my file size has dropped down to 400 KB from 4 MB. 3- I tried the macros that were taking forever and they were executed fairly quickly (30 sec as opposed to infinity before the change). The macros were still slower than in 2003 but at least they were useful again. I'm not sure why would this happen. The file that I used was a template and after each use the template clears itself completely of all data so I'm not sure why it was having such big file (4 MB) and why deleting the sheet and then recreating it make such a big difference in the size. Anyway, it was satisfying to see that excel 2007 can actually run macros in reasonable times. M Denni wrote: 2007 Macro's Painfully slow 02-Mar-08 I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. Previous Posts In This Thread: On Sunday, March 02, 2008 4:52 PM Denni wrote: 2007 Macro's Painfully slow I have 2 or 3 macros that I created in Excel 2003 that worked great.... When I upgraded to Excel 2007, the macros all slowed to a snails crawl.... I tried recreating them using Excel 2007, but no different. I was wondering if there are security settings that need to be changed to let the macros run faster....My macros run, just very very slowly. On Sunday, March 02, 2008 5:10 PM HelpFrom wrote: I know this sounds like an odd question, but what CPU is in the machine you're I know this sounds like an odd question, but what CPU is in the machine you're running 2007 on? If it's a multi-core CPU such as an AMD X2 or Intel Core-Duo then the slowdown could be because of multi-threading issues in 2007 on multi-core CPUs. One test, if you do have a multi-core CPU, would be to turn off multi-threading in Excel and run the macros a few times to see if there's a speed improvement. Here's the path to the setting: [Office Button] | [Excel Options] | [Advanced] Scroll down to the [Formulas] section and uncheck the "Enable Multi-threaded calculation" setting. This has the disadvantage of turning off multi-threading in Excel on your computer for all workbooks. The problem(s) with multi-threading probably don't affect all functions that may be used within the VBA code. If turning off this feature improves performance, then one or more of your macros may be using a function that is affected by the problem. I do have some software code that you can wrap the offending process(es) in to selectively turn multi-threading off and on to try to give you optimum performance. If you decide this is the way to go, reply asking for the code and I'll post it. "Dennis" wrote: On Sunday, March 02, 2008 6:23 PM Jim Rech wrote: I've found that macros take about twice as long, best case, in Excel 2007. I've found that macros take about twice as long, best case, in Excel 2007. But then I've seen some macros, particularly ones that manipulate "shapes", become almost unusably slow. I think this is the way Excel 2007 is and not the result of some setting. -- Jim "Dennis" wrote in message ... On Monday, March 03, 2008 3:13 AM Charles Williams wrote: Excel 2007 does run macros slower, particularly with shapes and charts. Excel 2007 does run macros slower, particularly with shapes and charts. One thing to try is to make sure that you have the Google Desktop Office Addin and Google Desktop Search Office Addin unloaded: Office button--Excel Options--Addins--Com Addins--Uncheck all the Google stuff Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "Dennis" wrote in message ... On Monday, March 03, 2008 3:20 AM Denni wrote: 2007 Macro's Painfully slow ActiveWorkbook.Save Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("D:D").Select Selection.Delete Shift:=xlToLeft Columns("E:E").Select Selection.Delete Shift:=xlToLeft Columns("A:A").ColumnWidth = 25.43 Columns("B:B").ColumnWidth = 27.57 Columns("D:D").ColumnWidth = 30.14 Range("a1:a200").Select Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range("A2").Select ActiveWorkbook.Save Above is my macro.... My processor is a P$ running at 2.4GHz. I turned Multithreading off, but it didn't make any difference. I just ran this macro on a 38 row dataset, and it took 8 Minutes and 21 Seconds. I am really at a loss. "JLatham" wrote: On Monday, March 03, 2008 3:28 AM Denni wrote: Thanks for the help, what do you class shapes as? Fill colors? Thanks for the help, what do you class shapes as? Fill colors? That is the only thing, my dataset has other than ordinairy letter and numbers. I just did some timing, and it takes 1 minute and 49 seconds to delete 4 individual columns and set the width of 4 columns. Seems excessive to me..... Faster to do it by hand. "Jim Rech" wrote: On Monday, March 03, 2008 9:31 AM Jim Rech wrote: "Shapes" is the collective programming "object" for pictures, charts, "Shapes" is the collective programming "object" for pictures, charts, rectangles, etc. Sounds like you don't have any. Whenever something is slower by macro than manually you have a problem. Obviously something is amiss and you'd think there would be a solution. I couldn't venture a guess what it is though without seeing the problem first hand and playing around with it. -- Jim "Dennis" wrote in message ... me..... 2007. "shapes", not crawl.... to On Wednesday, March 05, 2008 1:39 AM Denni wrote: 2007 Macro's Painfully slow And now for the rest of the story...... The task I am trying to complete is: 1. My distant Head Office Publishes data on our private website. 2. I copy and paste one of the reports into Excel to manipulate the data before placing it into my own database. Given the answers that you have all provided, I got to thinking that maybe the data that I am scraping from the Website is full of "stuff" that Excel doesn't like. I.E. HTML code? I downloaded the data to NOTEPAD, and then with a few quick "replace" steps, was able to make the data pure text, and comma delimited. This data moves quickly to my Access Database, so problem solved.... Thanks again. PS. Once I had my data as pure text, I did place it back into Excel, just as a test, and discovered that Excel is very slow at "Maximizing the width of Columns". and "Deleting entire Columns", so should avoid those features in a Macro. The "Find and Replace" function worked very quickly once the data had been purified in NOTEPAD. Thanks Everyone for your Help. "Dennis" wrote: On Wednesday, March 05, 2008 6:08 AM paul.robinso wrote: HiTry sprinkling your code with the lineDoEventsIt speeded up some Excel Hi Try sprinkling your code with the line DoEvents It speeded up some Excel charting for me in 2007 (from a situation where the chart would simply not update without it). I think anything happening to the GUI is very slow in 2007, so deleting columns would be a problem?? Does anyone not dislike Vista and Excel 2007??? regards Paul On Mar 3, 8:28=A0am, Dennis wrote: e..... 7. s", d not ..... to de quoted text - On Wednesday, March 05, 2008 6:08 AM xlrotor wrote: Have you triedApplication. Have you tried Application.ScreenUpdating = False and setting excel Calculation to Manual to see if these make any difference? Brian Murphy On Wednesday, June 25, 2008 10:19 AM Norma Dowell wrote: Found a workaround I have also been struggling with macros for some time and found a workaround. It takes a few steps, but it worked and I am happy enough. Load your excel spreadsheet in Compatability Mode, or save it to begin with as an Excel 2003 file. If you have a large file and are not sure if there are hyperlinks or other hypertext in them, copy the spreadsheet. Open a new spreadsheet, click on Paste Special... under the paste icon. In the popup box, click the "Paste Link" button inthe lower left-hand corner. This will paste the sheet in a plain text, unformatted environment, similar to the previous post using Notepad, but you can stay in Excel to do this. My Macros work beautifully as before. Norma On Wednesday, February 24, 2010 2:24 PM Marko Polo wrote: I found my solution to this problem I was suffering of the same propblem (now much better) and never found a clear answer to that. What I found however is the following: 1- My file size in Excel 2007 is around 4 MB and that is about the same size I had when I was using Excel 2003. 2- One day I happened to delete some sheets from my file and recreated them again and saved the file and I was surprised to see that my file size has dropped down to 400 KB from 4 MB. 3- I tried the macros that were taking forever and they were executed fairly quickly (30 sec as opposed to infinity before the change). The macros were still slower than in 2003 but at least they were useful again. I'm not sure why would this happen. The file that I used was a template and after each use the template clears itself completely of all data so I'm not sure why it was having such big file (4 MB) and why deleting the sheet and then recreating it make such a big difference in the size. Anyway, it was satisfying to see that excel 2007 can actually run macros in reasonable times. M Submitted via EggHeadCafe - Software Developer Portal of Choice How to display a Gravatar Image with 100 Percent Client Script Code http://www.eggheadcafe.com/tutorials...-gravatar.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
running macro's in Excel 2007 | Excel Worksheet Functions | |||
Documents (painfully) slow to open in Excel 2007 | Setting up and Configuration of Excel | |||
Macro Running Painfully Slow! | Excel Discussion (Misc queries) | |||
Excel 2007 painfully slow to open files | New Users to Excel | |||
Hide Macro's in Toolbar / Macro's list | Excel Discussion (Misc queries) |