Excel 2007 files running very slow

[paulfj]

First off, I really like many of the changes made in 2007. However, I
have a number of worksheets that were created in 2003 and used by
scores of people. The problem I'm running into is some of these files
are getting slower, and slower, and slower. I'm running XP, Core 2 Duo
processor, 1 GB Ram. The main file I'm concerned about tracks
students' attendance throughout the semester. I have a number of
macros to "help speed up" the process, plus a number of formulas and
formatting on each worksheet. There's about 6-8 worksheets in each
workbook. I've saved the file as a Macro-Enabled Workbook. The
workbook worked (almost flawlessly) in 2003 and was very fast. That is
no longer the case. What used to take 20 seconds is now taking up to
and sometimes more than 10 minutes. This is disconcerting for me, but
since the above mentioned group of people has just transitioned to
2007 I'm going to be facing a real problem in the near future. Any
help would be appreciated!

I've looked through this and other groups, but I haven't found a
solution that has had a long term solution for this dilemma. Thanks
again for any help.

 

[AltaEgo]

Macros are a possible source of poor performance. Are you turning off
automatic recalculation and screen updating before you run them? Are you
looping through a lot of cells using 'select'?

There is a lot of good advice here:
http://www.mvps.org/dmcritchie/excel/slowresp.htm
http://www.decisionmodels.com/calcsecrets.htm
http://www.avdf.com/apr98/art_ot003.html

 

[paulfj]

Macros are a possible source of poor performance. Are you turning off
automatic recalculation and screen updating before you run them? Are you
looping through a lot of cells using 'select'?

There is a lot of good advice here:http://www.mvps.org/dmcritchie/exce...s.htmhttp://www.avdf.com/apr98/art_ot003.html

--
Steve







- Show quoted text -

Thanks, Steve. I'll check these links out. I do turn off screen
updating, but I haven't done anything with automatic recalc. If I'm
still running into a problem after this, I'll be sure to let the group
know.

 

[paulfj]

Thanks, Steve. I'll check these links out. I do turn off screen
updating, but I haven't done anything with automatic recalc. If I'm
still running into a problem after this, I'll be sure to let the group
know.- Hide quoted text -

- Show quoted text -

Actually, the problem was tied to some conditional formatting that had
started to grow like kudzu. I suspect some of my macros may be
functioning with a little different results than in 2003. Fortunately,
since the intended cond form was peripheral in nature, I cleared all
the conditional formatting rules (and there were thousands of rules
which I can't explain) and the workbook purrs just like it used to.

I'm still going to try to figure that out though just for my own sake.
None of my macros have anything to do with conditional formatting and
while some of them copy data from multiple sheets onto a hidden sheet
in order to mass sort and paste the info back to the original sheet, I
don't see why this would cause the fiasco that it did, especially
since it never did before for 4 years.

Anyway, thanks for the help. I learned some other things along the way
that I'm sure I'll put into use in other projects!

 

[Don Guillett]

For your CF you could use macros to paint the cells as desired. An example
of one I did very recently.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a2:g22")) Is Nothing Then Exit Sub
tr = Target.Row
Select Case UCase(Target)
Case Is = "PENDING": mi = 0
Case "A+": mi = 44
Case "A", "B", "C", "D": mi = 6
Case Else: mi = 20
End Select
Range(Cells(tr, "a"), Cells(tr, "s")).Interior.ColorIndex = mi
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks, Steve. I'll check these links out. I do turn off screen
updating, but I haven't done anything with automatic recalc. If I'm
still running into a problem after this, I'll be sure to let the group
know.- Hide quoted text -

- Show quoted text -

Actually, the problem was tied to some conditional formatting that had
started to grow like kudzu. I suspect some of my macros may be
functioning with a little different results than in 2003. Fortunately,
since the intended cond form was peripheral in nature, I cleared all
the conditional formatting rules (and there were thousands of rules
which I can't explain) and the workbook purrs just like it used to.

I'm still going to try to figure that out though just for my own sake.
None of my macros have anything to do with conditional formatting and
while some of them copy data from multiple sheets onto a hidden sheet
in order to mass sort and paste the info back to the original sheet, I
don't see why this would cause the fiasco that it did, especially
since it never did before for 4 years.

Anyway, thanks for the help. I learned some other things along the way
that I'm sure I'll put into use in other projects!