Macros Execute Slowly

[Rene''48]

Hello Tech Support

I am developing macros in Excel 2003.

I have a main macro that has 7 other sub macros. The first time I run the
macro it executes very fast. If I run the macro again it executes slowly.
As suggested added the command Application.ScreenUpdating = False at the
beginning of my macro and Application.ScreenUpdating = True at the end of my
macro. When I ran the macro again it still does not execute as fast as it
did the first time. However the Application.ScreenUpdating does make it run
faster but not as fast as it ran the first time I executed the same macro. I
had to close excel and open the file again to make the macro execute fast. As
a test I made copies of the file and opened one at a time. What I found was
if I opened the copied file for the first time the macro executed very fast.
Does Excel remember all the macros that were executed in the file while the
file is still open? I believe all procedures are saved somewhere in Excels
memory making the larger macro execute more slowly. If this is true is there
a command that would wipe out the memory in excel so my macro could execute
quickly?

If what I am saying sounds confusing is there a number I could call and talk
someone?

Thank you for your help.

Rene’

 

[Dave Peterson]

(Saved from a previous post)

Do you see the dotted lines that you get after you do a print or print preview?

If you do
Tools|Options|view tab|uncheck display page breaks

does the run time go back to normal?

You may want to do something like:

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 (Your code goes here)

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

End Sub

Being in View|PageBreak Preview mode can slow macros down, too.

 

[Rene''48]

Hi Dave,

It was the printview macro that caused my main macro to run very slow the
next time I ran it. I used ActiveSheet.DisplayPageBreaks = False at the end
of the macro. That solved my problem.
Thanks so much for your help.