Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop slows down
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop slows down
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 "Andrew" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop slows down
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. "Andrew" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop slows down
Do you have this line near the beginning of your code ?
Application.ScreenUpdating = False and Application.ScreenUpdating = True near the end ? -- Regards Robin "JLGWhiz" wrote: 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. "Andrew" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop slows down
On Nov 4, 11:42*am, Robin Clay <Robin_B DOT Clay AT virgin DOT net
wrote: Do you have this line near the beginning of your code ? Application.ScreenUpdating = False and Application.ScreenUpdating = True near the end ? -- Regards Robin "JLGWhiz" wrote: 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. "Andrew" wrote: 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.- Hide quoted text - - 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop slows down
"Andrew" wrote:
..... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slows after about 1,000 records? | Excel Discussion (Misc queries) | |||
Filtering slows down a macro | Excel Worksheet Functions | |||
macro slows down | Excel Programming | |||
ifsum slows down calculations | Excel Worksheet Functions | |||
Code slows on subsequent run | Excel Programming |