"Application.Calculate" does not always update/recalculate the for

In writing VBA code for an Excel Application, the following problem has
occurred:
The command, "Application.Calculate", does not always update or recalculate
the formulas in the cells of the workbook.

To compound this problem the users of the Excel Application are using
several different versions of Excel and VBA. VBA code will need to check
the version of Excel and VBA before executing a command (i.e. using
Application.Version and Application.VBE.Version)

Present documentation shows the following:
* F9 - recalculates all of the data in the open workbooks
(Application.Calculate)
* Shift+F9 - only calculates data in the specified worksheet
(ActiveSheet.Calculate)
* Ctrl+Alt+F9 - Forces a full calculation of the data in all of the open
workbooks (Application.CalculateFull)
* Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of the
open workbooks after checking the dependencies between formulas
(Application.CalculateFullRebuild)

The following information is needed:
* What is the best way to handle this issue over the various versions of
Excel (backward compatibility)?
* What version of Excel and VBA correspond to the previous commands (i.e.
Application.CalculateFullRebuild was introduced in Excel 2003 or Excel
Version 11, VBA Version ??).

"John K" <[email protected]> wrote in message
news:[email protected]...
>
> In writing VBA code for an Excel Application, the following problem has
> occurred:
> The command, "Application.Calculate", does not always update or
recalculate
> the formulas in the cells of the workbook.
>
> To compound this problem the users of the Excel Application are using
> several different versions of Excel and VBA. VBA code will need to check
> the version of Excel and VBA before executing a command (i.e. using
> Application.Version and Application.VBE.Version)
>
> Present documentation shows the following:
> * F9 - recalculates all of the data in the open workbooks
> (Application.Calculate)
> * Shift+F9 - only calculates data in the specified worksheet
> (ActiveSheet.Calculate)
> * Ctrl+Alt+F9 - Forces a full calculation of the data in all of the open
> workbooks (Application.CalculateFull)
> * Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of the
> open workbooks after checking the dependencies between formulas
> (Application.CalculateFullRebuild)
>
> The following information is needed:
> * What is the best way to handle this issue over the various versions of
> Excel (backward compatibility)?
> * What version of Excel and VBA correspond to the previous commands (i.e.
> Application.CalculateFullRebuild was introduced in Excel 2003 or Excel
> Version 11, VBA Version ??).
>

You have to check the version prior to deciding what method to call. There
are may ways to force Excel top recalculate a worksheet. The best options
are of course Application.CalculateFull and
Application.CalculateFullrebuild.

If you use Excel 2000 there's another way

Dim oSht as worksheet
Application.Calculation=xlCalculationManual
for each oSht in Worksheets
oSht.enablecalculation=false
osht.enablecalculation=true
next osht

Application.calculate

Read more here: http://www.decisionmodels.com/calcsecretsh.htm

/Fredrik

The following code was used for the trouble worksheet:
'--EnableCalculation must be unset and then set
ThisWorkbook.Worksheets("MySheet").EnableCalculation = False
ThisWorkbook.Worksheets("MySheet").EnableCalculation = True
ThisWorkbook.Worksheets("MySheet").Calculate

This was similar to the loop you describe in the code:
Dim oSht as worksheet
Application.Calculation=xlCalculationManual
for each oSht in Worksheets
oSht.enablecalculation=false
osht.enablecalculation=true
next osht
Application.calculate

Unfortunately, this method did not work. Any other ideas?

Question: Is there a document on the Microsoft site that will link the
version of Excel and the VBE with the following VBA commands:
Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild

Thank you.



"Fredrik Wahlgren" wrote:

>
> "John K" <[email protected]> wrote in message
> news:[email protected]...
> >
> > In writing VBA code for an Excel Application, the following problem has
> > occurred:
> > The command, "Application.Calculate", does not always update or
> recalculate
> > the formulas in the cells of the workbook.
> >
> > To compound this problem the users of the Excel Application are using
> > several different versions of Excel and VBA. VBA code will need to check
> > the version of Excel and VBA before executing a command (i.e. using
> > Application.Version and Application.VBE.Version)
> >
> > Present documentation shows the following:
> > * F9 - recalculates all of the data in the open workbooks
> > (Application.Calculate)
> > * Shift+F9 - only calculates data in the specified worksheet
> > (ActiveSheet.Calculate)
> > * Ctrl+Alt+F9 - Forces a full calculation of the data in all of the open
> > workbooks (Application.CalculateFull)
> > * Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of the
> > open workbooks after checking the dependencies between formulas
> > (Application.CalculateFullRebuild)
> >
> > The following information is needed:
> > * What is the best way to handle this issue over the various versions of
> > Excel (backward compatibility)?
> > * What version of Excel and VBA correspond to the previous commands (i.e.
> > Application.CalculateFullRebuild was introduced in Excel 2003 or Excel
> > Version 11, VBA Version ??).
> >
>
> You have to check the version prior to deciding what method to call. There
> are may ways to force Excel top recalculate a worksheet. The best options
> are of course Application.CalculateFull and
> Application.CalculateFullrebuild.
>
> If you use Excel 2000 there's another way
>
> Dim oSht as worksheet
> Application.Calculation=xlCalculationManual
> for each oSht in Worksheets
> oSht.enablecalculation=false
> osht.enablecalculation=true
> next osht
>
> Application.calculate
>
> Read more here: http://www.decisionmodels.com/calcsecretsh.htm
>
> /Fredrik
>
>
>