Ensure Automatic Calculation

    Hi there


    I have largish workbooks (10MB) with a variety of formulas and lookups to generate tables and charts. Calculation is ALWAYS set to Automatic, but sometimes (not always) the formulas fail to update when values are changed. Sometimes F9 will force calculation, sometimes Ctrl + Alt + F9, sometimes (especially with charts) I have to close the workbook and reopen before they will update. The workbooks contain macros but none are running when this happens.


    A search of your forum indicated that this question has arisen several times before, but I haven't seen a definitive answer. Has anybody come up with possible solutions?


    Any help appreciated.

    Re: Ensure Automatic Calculation


    I guess you could just add a macro that throws the Calculate option, maybe even doing it on every sheet of the worksheet... But that shouldn't be necessary...


    Added by admin


    Right click on the Excel icon, top left next to File and choose View Code. In here copy/paste;

    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Application.Calculation = xlCalculationAutomatic
End Sub

    Re: Ensure Automatic Calculation


    Hey there,


    Not sure if this makes a difference to you, but the calculation mode is an Excel level setting NOT a workbook level setting, and it is set by the first workbook opened in an Excel session. That is, if you open a workbook whose calculation mode is set to manual, then open your other workbook even if its calculation is technically set to automatic, the calculation in the second workbook will revert back to manual.


    You may also want to use Shift/Ctrl/Alt/F9 which will rebuild dependencies and do a full calculate.


    Cheers,
    Averil

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

    Re: Ensure Automatic Calculation


    Hi Guys


    Thank you for those helpful replies. I'm sorry for my delay in responding; I have been following them up.


    1. Sorry, Admin, your code doesn't seem to work.


    2. All my workbooks are always set to automatic calculation, so whichever is opened first should not affect the operation.


    3. What I have found is:
    Sheets contain summary tables in which the cell values depend on lookup formulas on the same or a different sheet. Likewise the lookup value may be on the same or a different sheet.
    The problems arise when sheets containing some of the summary tables are copied and detached. After this has been done, the only formulas that update automatically are those on the same sheet as the lookup value.
    You can force calculation once by Ctrl + Alt + F9 (if you then change the lookup values again, calculation is again frozen). But if you calculate with Shift + Ctrl + Alt + F9, automatic calculation then continues when you change the lookup value repeatedly.
    If you close the file after calculation and reopen it, calculation proceeds automatically until you again start detaching sheets.
    But if you close the file without calculating and reopen it, it is still uncalculated. Even if you shut down and restart the computer, it is still uncalculated until you force calculation.


    Any further help appeciated.

    Re: Ensure Automatic Calculation


    Thanks again for your helpful replies.


    1. Reafidy. Some of the formulas involved are actually very simple, and I now think they are not part of the problem (see below).


    2. Admin. I hesitate to contradict those who are far more expert in Excel/VBA than I am, but - I'm sorry, in the particular circumstances of my particular workbooks, your code does not seem to work.


    One useful thing about these threads is that even if they do not provide an immediate answer to your problem, they stimulate you into further investigation. I have now done this and narrowed down the problem.


    Because it's a slightly differently defined problem from that with which we started, and because I've previously been reprimanded for changing the subject, I will start a new thread.



    Regards

    Re: Ensure Automatic Calculation


    Hi,

    I am having exactly the same problem using VLOOKUP functions etc. I can confirm also that the little piece of code doesn't work in this scenario. I understand this is quite an old thread however can anyone direct me to the new one which was started? Alternatively perhaps a thread which provides a solution!!

    Cheers,
    Harry

    Re: Ensure Automatic Calculation


    Harry,


    [COLOR="navy"]Please read the Forum Rules![/COLOR] See link in my signature for quick access.


    Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking. Posting solutions is acceptable.
    ALWAYS start a new thread for your question and if you think it is helpful to clarify your thread you may include a link back this thread (or any other).


    Start a new thread for your question and be sure your thread title follows these guidelines:


    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words.


    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login