Rerunning Macro takes longer after each consecutive use....Why?

[Guest]

I have a timer in a VBA sub that copies data from one workbook to a series of
customer forms.

I've noticed that each consecutive time I re-run the same exact macro with
the same data, it takes a couple seconds longer. If I completely close
excel, it seems run faster, then get slower again.

Looking at windows task manager, my memory usage isn't going up so I'm
wondering what I'm doing wrong.

1st run: 6 sec
2nd run: 7 sec
3rd run: 8 sec
4th run: 10 sec
5th run: 12 sec
6th run: 13 sec
7th run: 14 sec

This is the first time I've done the following and I don't have problems
with any other macros taking longer:
1. Use Application.EnableEvents = False
2. Reference named ranges more instead of load data into array first.


Thanks, MikeZz

 

[Guest]

Is the macro doing increasing amounts of work or is the worksheet essentially
unaltered by running the macro? Are the named ranges increasing in size?
Have you tried setting calculation to manual at the start of the macro?
Turned screenupdating to false?

 

[Dave Peterson]

(Saved from a previous post)

I would think that it would depend a lot on what your macro does--if it
inserts/deletes rows or columns, then...

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?

Since you're changing columnwidths, excel could be figuring out where those
dotted lines go.

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

'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.

 

[Guest]

The first time it runs, it copies data from my workbook to many sheets in the
customer file. The second and subsequent times, it just overwrites the same
data so I think it's just repeating what it did the first time.

Have not tried setting Calculate to Manual yet. Never have in the past and
never had this type of problem.
The macro just pastes values from my workbook to the customer workbook which
has many sheets.
There are no Insert Rows so the sheet doens't get bigger.
The named ranges don't grow in sizez.
Screen Udpating is turned off.
I also had to set "EnableEvents=false" to eliminate nagging pop-ups and
validation checks on the customer form.

 

[Guest]

Hi Dave,
I don't do any column width changing. Just paste values from my workbook to
the customer workbook. There aren't a massive amount of formulas so I don't
think it's the calculation udpate that's causing the problem. In addition,
each time I run the macro, it's just re-copying what the previous macro had
done so it's not doing anything different.

Thanks for the help.

 

[Dave Peterson]

The columnwidths stuff was germane to the other post--I should have taken that
out before I pasted.

Did you try the other suggestions.

 

[Guest]

Hi Dave,
Did all the suggestions but no changes.

The columnwidths stuff was germane to the other post--I should have taken that
out before I pasted.

Did you try the other suggestions.

 

[Dave Peterson]

Maybe you could add a few strategically placed:
Debug.print "Step ###: " & now
into your code.

Then you may find out what area is taking the most time and see if you can
improve that area?????

Hi Dave,
Did all the suggestions but no changes.

 

[Guest]

That's a good idea.
Just curious though, I haven't used the .print since college maybe 15 years
ago. Where does it print to? ie... where does the log data go?

Thanks,

 

[Dave Peterson]

It prints to the VBE's immediate window.

You can see it by hitting ctrl-g or View|immediate window (when you're in the
VBE).

That's a good idea.
Just curious though, I haven't used the .print since college maybe 15 years
ago. Where does it print to? ie... where does the log data go?

Thanks,