Macro speed change when closing file

[snax500]

In Excel20000, how come sometimes a macro runs really slowly. I
sometimes have to Control-Break the macro if it is running really slow
(many minutes). I then close Excel and rerun the same macro and it
takes only seconds. Does anyone know why this might be happening?

Thanks

 

[Dave Peterson]

Just a guess...

I'd turn calculation to manual, then do the work, then change the calculation
back to whatever it was before.

In fact, there are a few things that can slow down macros. I do this kind of
thing to speed them up:

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

Your code will replace the "'do the work" line.

When/if you're hiding rows/columns, excel wants to figure out where to draw
those lines each time you change the layout.

 

[Gary Brown]

Can be many reasons. Could be a poorly written macro that is a memory hog.
Could be other things running on your system are pulling memory needed for
Excel. Could be the macro is not releasing memory so if you run it a number
of times, less and less memory is available for processing.
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Mike H.]

One reason this seems to vary is that you have other files in memory at the
time you're running the macro and they have overhead that if you don't do the
things Dave Peterson said to do, will cause BIG problems one time, but the
next time may not slow down much because that particular file is not in
memory. So just always do what Dave said to do and your problem should be
solved.