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:
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.
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
:
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
--
Ji
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
RE: 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
").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
").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.
:
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.
:
"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
me.....
2007.
"shapes",
not
crawl....
to
RE: 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.
:
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
e.....
7.
s",
d not
.....
to
de quoted text -
Have you triedApplication.
Have you tried
Application.ScreenUpdating = False
and setting excel Calculation to Manual
to see if these make any difference?
Brian Murphy
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
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/tutorial...c-b0877c10ecb4/how-to-display-a-gravatar.aspx