+ Reply to Thread
Results 1 to 3 of 3

Question about confusing volatility issue

  1. #1
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Question about confusing volatility issue

    I'm trying to improve the performance of my workbooks. I'm doing some testing on volatility on 1 sheet at the moment.
    I'm using XL2002.
    I have Application.Calculation set to Manual.
    I have all the worksheets in all the workbooks .EnableCalculate=False except 1 worksheet that I'm testing.

    I have 4 workbooks open.
    In workbook #1 on the worksheet I'm testing I have a cell that points to a cell in workbooks #2 and a cell that points to a cell in workbook #3 The formulas are:
    Please Login or Register  to view this content.
    Both MtgKey cells do not have formulas in them.

    I save Workbook #4 and then run this macro inside Workbook #4 to time how long the sheet takes to calculate:
    Please Login or Register  to view this content.
    At that point 1 of the 2 cells recalculates, but not the other.
    I then run the macro again and neither cell recalculates.

    After saving Workbook #4 there are several cells that recalculate the 1st time I run the macro, but don't recalculate the 2nd time I run it. They all have formulas that have links to other workbooks, so I'm assuming that has something to do with why they recalculate after saving any workbook.

    But why don't both of those cells recalculate?
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  2. #2
    Registered User
    Join Date
    11-05-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: Question about confusing volatility issue

    I don't remember for sure but i recall reading something like the order of your spreadsheets could impact when things were recalced and that they were recalced in alphebetical order in excel '97 and 2002, though i don't think that answers your question

    This was a big help to me in figuring out how to better optimize my spreadsheet formulas, it has some stuff on VBA so if you haven't read through it before it's a good way to kill some time

    http://www.decisionmodels.com/calcsecrets.htm

  3. #3
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Question about confusing volatility issue

    Thanks;
    I've read that a few times, but I decided to read it again and I saw something that I think explains my confusion.

    I never noticed that workbooks get flagged as Dirty. So even though the MtgKey cells themselves weren't Dirty, one of the workbooks was, so that cell got recalculated after saving any workbook.

    That explains a few inconsistencies I've noticed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1