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

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

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

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

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

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

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

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