"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

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.





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

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
>
>
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> >
> >
> >
> > 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> > >
> > >
> > >
> > > 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