Home 
Search 
Today's Posts 
#1




"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O)
HI
We are facing a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in the usage ot the Group function... Using the exact same Excel file that have around 1000 lines with several level of grouping, the time needed to group under level 1 or 2 is around 2 to 3 seconds with Excel 2000 while it is around 25 to 30 seconds with Excel 2003. In both case the same function is quite immediat if we settle the calculation mode to manual... The same phenomenon can be seen if you ask for the group function on a file without any formulas while another big excel file with a lot of formulas is open at the same time but not active... Is there something different than the calculation mode that should be settled in Excel 2003 in order to get the same response time on group function than in Excel 200? Any other information, experience about that problem? Is it link to a different behavior of excel in front of calculation strategy? Thanks for your help Alain79 
#2




Just a guess...
xl2003 added some options to the =subtotal() worksheet function. In earlier versions, =subtotal() would ignore the rows that were hidden by filtering (datafilterautofilter for example). In xl2003, you can use =subtotal(10x,a1:a99) to ignore cells hidden manually. My bet is that is what causes excel to recalc. The only way around it (that I know) is to change the calculation mode before and afteror even interupt the calculation in mid stream. I hit the escape key to interupt that calculation. Excel will catch up when it can. Usually when I'm staring at the data and my fingers are off the keyboard/mouse. Watch the statusbar. If it says Calculate, excel hasn't finished. Alain79 wrote: HI We are facing a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in the usage ot the Group function... Using the exact same Excel file that have around 1000 lines with several level of grouping, the time needed to group under level 1 or 2 is around 2 to 3 seconds with Excel 2000 while it is around 25 to 30 seconds with Excel 2003. In both case the same function is quite immediat if we settle the calculation mode to manual... The same phenomenon can be seen if you ask for the group function on a file without any formulas while another big excel file with a lot of formulas is open at the same time but not active... Is there something different than the calculation mode that should be settled in Excel 2003 in order to get the same response time on group function than in Excel 200? Any other information, experience about that problem? Is it link to a different behavior of excel in front of calculation strategy? Thanks for your help Alain79  Dave Peterson 
#3




Thanks Dave for this information  It is the first consistant one I ever get
on this tiopic but I do not feal so safe with it... I have already tried to stop the calculation by hitting the esc key  it sometimes work, sometimes not... If you are right, do you know another way to interrupt? Is there any configuration parameter that would permit to modify the excel behavior... Is there a way to intercept the Grouping command by some code? additional info: In my case, Excel tell very quickly "calculation 100%" but then wait some more tens of seconds before finishing the grouping... Thanks once more Alain79 "Dave Peterson" wrote in message ... Just a guess... xl2003 added some options to the =subtotal() worksheet function. In earlier versions, =subtotal() would ignore the rows that were hidden by filtering (datafilterautofilter for example). In xl2003, you can use =subtotal(10x,a1:a99) to ignore cells hidden manually. My bet is that is what causes excel to recalc. The only way around it (that I know) is to change the calculation mode before and afteror even interupt the calculation in mid stream. I hit the escape key to interupt that calculation. Excel will catch up when it can. Usually when I'm staring at the data and my fingers are off the keyboard/mouse. Watch the statusbar. If it says Calculate, excel hasn't finished. Alain79 wrote: HI We are facing a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in the usage ot the Group function... Using the exact same Excel file that have around 1000 lines with several level of grouping, the time needed to group under level 1 or 2 is around 2 to 3 seconds with Excel 2000 while it is around 25 to 30 seconds with Excel 2003. In both case the same function is quite immediat if we settle the calculation mode to manual... The same phenomenon can be seen if you ask for the group function on a file without any formulas while another big excel file with a lot of formulas is open at the same time but not active... Is there something different than the calculation mode that should be settled in Excel 2003 in order to get the same response time on group function than in Excel 200? Any other information, experience about that problem? Is it link to a different behavior of excel in front of calculation strategy? Thanks for your help Alain79  Dave Peterson 
#4




I think Escape has always worked for me to interrupt the calculation. But
typing anything into a cell also tells excel to stop calculating and wait for the user to finish. I don't think that there's any configuration parm that stops this, but maybe someone else knows of one. Maybe you could add a couple of macros to your favorite toolbar that toggles calculation mode. Alain79 wrote: Thanks Dave for this information  It is the first consistant one I ever get on this tiopic but I do not feal so safe with it... I have already tried to stop the calculation by hitting the esc key  it sometimes work, sometimes not... If you are right, do you know another way to interrupt? Is there any configuration parameter that would permit to modify the excel behavior... Is there a way to intercept the Grouping command by some code? additional info: In my case, Excel tell very quickly "calculation 100%" but then wait some more tens of seconds before finishing the grouping... Thanks once more Alain79 "Dave Peterson" wrote in message ... Just a guess... xl2003 added some options to the =subtotal() worksheet function. In earlier versions, =subtotal() would ignore the rows that were hidden by filtering (datafilterautofilter for example). In xl2003, you can use =subtotal(10x,a1:a99) to ignore cells hidden manually. My bet is that is what causes excel to recalc. The only way around it (that I know) is to change the calculation mode before and afteror even interupt the calculation in mid stream. I hit the escape key to interupt that calculation. Excel will catch up when it can. Usually when I'm staring at the data and my fingers are off the keyboard/mouse. Watch the statusbar. If it says Calculate, excel hasn't finished. Alain79 wrote: HI We are facing a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in the usage ot the Group function... Using the exact same Excel file that have around 1000 lines with several level of grouping, the time needed to group under level 1 or 2 is around 2 to 3 seconds with Excel 2000 while it is around 25 to 30 seconds with Excel 2003. In both case the same function is quite immediat if we settle the calculation mode to manual... The same phenomenon can be seen if you ask for the group function on a file without any formulas while another big excel file with a lot of formulas is open at the same time but not active... Is there something different than the calculation mode that should be settled in Excel 2003 in order to get the same response time on group function than in Excel 200? Any other information, experience about that problem? Is it link to a different behavior of excel in front of calculation strategy? Thanks for your help Alain79  Dave Peterson  Dave Peterson 
#5




I have finaly added some more command to my application
Those commande being = show level 1 = show level 2 = show level 3 = show level 4 = show all lines and the basic code behind is Sub ShowChapterLevel_subroutine(Level As Integer) Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ActiveSheet.Outline.ShowLevels Level Application.ScreenUpdating = True Selection.Activate Selection.Show Application.Calculation = xlCalculationAutomatic End Sub Thanks for yor help Alain "Dave Peterson" wrote in message ... I think Escape has always worked for me to interrupt the calculation. But typing anything into a cell also tells excel to stop calculating and wait for the user to finish. I don't think that there's any configuration parm that stops this, but maybe someone else knows of one. Maybe you could add a couple of macros to your favorite toolbar that toggles calculation mode. Alain79 wrote: Thanks Dave for this information  It is the first consistant one I ever get on this tiopic but I do not feal so safe with it... I have already tried to stop the calculation by hitting the esc key  it sometimes work, sometimes not... If you are right, do you know another way to interrupt? Is there any configuration parameter that would permit to modify the excel behavior... Is there a way to intercept the Grouping command by some code? additional info: In my case, Excel tell very quickly "calculation 100%" but then wait some more tens of seconds before finishing the grouping... Thanks once more Alain79 "Dave Peterson" wrote in message ... Just a guess... xl2003 added some options to the =subtotal() worksheet function. In earlier versions, =subtotal() would ignore the rows that were hidden by filtering (datafilterautofilter for example). In xl2003, you can use =subtotal(10x,a1:a99) to ignore cells hidden manually. My bet is that is what causes excel to recalc. The only way around it (that I know) is to change the calculation mode before and afteror even interupt the calculation in mid stream. I hit the escape key to interupt that calculation. Excel will catch up when it can. Usually when I'm staring at the data and my fingers are off the keyboard/mouse. Watch the statusbar. If it says Calculate, excel hasn't finished. Alain79 wrote: HI We are facing a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in the usage ot the Group function... Using the exact same Excel file that have around 1000 lines with several level of grouping, the time needed to group under level 1 or 2 is around 2 to 3 seconds with Excel 2000 while it is around 25 to 30 seconds with Excel 2003. In both case the same function is quite immediat if we settle the calculation mode to manual... The same phenomenon can be seen if you ask for the group function on a file without any formulas while another big excel file with a lot of formulas is open at the same time but not active... Is there something different than the calculation mode that should be settled in Excel 2003 in order to get the same response time on group function than in Excel 200? Any other information, experience about that problem? Is it link to a different behavior of excel in front of calculation strategy? Thanks for your help Alain79  Dave Peterson  Dave Peterson 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
trying to open an excel file in excel 2003  Excel Discussion (Misc queries)  
Why is eveything so slow after upgrading from Excel 2000 to 2003?  Excel Discussion (Misc queries)  
Open file Slow on Excel 2003  Excel Discussion (Misc queries)  
Can't group pivot table items by month in Excel  Excel Discussion (Misc queries)  
sharing/using/saving Excel 2002 files in Excel 2003  Excel Discussion (Misc queries) 