Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc from a
database. I have disabled auto calculation when opening the workbook as I
wish to control the order in which the workbook is calculated, which is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then close
it. My problem is that after the sort has been completed, Excel considers
that the workbook needs calculating again, even though I do not need it to.
When the user saves the workbook, as the calculation state is now pending,
Excel now calculates (which takes some considerable time) before saving. Can
I override this pending state in some way? If not, what other possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.

Application.CalculateBeforeSave=False

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc from a
database. I have disabled auto calculation when opening the workbook as I
wish to control the order in which the workbook is calculated, which is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then close
it. My problem is that after the sort has been completed, Excel considers
that the workbook needs calculating again, even though I do not need it
to.
When the user saves the workbook, as the calculation state is now pending,
Excel now calculates (which takes some considerable time) before saving.
Can
I override this pending state in some way? If not, what other possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.

Charles

Many thanks for replying. I had seen this option but was a little wary of
using it. I guess that you are suggesting that I use it in the workbook_open
event. When the workbook opens I currently store the user's
Application.Calculation setting. If it is automatic then I also change it to
manual. In the workbook_close event I am then setting
Application.Calculation setting back to the value store during the
workbook_open event. This way I am not affecting the user's underlying
wishes about the calculation mode. Can I do something similar with the
CalculateBeforeSave setting? If so, in which event would I set it back to
it's original setting? Is it Before_Close? Would setting it back at this
point not initial a calculation?

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Application.CalculateBeforeSave=False

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc from a
database. I have disabled auto calculation when opening the workbook as I
wish to control the order in which the workbook is calculated, which is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then close
it. My problem is that after the sort has been completed, Excel considers
that the workbook needs calculating again, even though I do not need it
to.
When the user saves the workbook, as the calculation state is now pending,
Excel now calculates (which takes some considerable time) before saving.
Can
I override this pending state in some way? If not, what other possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.

Note that calculating sheet by sheet without a final recalc may NOT get the
correct answer if there are any references from one sheet to another: if the
user has any ability to add their own formula this can cause problems.

Changing the calculation mode from Manual to Automatic will initiate a
recalculation.
Saving a workbook with CalculateBeforeSave=true will also initiate a
recalculation.

In other words if the user wants Automatic Calc or Calc before save then
restoring the users settings will trigger a recalc:
so you can either restore the users settings and suffer the recalc time or
not restore them and avoid the recalc time.

What is causing the slow recalc time? Can you do anything about that?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Charles

Many thanks for replying. I had seen this option but was a little wary of
using it. I guess that you are suggesting that I use it in the
workbook_open
event. When the workbook opens I currently store the user's
Application.Calculation setting. If it is automatic then I also change it
to
manual. In the workbook_close event I am then setting
Application.Calculation setting back to the value store during the
workbook_open event. This way I am not affecting the user's underlying
wishes about the calculation mode. Can I do something similar with the
CalculateBeforeSave setting? If so, in which event would I set it back to
it's original setting? Is it Before_Close? Would setting it back at this
point not initial a calculation?

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Application.CalculateBeforeSave=False

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc from
a
database. I have disabled auto calculation when opening the workbook
as I
wish to control the order in which the workbook is calculated, which
is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then
close
it. My problem is that after the sort has been completed, Excel
considers
that the workbook needs calculating again, even though I do not need it
to.
When the user saves the workbook, as the calculation state is now
pending,
Excel now calculates (which takes some considerable time) before
saving.
Can
I override this pending state in some way? If not, what other possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.

Charles

Thanks for the very clear explanations. The slow recalc is caused by my
custom function (non volatile) which accesses a database for the results. I
use it to calculate some results, then sort these results, which causes the
calculation to be invoked again.

As I am unable to prevent this second recalculation, I think that I may try
collating my database results in one master worksheet. I could then use
vlookups to put these results into groups on the individual worksheets, and
sort these rather than sorting my custom function cells. This should halve
the time it takes (I hope!)

Many thanks.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Note that calculating sheet by sheet without a final recalc may NOT get the
correct answer if there are any references from one sheet to another: if the
user has any ability to add their own formula this can cause problems.

Changing the calculation mode from Manual to Automatic will initiate a
recalculation.
Saving a workbook with CalculateBeforeSave=true will also initiate a
recalculation.

In other words if the user wants Automatic Calc or Calc before save then
restoring the users settings will trigger a recalc:
so you can either restore the users settings and suffer the recalc time or
not restore them and avoid the recalc time.

What is causing the slow recalc time? Can you do anything about that?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Charles

Many thanks for replying. I had seen this option but was a little wary of
using it. I guess that you are suggesting that I use it in the
workbook_open
event. When the workbook opens I currently store the user's
Application.Calculation setting. If it is automatic then I also change it
to
manual. In the workbook_close event I am then setting
Application.Calculation setting back to the value store during the
workbook_open event. This way I am not affecting the user's underlying
wishes about the calculation mode. Can I do something similar with the
CalculateBeforeSave setting? If so, in which event would I set it back to
it's original setting? Is it Before_Close? Would setting it back at this
point not initial a calculation?

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Application.CalculateBeforeSave=False

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc from
a
database. I have disabled auto calculation when opening the workbook
as I
wish to control the order in which the workbook is calculated, which
is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then
close
it. My problem is that after the sort has been completed, Excel
considers
that the workbook needs calculating again, even though I do not need it
to.
When the user saves the workbook, as the calculation state is now
pending,
Excel now calculates (which takes some considerable time) before
saving.
Can
I override this pending state in some way? If not, what other possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.

Would it be feasible to make your custom function get all the results for a
sheet and return a sorted array of results?
(the custom function would be array-entered into multiple cells, and would
either use quicksort or do the sort in the database query)

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Charles

Thanks for the very clear explanations. The slow recalc is caused by my
custom function (non volatile) which accesses a database for the results.
I
use it to calculate some results, then sort these results, which causes
the
calculation to be invoked again.

As I am unable to prevent this second recalculation, I think that I may
try
collating my database results in one master worksheet. I could then use
vlookups to put these results into groups on the individual worksheets,
and
sort these rather than sorting my custom function cells. This should
halve
the time it takes (I hope!)

Many thanks.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Note that calculating sheet by sheet without a final recalc may NOT get
the
correct answer if there are any references from one sheet to another: if
the
user has any ability to add their own formula this can cause problems.

Changing the calculation mode from Manual to Automatic will initiate a
recalculation.
Saving a workbook with CalculateBeforeSave=true will also initiate a
recalculation.

In other words if the user wants Automatic Calc or Calc before save then
restoring the users settings will trigger a recalc:
so you can either restore the users settings and suffer the recalc time
or
not restore them and avoid the recalc time.

What is causing the slow recalc time? Can you do anything about that?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Charles

Many thanks for replying. I had seen this option but was a little wary
of
using it. I guess that you are suggesting that I use it in the
workbook_open
event. When the workbook opens I currently store the user's
Application.Calculation setting. If it is automatic then I also change
it
to
manual. In the workbook_close event I am then setting
Application.Calculation setting back to the value store during the
workbook_open event. This way I am not affecting the user's underlying
wishes about the calculation mode. Can I do something similar with the
CalculateBeforeSave setting? If so, in which event would I set it back
to
it's original setting? Is it Before_Close? Would setting it back at
this
point not initial a calculation?

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Application.CalculateBeforeSave=False

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in
message
...
Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc
from
a
database. I have disabled auto calculation when opening the
workbook
as I
wish to control the order in which the workbook is calculated, which
is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then
close
it. My problem is that after the sort has been completed, Excel
considers
that the workbook needs calculating again, even though I do not need
it
to.
When the user saves the workbook, as the calculation state is now
pending,
Excel now calculates (which takes some considerable time) before
saving.
Can
I override this pending state in some way? If not, what other
possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.

Charles

This is more-or-less what I am doing now. It has reduced the time by about
half, so I am happy enough with that. Many thanks.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Would it be feasible to make your custom function get all the results for a
sheet and return a sorted array of results?
(the custom function would be array-entered into multiple cells, and would
either use quicksort or do the sort in the database query)

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Charles

Thanks for the very clear explanations. The slow recalc is caused by my
custom function (non volatile) which accesses a database for the results.
I
use it to calculate some results, then sort these results, which causes
the
calculation to be invoked again.

As I am unable to prevent this second recalculation, I think that I may
try
collating my database results in one master worksheet. I could then use
vlookups to put these results into groups on the individual worksheets,
and
sort these rather than sorting my custom function cells. This should
halve
the time it takes (I hope!)

Many thanks.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Note that calculating sheet by sheet without a final recalc may NOT get
the
correct answer if there are any references from one sheet to another: if
the
user has any ability to add their own formula this can cause problems.

Changing the calculation mode from Manual to Automatic will initiate a
recalculation.
Saving a workbook with CalculateBeforeSave=true will also initiate a
recalculation.

In other words if the user wants Automatic Calc or Calc before save then
restoring the users settings will trigger a recalc:
so you can either restore the users settings and suffer the recalc time
or
not restore them and avoid the recalc time.

What is causing the slow recalc time? Can you do anything about that?

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in message
...
Charles

Many thanks for replying. I had seen this option but was a little wary
of
using it. I guess that you are suggesting that I use it in the
workbook_open
event. When the workbook opens I currently store the user's
Application.Calculation setting. If it is automatic then I also change
it
to
manual. In the workbook_close event I am then setting
Application.Calculation setting back to the value store during the
workbook_open event. This way I am not affecting the user's underlying
wishes about the calculation mode. Can I do something similar with the
CalculateBeforeSave setting? If so, in which event would I set it back
to
it's original setting? Is it Before_Close? Would setting it back at
this
point not initial a calculation?

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Charles Williams" wrote:

Application.CalculateBeforeSave=False

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Alan Moseley" wrote in
message
...
Hi all. Hope someone can help.

I have a workbook with custom functions which fetch data via odbc
from
a
database. I have disabled auto calculation when opening the
workbook
as I
wish to control the order in which the workbook is calculated, which
is:-

1. Calulate worksheet 1
2. Sort the data based upon the result of some calculations
3. Calculate worksheet 2
4. Sort the data based upon the result of some calculations
5. And so on until the last worksheet is processed.

The user would then print as necessary, save the workbook, and then
close
it. My problem is that after the sort has been completed, Excel
considers
that the workbook needs calculating again, even though I do not need
it
to.
When the user saves the workbook, as the calculation state is now
pending,
Excel now calculates (which takes some considerable time) before
saving.
Can
I override this pending state in some way? If not, what other
possible
solutions are available to me?

Thanks in advance. A gold star for the best answer!

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.