Excel 2003 very slow

[lallen]

have a workbook that works fine in excel 2002, but runs extremely slow in
Excel 2003. For example, one macro which is fairly complicated but doesn't
process a lot of sheets or a lot of cells, takes about 10 seconds in 2002 on
a PII-350 with 128 mb RAM and Win 98SE, and takes about 10 minutes in 2003 on
a AMD Athlon 3200+ with 512 MB RAM and XP SP2.

The following code is a stripped down version of the macro in question. It
takes 4 minutes to run.

Option Explicit
Private Sub worksheet_activate()
Dim M As Long
With Me
For M = 1 To 8
.Columns(M).ClearContents
Next M
.Cells(10, 3) = "Tax Report"
.Cells(1, 1) = "3. Mutual fund units, deferral of " _
& "eligible small business corporation shares," _
& " and other shares including "
.Cells(2, 1) = "publicly traded shares"
.Cells(4, 1) = "Number"
.Cells(4, 2) = "Name & Class"
.Cells(4, 4) = "Yr Acq"
.Cells(4, 5) = "Proceeds"
.Cells(4, 6) = "Cost Base"
.Cells(4, 7) = "Expenses"
.Cells(4, 8) = "Gain (Loss)"
End With
End Sub

This is a new install of Office 2003, so I'm wondering if something is not
set up properly. Anybody have any ideas?

 

[Jim Rech]

I have to assume this is a large workbook that takes a while to calculate
and that calc mode is automatic. If that's the case you'd want to switch to
manual calc.

Your code clears the first 8 columns every time you activate the sheet. I
guess you know what you're doing but that seems very odd to me.

Why clear columns one at a time when you can do them all at once?:

..Range("A:H").ClearContents

 

[lallen]

Thanks Jim. Switching between manual and automatic calculation didn't make
any difference (same with turning Screen Updating off), but I discovered that
Page Breaks was on by default, and turning it off made a significant
difference. My main macro is still significantly slower (20 seconds as
opposed to 2), but I can live with it now. It's a lot better than 10 minutes.