Code slows on subsequent run

  • Thread starter Thread starter Paul Mac.
  • Start date Start date
P

Paul Mac.

Hi All,

Currently I have a report being generated by a series of
for each statements. This in total requests approximately
15,000 range validations. On top of this there are checks
and validations from collection objects for the item to
be included in the report.

The issues is:

On the first pass of running the report, it completes in
approximately 3 Seconds. After that, it takes over 30.
I'm wondering if any variables are loaded into memory
that are slowing the process down. An intial selection is
made from a calendar form (not MSCAL.ocx). Which has not
been unloaded at this stage.

Is it possible to clear the memory (as if you had closed
& re-opened excel) at the completion of the report?

Compile is Option Explicit, so there are no un-declared
variables.

XL XP

Ideas, anyone?

Thanks in advance.

Paul Mac.
 
I'm guessing that if you're doing something with items to be included in a
report, then maybe you're hiding or deleting rows.

Maybe adding something like:
ActiveSheet.DisplayPageBreaks = False

will speed up your code--xl won't try to determine where those little dotted
lines go after each deletion/hide.
 
Hi Dave,

Thanks for your reply. I have been had by that speed
issue before and have learn't fom my ways. Although the
reports are inserting rows etc. They are not set to
sepecific break points or PGBreak Preview. This
[Breakpoint] is programatically done at the end of the
compile.

When set to ScreenUpdate = TRUE, you can noticably see
the difference during RunTime. The Process occurs at
almost a unnoticably quick rate (the screen is flickering
and Scrolling), where on the second run it is
significantly slower, you can see the chages & progress
as they occur.

There is no real spikes in Memory, CPU spikes during
runtime, but returns on the end. Everything seems in
order, but just wondering if there is a command line that
can clear cache or pagefile memory, or something like
that. Is there a time function to see the compile time?
Or the run-time, then maybe I could show you the interval
times of each separate instance.

Any more help would be great.

Thanks again.

Paul.
 
I'm not quite sure what you mean by [breakpoint]. The problem is that excel
wants to determine where to put those little dotted lines after you insert
stuff.

If you turn this display off, then excel won't care.

At the top of many of my macros:

Dim CalcMode As Long
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False

and near the bottom:

Application.Calculation = CalcMode
Application.ScreenUpdating = True

(Did you try it to see if it helped?)

And no, I don't know of a line of code that will do what you ask.

Paul Mac. said:
Hi Dave,

Thanks for your reply. I have been had by that speed
issue before and have learn't fom my ways. Although the
reports are inserting rows etc. They are not set to
sepecific break points or PGBreak Preview. This
[Breakpoint] is programatically done at the end of the
compile.

When set to ScreenUpdate = TRUE, you can noticably see
the difference during RunTime. The Process occurs at
almost a unnoticably quick rate (the screen is flickering
and Scrolling), where on the second run it is
significantly slower, you can see the chages & progress
as they occur.

There is no real spikes in Memory, CPU spikes during
runtime, but returns on the end. Everything seems in
order, but just wondering if there is a command line that
can clear cache or pagefile memory, or something like
that. Is there a time function to see the compile time?
Or the run-time, then maybe I could show you the interval
times of each separate instance.

Any more help would be great.

Thanks again.

Paul.
-----Original Message-----
I'm guessing that if you're doing something with items to be included in a
report, then maybe you're hiding or deleting rows.

Maybe adding something like:
ActiveSheet.DisplayPageBreaks = False

will speed up your code--xl won't try to determine where those little dotted
lines go after each deletion/hide.



--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
Back
Top