Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
HI
We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
Alan
see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
hmm.. i was thinking check settings for display pagebreaks in both versions. visible pagebreaks can cause significant delays -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Alan see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
FYI - Here is what suggested Dave Peterson on the same question placed in
microsoft.public.excel.misc newsgroup Thanks to comment if possible If true, I am not that happy and should find if it is possible to intercept the grouping command... = Start of Dave suggestion... 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 (data|filter|autofilter 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 after--or 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. = End of Dave suggestion "keepITcool" wrote in message ft.com... hmm.. i was thinking check settings for display pagebreaks in both versions. visible pagebreaks can cause significant delays -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Alan see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
hmm
changing the outlinelevel doesnot trigger a recalc.... but indeed in xl2003 changing SHOWLEVELS does trigger a recalc. (does not in xl97/xl2002) looks like your only recourse is to turnoff autocalculation Sub x() Dim lngCalc& With Application lngCalc = .Calculation .EnableEvents = False End With 'Prep sheet Me.UsedRange.EntireRow.Delete [a1:a19].Value = 1 [a20].FormulaR1C1 = "=subtotal(9,r1c:r19c)" Me.Calculate 'Test calc MANUAL Application.Calculation = xlCalculationManual Application.EnableEvents = True 'Set the level Debug.Print "Set levels MANUAL" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels MANUAL" Me.Outline.ShowLevels 1 'now test AUTO With Application .EnableEvents = False .Calculation = xlCalculationAutomatic .EnableEvents = True End With 'Set the level Debug.Print "Set levels AUTO" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels AUTO" 'TRIGGERS RECALC in xl2003 Me.Outline.ShowLevels 1 Debug.Print "Done" Application.Calculation = lngCalc End Sub Private Sub Worksheet_Calculate() Debug.Print "CALC!" End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : FYI - Here is what suggested Dave Peterson on the same question placed in microsoft.public.excel.misc newsgroup Thanks to comment if possible If true, I am not that happy and should find if it is possible to intercept the grouping command... = Start of Dave suggestion... 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 (data|filter|autofilter 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 after--or 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. = End of Dave suggestion "keepITcool" wrote in message ft.com... hmm.. i was thinking check settings for display pagebreaks in both versions. visible pagebreaks can cause significant delays -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Alan see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
Thanks
I have now writen my own show outline level program One question about one piece of your code that is: With Application lngCalc = .Calculation .EnableEvents = False End With What is the role of "Application.EnableEvents = False" Alain 79 "keepITcool" wrote in message ft.com... hmm changing the outlinelevel doesnot trigger a recalc.... but indeed in xl2003 changing SHOWLEVELS does trigger a recalc. (does not in xl97/xl2002) looks like your only recourse is to turnoff autocalculation Sub x() Dim lngCalc& With Application lngCalc = .Calculation .EnableEvents = False End With 'Prep sheet Me.UsedRange.EntireRow.Delete [a1:a19].Value = 1 [a20].FormulaR1C1 = "=subtotal(9,r1c:r19c)" Me.Calculate 'Test calc MANUAL Application.Calculation = xlCalculationManual Application.EnableEvents = True 'Set the level Debug.Print "Set levels MANUAL" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels MANUAL" Me.Outline.ShowLevels 1 'now test AUTO With Application .EnableEvents = False .Calculation = xlCalculationAutomatic .EnableEvents = True End With 'Set the level Debug.Print "Set levels AUTO" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels AUTO" 'TRIGGERS RECALC in xl2003 Me.Outline.ShowLevels 1 Debug.Print "Done" Application.Calculation = lngCalc End Sub Private Sub Worksheet_Calculate() Debug.Print "CALC!" End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : FYI - Here is what suggested Dave Peterson on the same question placed in microsoft.public.excel.misc newsgroup Thanks to comment if possible If true, I am not that happy and should find if it is possible to intercept the grouping command... = Start of Dave suggestion... 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 (data|filter|autofilter 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 after--or 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. = End of Dave suggestion "keepITcool" wrote in message ft.com... hmm.. i was thinking check settings for display pagebreaks in both versions. visible pagebreaks can cause significant delays -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Alan see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
it was only needed to demonstrate that the calculation event occured when setting ShowLevels and to suppress the debug line when adding data or changing calculation mode. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : Thanks I have now writen my own show outline level program One question about one piece of your code that is: With Application lngCalc = .Calculation .EnableEvents = False End With What is the role of "Application.EnableEvents = False" Alain 79 "keepITcool" wrote in message ft.com... hmm changing the outlinelevel doesnot trigger a recalc.... but indeed in xl2003 changing SHOWLEVELS does trigger a recalc. (does not in xl97/xl2002) looks like your only recourse is to turnoff autocalculation Sub x() Dim lngCalc& With Application lngCalc = .Calculation .EnableEvents = False End With 'Prep sheet Me.UsedRange.EntireRow.Delete [a1:a19].Value = 1 [a20].FormulaR1C1 = "=subtotal(9,r1c:r19c)" Me.Calculate 'Test calc MANUAL Application.Calculation = xlCalculationManual Application.EnableEvents = True 'Set the level Debug.Print "Set levels MANUAL" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels MANUAL" Me.Outline.ShowLevels 1 'now test AUTO With Application .EnableEvents = False .Calculation = xlCalculationAutomatic .EnableEvents = True End With 'Set the level Debug.Print "Set levels AUTO" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels AUTO" 'TRIGGERS RECALC in xl2003 Me.Outline.ShowLevels 1 Debug.Print "Done" Application.Calculation = lngCalc End Sub Private Sub Worksheet_Calculate() Debug.Print "CALC!" End Sub -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : FYI - Here is what suggested Dave Peterson on the same question placed in microsoft.public.excel.misc newsgroup Thanks to comment if possible If true, I am not that happy and should find if it is possible to intercept the grouping command... = Start of Dave suggestion... 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 (data|filter|autofilter 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 after--or 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. = End of Dave suggestion "keepITcool" wrote in message ft.com... hmm.. i was thinking check settings for display pagebreaks in both versions. visible pagebreaks can cause significant delays -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Alan see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time needed for the group function: big diff between Excel2003 and Excel2000
I have finaly added some more commands to my application
Those commands 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 "keepITcool" wrote in message ft.com... it was only needed to demonstrate that the calculation event occured when setting ShowLevels and to suppress the debug line when adding data or changing calculation mode. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : Thanks I have now writen my own show outline level program One question about one piece of your code that is: With Application lngCalc = .Calculation .EnableEvents = False End With What is the role of "Application.EnableEvents = False" Alain 79 "keepITcool" wrote in message ft.com... hmm changing the outlinelevel doesnot trigger a recalc.... but indeed in xl2003 changing SHOWLEVELS does trigger a recalc. (does not in xl97/xl2002) looks like your only recourse is to turnoff autocalculation Sub x() Dim lngCalc& With Application lngCalc = .Calculation .EnableEvents = False End With 'Prep sheet Me.UsedRange.EntireRow.Delete [a1:a19].Value = 1 [a20].FormulaR1C1 = "=subtotal(9,r1c:r19c)" Me.Calculate 'Test calc MANUAL Application.Calculation = xlCalculationManual Application.EnableEvents = True 'Set the level Debug.Print "Set levels MANUAL" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels MANUAL" Me.Outline.ShowLevels 1 'now test AUTO With Application .EnableEvents = False .Calculation = xlCalculationAutomatic .EnableEvents = True End With 'Set the level Debug.Print "Set levels AUTO" [a1:a19].EntireRow.OutlineLevel = 2 'Change display Debug.Print "Show levels AUTO" 'TRIGGERS RECALC in xl2003 Me.Outline.ShowLevels 1 Debug.Print "Done" Application.Calculation = lngCalc End Sub Private Sub Worksheet_Calculate() Debug.Print "CALC!" End Sub -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : FYI - Here is what suggested Dave Peterson on the same question placed in microsoft.public.excel.misc newsgroup Thanks to comment if possible If true, I am not that happy and should find if it is possible to intercept the grouping command... = Start of Dave suggestion... 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 (data|filter|autofilter 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 after--or 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. = End of Dave suggestion "keepITcool" wrote in message ft.com... hmm.. i was thinking check settings for display pagebreaks in both versions. visible pagebreaks can cause significant delays -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Alan see http://www.decisionmodels.com/calcsecretsc.htm for the ins and outs of excel calculation. and the changes between versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Alain79 wrote : HI We are experimenting a consistant difference in real time behavior between Excel 2003 behaviour and Excel 2000 behaviour in front of 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening .csv file in Excel2003 vs Excel2000 | Excel Discussion (Misc queries) | |||
How can I use autosave from excel2000 in excel2003 | Excel Worksheet Functions | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
excel2000 owc doesn't work on excel2003 ! | Excel Discussion (Misc queries) | |||
Can't open Excel2003 workbook containing VBA macros with Excel2000. | Excel Programming |