Excel .xlsm / Vista / Speed Problem

[WCM]

I am running Excel 2007 on Vista (2GB Ram / 3GHz CPU). I am working on an
..xlsm file with 6 short macros and 500 4-column rows of data.

Switching back and forth between the Excel sheet and the VBA code window
takes way too long (about 10 seconds) and saving the workbook also takes
approx 10 seconds. If I use Excel 2000 for the same work, it is extremely
fast - not enough delay to notice.

Nothing wrong with my machine. Anyone know why working on an .xlsm workbook
would on Vista would be so slow? Am I missing something?

Thanks for your help ...

Bill

 

[WCM]

ps: I timed how long it takes workbook to save: 25 seconds.

 

[Barb Reinhardt]

I know that things are slower on 2007 than 2003, but there may be some things
you can do to speed up execution. I usually add at least this to do that

Application.Calculation = XLCalculationManual
Application.ScreenUpdating = False
Application.Enableevents = False

(above before code)
(below after code)

Application.Calculation = XLCalculationAutomatic
Application.ScreenUpdating = True
Application.Enableevents = True

HTH (some)

Barb Reinhardt

 

[Martin Brown]

ps: I timed how long it takes workbook to save: 25 seconds.

Are charts and graphs involved? That is the only situation I have found
where the performance of XL2007 is more than an order of magnitude
slower and borderline unstable because of race conditions.

The sorts of things the go wrong are axes get referneced by VBA before
they have been drawn. You can fix it with judicious use of delays and
that appears to have been the bodge applied to the main code too

Some of my calibration spreadsheets with many graphs that were a couple
of seconds or less on XL2003 now have to display an estimated wait time
in minutes to stop the users from aborting it!

Regards,
Martin Brown

 

[Jim Cone]

For what it is worth...
MS is now bragging? http://blogs.msdn.com/excel/
that xl2010 (preview version) handles charts much faster then xl2007.
It will only require ~twice as much time as XL2003.

 

[WCM]

Hi Martin,
Thank you for your response. No, I do not have any charts for other
graphics - only several simple macros that do easy updates on 500 rows of
data. Same situation in Excel2003 would take 1-2 seconds to save.

 

[WCM]

Hi Barb,

Thanks for your suggestion. Problem isn't that the macros run slow - they
actually run fine - it is the development process itself. I have to wait a
long time for any macro changes I make to 'save' and saving workbook takes 25
seconds. A bit frustrating since I'm used to "lightening speed" in 2003. I
am actually getting used to 'multi-tasking' while waiting for workbook to
save ... work in sql server or something to use up the time, then come back
to workbook ...

 

[WCM]

Hi Jim,

Not too encouraging ... don't want to revert to 2003 as I'm now used to the
many design changes in 2007, but this is first time I've had to run VBA in
2007 - and like I say, run speed is okay it is the development that is slow.

If I find a solution, I will come back here to post.

 

[Don Guillett]

Do you have formulas using entire columns?
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Don Guillett]

As I said, I'll be happy to take a look.

 

[WCM]

Don,
I inadvertently skipped over your response. Suggestion is a good one as I
have made that mistake before. I am reviewing workbook and will get back to
you. I this doesn't solve problem, I will send you file for analysis on your
end. Thanks for your response ...

Bill

 

[Don Guillett]

Many links that went nowhere and defined names with non existent references
may be the problem.

 

[WCM]

Don,

That was it!! Thank you so much for your review. There were many
connections to other files that were totally unnecessary. After removing
them per your suggestion (Data > Connections), the workbook saves in a flash
and opens just as fast. Thank you again ...

Bill

Many links that went nowhere and defined names with non existent references
may be the problem.