I have a large Excel 2003 workbook with multiple sheets and formula
interdependencies. When my code runs I change the calc status to manual and
then use 'Calculate' (not ActiveSheet.Calculate or
ActiveSheet.UsedRange.Calculate for example, just 'Calculate') at various
points during the process. However, after coming across Microsoft's KB919127
(see below for partial excerpt), am now concerned if this can give me
inaccurate results. Is 'Calculate' accurate or must I use
UsedRange.Calculate; if so, I thought there are issues with applying the
UsedRange method also (in terms of correctly selecting the used range). Can
someone advise me please?

KB919127:
Calculations may not occur in an Excel workbook that has many formulas when
you use the Calculate method to calculate formulas or after you press
SHIFT+F9 to calculate formulas.

SYMPTOMS
When you calculate formulas in a workbook that contains many formulas in
Microsoft Excel 2002 or in Microsoft Office Excel 2003, the calculations may
not occur. This issue may occur when either of the following conditions is
met:
o You use the Calculate method in a Microsoft Visual Basic for Applications
(VBA) macro to calculate formulas.
o You press F9 to calculate formulas in all open workbooks that have
changed since the last calculation. Then, you press SHIFT+F9 to calculate
formulas in the active worksheet that have changed since the last
calculation. When you press F9 again to calculate formulas in all open
workbooks, the formulas in all open workbooks are not calculated.

This issue may occur when either of the following conditions is true:
WORKAROUND
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited to,
the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to
create and to debug procedures. Microsoft support engineers can help explain
the functionality of a particular procedure, but they will not modify these
examples to provide added functionality or construct procedures to meet your
specific requirements. To work around this problem, use the UsedRange
property when you calculate formulas. To do this, use one of the following
methods depending on how you calculate formulas.

You use the Calculate method in a VBA macro to calculate formulas
If you use the Calculate method in a VBA macro to calculate the formulas in
a workbook, use the UsedRange property.

For example, you use one of the following Calculate methods:
 ActiveSheet.Calculate
 Worksheets("Sheet1").Calculate

Instead, use the UsedRange property as follows:
 ActiveSheet.UsedRange.Calculate
 Worksheets("Sheet1").UsedRange.Calculate

Unfortunately Article KB919127 is badly written, confusing and unhelpful.

The Title talks about Calculate, but the body of the article talks about
Worksheet.Calculate, and further information refers you to an article that
has nothing whatsoever to do with calculation!

There is no statement that Microsoft has confirmed this to be a problem with
Excel 2002 and 2003 (2007?)

Whilst it is impossible to know exactly how inaccurate the article is, or
what circumstances cause the described problem, or indeed what the problem
really is, my best guess is as follows:

- Sounds like there may be a problem with Worksheet.Calculate rather than
Calculate.

- I have used Worksheet.Calculate on many extremely complex workbooks
without meeting the described problem: if the described problem does indeed
exist it is probably very rare.

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

"Paige" wrote in message
...
I have a large Excel 2003 workbook with multiple sheets and formula
interdependencies. When my code runs I change the calc status to manual
and
then use 'Calculate' (not ActiveSheet.Calculate or
ActiveSheet.UsedRange.Calculate for example, just 'Calculate') at various
points during the process. However, after coming across Microsoft's
KB919127
(see below for partial excerpt), am now concerned if this can give me
inaccurate results. Is 'Calculate' accurate or must I use
UsedRange.Calculate; if so, I thought there are issues with applying the
UsedRange method also (in terms of correctly selecting the used range).
Can
someone advise me please?

KB919127:
Calculations may not occur in an Excel workbook that has many formulas
when
you use the Calculate method to calculate formulas or after you press
SHIFT+F9 to calculate formulas.

SYMPTOMS
When you calculate formulas in a workbook that contains many formulas in
Microsoft Excel 2002 or in Microsoft Office Excel 2003, the calculations
may
not occur. This issue may occur when either of the following conditions
is
met:
o You use the Calculate method in a Microsoft Visual Basic for
Applications
(VBA) macro to calculate formulas.
o You press F9 to calculate formulas in all open workbooks that have
changed since the last calculation. Then, you press SHIFT+F9 to calculate
formulas in the active worksheet that have changed since the last
calculation. When you press F9 again to calculate formulas in all open
workbooks, the formulas in all open workbooks are not calculated.

This issue may occur when either of the following conditions is true:
WORKAROUND
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited
to,
the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to
create and to debug procedures. Microsoft support engineers can help
explain
the functionality of a particular procedure, but they will not modify
these
examples to provide added functionality or construct procedures to meet
your
specific requirements. To work around this problem, use the UsedRange
property when you calculate formulas. To do this, use one of the following
methods depending on how you calculate formulas.

You use the Calculate method in a VBA macro to calculate formulas
If you use the Calculate method in a VBA macro to calculate the formulas
in
a workbook, use the UsedRange property.

For example, you use one of the following Calculate methods:
? ActiveSheet.Calculate
? Worksheets("Sheet1").Calculate

Instead, use the UsedRange property as follows:
? ActiveSheet.UsedRange.Calculate
? Worksheets("Sheet1").UsedRange.Calculate

Thanks, Charles, for the reassurance!

"Charles Williams" wrote:

Unfortunately Article KB919127 is badly written, confusing and unhelpful.

The Title talks about Calculate, but the body of the article talks about
Worksheet.Calculate, and further information refers you to an article that
has nothing whatsoever to do with calculation!

There is no statement that Microsoft has confirmed this to be a problem with
Excel 2002 and 2003 (2007?)

Whilst it is impossible to know exactly how inaccurate the article is, or
what circumstances cause the described problem, or indeed what the problem
really is, my best guess is as follows:

- Sounds like there may be a problem with Worksheet.Calculate rather than
Calculate.

- I have used Worksheet.Calculate on many extremely complex workbooks
without meeting the described problem: if the described problem does indeed
exist it is probably very rare.

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

"Paige" wrote in message
...
I have a large Excel 2003 workbook with multiple sheets and formula
interdependencies. When my code runs I change the calc status to manual
and
then use 'Calculate' (not ActiveSheet.Calculate or
ActiveSheet.UsedRange.Calculate for example, just 'Calculate') at various
points during the process. However, after coming across Microsoft's
KB919127
(see below for partial excerpt), am now concerned if this can give me
inaccurate results. Is 'Calculate' accurate or must I use
UsedRange.Calculate; if so, I thought there are issues with applying the
UsedRange method also (in terms of correctly selecting the used range).
Can
someone advise me please?

KB919127:
Calculations may not occur in an Excel workbook that has many formulas
when
you use the Calculate method to calculate formulas or after you press
SHIFT+F9 to calculate formulas.

SYMPTOMS
When you calculate formulas in a workbook that contains many formulas in
Microsoft Excel 2002 or in Microsoft Office Excel 2003, the calculations
may
not occur. This issue may occur when either of the following conditions
is
met:
o You use the Calculate method in a Microsoft Visual Basic for
Applications
(VBA) macro to calculate formulas.
o You press F9 to calculate formulas in all open workbooks that have
changed since the last calculation. Then, you press SHIFT+F9 to calculate
formulas in the active worksheet that have changed since the last
calculation. When you press F9 again to calculate formulas in all open
workbooks, the formulas in all open workbooks are not calculated.

This issue may occur when either of the following conditions is true:
WORKAROUND
Microsoft provides programming examples for illustration only, without
warranty either expressed or implied. This includes, but is not limited
to,
the implied warranties of merchantability or fitness for a particular
purpose. This article assumes that you are familiar with the programming
language that is being demonstrated and with the tools that are used to
create and to debug procedures. Microsoft support engineers can help
explain
the functionality of a particular procedure, but they will not modify
these
examples to provide added functionality or construct procedures to meet
your
specific requirements. To work around this problem, use the UsedRange
property when you calculate formulas. To do this, use one of the following
methods depending on how you calculate formulas.

You use the Calculate method in a VBA macro to calculate formulas
If you use the Calculate method in a VBA macro to calculate the formulas
in
a workbook, use the UsedRange property.

For example, you use one of the following Calculate methods:
? ActiveSheet.Calculate
? Worksheets("Sheet1").Calculate

Instead, use the UsedRange property as follows:
? ActiveSheet.UsedRange.Calculate
? Worksheets("Sheet1").UsedRange.Calculate