Help - Excel 2003 Macro execution SLOW!

[C C]

Hello.

After I installed WinXP SP2, my Excel macros
execute very slow. A macro to convert a 7-meg .w3c
file (ISA Server 2004 log) completes after 30 minutes
or so. This used to take around 2 minutes pre-SP2.

What happened?

Thanks in advance.

If more info needed, please let me know.

 

[windsurferLA]

Using Windows Task Manager, check to see the load on your RAM. Perhaps
SR2 increased the amount of memory required to run Windows to the point
where there is no longer RAM enough to run your application.

 

[C C]

Thanks. I looked and I have 120Meg "Available".
I looked at the task manager while the Excel was
doing the convert macro. This convert macro
converts the date/time stamp, re-arranges columns
(cut/paste-to-a-different-column), etc., of a
..w3c firewall log file.

SLOWWW.

 

[Dave Peterson]

Maybe it's not the upgrade to SP2 that's causing the trouble.

Maybe it's the macro itself.

If you've done a print (or print preview), you'll see those little dotted lines
indicating where the pages should be. If you're macro deletes/inserts
rows/columns, then excel will try to determine where those dotted lines should
be. If you have a lot of deletes/inserts, your macro can slow to a crawl.

In fact, I've seen posts that describe slow downs when in pagebreak preview
mode.

Maybe making your macro look more like this would help:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

It can't hurt to try.

 

[C C]

Okay,

I'll try your advice BUT I have been using this
Macro since pre-SP2 and I haven't changed/revised
it. In fact, not only the Macro in Excel is slow, the
entire Excel is slow. If highlight a bunch of rows
say 500 of them and delete the rows, it takes a long
time for Excel to delete the entire selection and I see
the hourglass mouse cursor for a longer than normal
time during pre-SP2.

 

[Dave Peterson]

Here are a couple of sites that discuss slow performance. The first is by
Charles Williams and the second by David McRitchie:

http://www.decisionmodels.com
http://www.mvps.org/dmcritchie/excel/slowresp.htm