Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Serious Slowness in this Situation--EnableEvents apparently not the issue
Good Day,
Posts here said to use Application.EnableEvents.False at the start of certain routines and Application.EnableEvents.True at the end to expedite the routines. However, the use of Application.EnableEvents, as well as Application.ScreenUpdating, is not speeding up my code I have a worksheet named 'Students' that has a bit of worksheet_change code---includes multiple instances of Application.EnableEvents true and false depending on conditions. The code runs well when records are being added or changed in that sheet. There is a separate module that copies columns from that sheet, pastes them into another sheet, grabs data from yet another sheet, selects all the new data, then pastes (values only) back into the 'Students' sheet. During the data transfer, the status bar says Calculating 100%, but then takes anywhere from 2 minutes on a fast computer to 15 minutes on a slow computer to complete the process. I've also tried to use Application.Calculation = xlManual and xlAutomatic; no difference in speed. Questions: Does Application.EnableEvents.False in a module that writes data into a sheet with change events disable all of the change events on that worksheet, or are the worksheet change events firing when the data is being put into the sheet? Is there a way to turn off the entire worksheet change event while the module is running? Is the slowness due to the copying & pasting back & forth? But there will only be 120 to 160 records total, and only 16 columns being manipulated. Any insight would be greatly appreciated. Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Serious Slowness in this Situation--EnableEvents apparently not the issue
Enableevents= false will turn off events, so this will stop other events,
such as calculation firing. That is fine when using the event procedures, and can be useful when using a batch process, which I assume that your copy/paste macro is, if you are working on cells that are being monitored within an event procedure. Similarly, turning calculation off will help that batch process, as does screenupdating. After that,. I would guess it is a matter of looking at the code, and seeing if savings can be made by better coding techniques. Can you paste that copy/paste routine here for review? -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Arnold" wrote in message oups.com... Good Day, Posts here said to use Application.EnableEvents.False at the start of certain routines and Application.EnableEvents.True at the end to expedite the routines. However, the use of Application.EnableEvents, as well as Application.ScreenUpdating, is not speeding up my code I have a worksheet named 'Students' that has a bit of worksheet_change code---includes multiple instances of Application.EnableEvents true and false depending on conditions. The code runs well when records are being added or changed in that sheet. There is a separate module that copies columns from that sheet, pastes them into another sheet, grabs data from yet another sheet, selects all the new data, then pastes (values only) back into the 'Students' sheet. During the data transfer, the status bar says Calculating 100%, but then takes anywhere from 2 minutes on a fast computer to 15 minutes on a slow computer to complete the process. I've also tried to use Application.Calculation = xlManual and xlAutomatic; no difference in speed. Questions: Does Application.EnableEvents.False in a module that writes data into a sheet with change events disable all of the change events on that worksheet, or are the worksheet change events firing when the data is being put into the sheet? Is there a way to turn off the entire worksheet change event while the module is running? Is the slowness due to the copying & pasting back & forth? But there will only be 120 to 160 records total, and only 16 columns being manipulated. Any insight would be greatly appreciated. Arnold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Serious Slowness in this Situation--EnableEvents apparently not the issue
Hi Bob,
Thanks; I just re-worked it so as to not have to use the temp sheet and the code works faster now. Thanks for your consideration. Arnold |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slowness in Opening Excel from document shortcut | Excel Discussion (Misc queries) | |||
Autofilter Slowness | Excel Discussion (Misc queries) | |||
Is there a fix for the slowness in excel 2007? | Excel Discussion (Misc queries) | |||
Slowness When Opening XLS File | Excel Discussion (Misc queries) | |||
Excel slowness | Excel Programming |