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.

"Paul Mac." wrote:

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.

--

Dave Peterson

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.

"Paul Mac." wrote:

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.

--

Dave Peterson

.

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." wrote:

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.

"Paul Mac." wrote:

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.

--

Dave Peterson

.


--

Dave Peterson