macro runs very slowly

[Dan Wasser]

The first time that I run my 6,000-line macro, it zooms.
Each time thereafter, it's runs VERY slowly unless I close
the workbook and reopen it. Then, it runs fast the first
time but, again, runs VERY slowly thereafter (unless,
again, I close the workbook and reopen it). The macro
does a lot of copying-and-pasting. My machine has, like
2GB, of RAM. Any idea why the macro would run so slowly
the SECOND time?

 

[Tom Ogilvy]

Does it do any printing. If the pagebreaks are visible, this can cause a
macro to slow down.

 

[Dan]

Hi, Tom. No, there's no printing involved. And no page
breaks either.

 

[shockley]

Dan,

How much available memory is showing in Task Manager/Performance after the
first run, before you close the workbook?

Shockley

You can

 

[Dan Wasser]

Before the 1st run:
Avail: 618,504
Cache: 690,496

After the 1st run:
Avail: 608,424
Cache: 688,060

After the 2nd run (without exiting the workbook):
Avail: 607,612
Cache: 687,620

My machine has 2GB of RAM.

 

[shockley]

Well, back to the drawing boards...

Has the problem grown as the project has grown or did it all of a sudden
come up?

Why so much copy/pasitng? Can't you use something like

Set rngSource = Sheets("aaa").Range("xxx")
Set rngDest = Sheets("bbb").Range("yyy")
rngDest.Value = rngSource.Value

This eliminates the need to select sheets and is much, much faster.

If this turns out to be the problem you could probably write a script to
automatically edit in the changes. Formatting could be transferred too if
necessary.

 

[Dan Wasser]

Thanks for the reply, Shockley.

I'm sorry to admit that I think it happened all of the
sudden ... like a year or so ago (I'm constantly
tweaking/changing the macro.) I guess I should have
jumped on it at that time. Too late now, I guess.

Why so much copy/pasting? Basically because I'm a
salesperson, not a programmer, trying to use Excel/macros,
and I have no idea what I'm doing. I suspect that I've
programmed the macro using a sledgehammer rather than a
paintbrush.

Oh, well. Thanks anyway. Dan

 

[shockley]

I hope it's something you can live with. Probably someone with more software
development knowledge than me could give you some possibilities to check.
One easy thing to try would be to 'clean' the code. This is done by
exporting your modules (and other project objects), deleting them from the
workbook, and then importing them back in. A lot of mysterious problems can
be cleared up this way. If you have a lot of modules, there is an automated
program to do this:
http://www.appspro.com/utilities/Cleaner.asp

Regards,
Shockley

 

[Toby Erkson]

Good info to know

Why so much copy/pasting? Basically because I'm a
salesperson, not a programmer, trying to use Excel/macros,
and I have no idea what I'm doing. I suspect that I've
programmed the macro using a sledgehammer rather than a
paintbrush.

Not sure if this will help, but stranger Microsoft things have happened ;-)
At the beginning of your macro (before all your code starts getting to work)
insert this:
Application.ScreenUpdating = False

Right before the END SUB of the macro insert this:
Application.ScreenUpdating = True

What this does is keep the workbook from being displayed while it's going
through its update routine. When the macro is done the final result will be
seen.

There are other ways to speed things up but I can't remember what the thread
was that had the info...and I'm a beginning VBA programmer as well so that's
why I can't offer much more help. I would recommend you find someone who
knows VBA and have them assist you in streamlining your macro(s).

Toby Erkson
Oregon, USA
Toby Erkson
Oregon, USA

 

[Alex J]

How about cleaning out any stray .emf files from the Temp directory. This
can REALLY screw up performance, and the amount of available RAM has nothing
to do with it.

For reference see: http://www.mvps.org/dmcritchie/excel/slowresp.htm (among
others)


AlexJ

 

[shockley]

Another possibility is you have some variables that are persisting even when
all execution has completed in the workbook. I have seen this happen with
variables declared as Public: execution completes, and then when I run the
first macro the next time, a variable already contains a value. Not sure why
or if it's supposed to be this way. So, if you have some variables
persisting, especially object variables or possibly a string variable
containing a very long string, Excel may have used up the memory allocated
to it by the operating system and is forced to do a lot of churning. Not
sure if this is a technically plausible scenario, but programmers are
encouraged to be careful about setting variables--especially object
variables--back to empty/Nothing when they are no longer needed.

You should be able to check for this the second time you run your program by
first opening the vb editor, pressing F8 to step into the first macro, and
then opening the locals window to see if any variables contain anything.

Shockley

 

[Joseph]

Hi,

Have you tried purging the contents of the clipboard every time yo
finish a paste operation? I may be telling you something you alread
know here having not seen the code but I would use this line of cod
directly after a paste operation:

Application.CutCopyMode = False

Cheers
Jo

 

[Dan Wasser]

Thank you, everyone, for your suggestions. I'm going to
give 'em a try.

Dan