Loop slows down

[Andrew]

I have a loop that runs really fast at first (15+ records per second),
but then continually slows down (2 per second). It seems to be after
2000-3000 records. It gets much slower when excel is not selected and
speeds up when it is selected. I read some information on optimization
and it helped speed it up at the beginning, but the problem of slowing
down still occurs.

Has anyone heard of this?

By the way, I read records from sheets and write back to them. I also
have a SQL query in a sheet that refreshes for each record.

Any suggestions are welcome.

 

[Peter T]

There can be a difference in speed writing to cells when an object is
selected rather than a cell, and better still to select an empty cell. I'm
not aware though of there being any difference if the application is/not
active in Windows.

When reading and (particularly) writing to cells it's best to to use an
intermediate array and assign/write in one go (but if more than say 100k
cells do it in chuncks)

' read
dim i as long, j as long
Dim arr
arr = range("A1:C10")

for j = 1 to ubound(arr)
for k = 1 to ubound(arr,2)

' process(arr(j,k)
next
next


' write
Redim arr(1 to myRows, 1 to myColumns)

' populate the array

dim rng as Range

set rng = "Range("a1")

set rng = rng.resize(ubound(arr), ubound(arr,2))

rng.Value = arr


(not tested, typos a distinct possibility!)

Regards,
Peter T

 

[JLGWhiz]

From what you describe, it sounds like normal operation. The slow down
occurs for a couple of reasons. One is that with that many records doing a
read/write operation, the memory is stressed. Another is the refresh
operation which suspends the read/write operation. You might be able to
re-organize the code and speed it up a little.

 

[Robin Clay]

Do you have this line near the beginning of your code ?

Application.ScreenUpdating = False

and

Application.ScreenUpdating = True

near the end ?

 

[Andrew]

Do you have this line near the beginning of your code ?

Application.ScreenUpdating = False

and

Application.ScreenUpdating = True

near the end ?
--
Regards

Robin






- Show quoted text -

I turn off screen updating. I have changed some things so that I am
not reading and writing as much, and it makes if faster, but the slow
down still occurs. After it has been running a while, the difference
in speed between having excel selected or not can be 10 fold. I still
don't understand why the speed changes at all. Is there a way to clear
memory? Will setting local variables to nothing after I am done speed
anything up?

 

[Robin Clay]

.....
After it has been running a while, the difference
in speed between having excel selected or not can be 10 fold. I still
don't understand why the speed changes at all. Is there a way to clear
memory? Will setting local variables to nothing after I am done speed
anything up?

Well .... this is a whole new ball game !

I suggest you might get ideas from a "Windows" discussion group....

Try Control Panel > System > Advanced > Performance > Settings

Etc.,


Regards

Robin