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

nfortunately 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" < XXXX@XXXXX.COM > wrote in message

news: XXXX@XXXXX.COM ...

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" < XXXX@XXXXX.COM > wrote in message

news: XXXX@XXXXX.COM ...

hanks, Charles, for the reassurance!

"Charles Williams" wrote:

"Charles Williams" wrote:

1. please please I Need A Code For This Question Very Important

2. stupid, simple question on using accuracy / estimated error value

3. Floppy driver DMA question - PLEASE answer me, it's important for fixing

4. Visio Activex Failing when launching using thread - MVP Please - Very important

5. Important question - please answer

6. Important Linux question, please help me

7. Hey. one important question please....

8. 2 important questions, please answer me

9. How does NTP calculate peer accuracy?

10. Please help - Important design question

11. 2 very important WMP11 questions. Please click.

12. Visio Activex Failing when launching using thread - MVP Please - Very important

13. VERY Important QUESTION!!!!!!!!!!!!!!!!!!!PLEASE!!!

14. PLEASE HELP -- IMPORTANT - using the WPM to monitor threads

15. How to calculate accuracy of matrix inversion

3 post • Page:**1** of **1**