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

Discussion in 'Microsoft Excel Programming' started by Guest, Jan 19, 2005.

  1. Guest

    Guest Guest

    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 ??).
     
    Guest, Jan 19, 2005
    #1
    1. Advertisements

  2. 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
     
    Fredrik Wahlgren, Jan 19, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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.
     
    Guest, Jan 20, 2005
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.