Time needed for the group function: big diff between Excel2003 and Excel2000

[Alain79]

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

[keepITcool]

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 :

[keepITcool]

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 :

[Alain79]

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" <xrrcv...@puryyb.ay> wrote in message
news:xn0e3g7r1fpevhz...@msnews.microsoft.com...

[keepITcool]

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

[Alain79]

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" <xrrcv...@puryyb.ay> wrote in message

news:xn0e3gcqjfw75h2...@msnews.microsoft.com...

[keepITcool]

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.

[Alain79]

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" <xrrcv...@puryyb.ay> wrote in message

news:xn0e3goudgckgt7...@msnews.microsoft.com...