Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why does F9 not work all the time

    EXCEL 97 on a Pentium II 350 processor with WIN98.
    Question:
    I usually set recalculation to Manual when I am making changes to spreadsheet equations and links so that I do not have to wait after each change while EXCEL calculates the entire workbook. When I am finished with each change, I copy the changed cell down the rows that it needs to be in and then I hit F9. Sometimes this does not result in a recalculation. At time, I have had to go cell by cell down the column using F2 and enter. I work in a fast-paced environment where it is important to be able to get correct answers quickly. If my spreadsheet did not recalculate (when I thought it did) I lose face and my tools will not be used. So, this is important to me.
    Anyone know what is going on here.
    Thanks in advance.
    Stephen

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does F9 not work all the time

    That's microsoft for you. There's an earlier thread about this but in the meantime try using ctrl-alt-f9

    Brooke

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Why does F9 not work all the time

    Try Alt+Ctrl+F9 which (should) force a full recalculation of all open workbooks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does F9 not work all the time

    Stephen: There are some cases where a formula will not recalculate when you press F9. I believe that one of them is if the cell only contains functions which are not volitile. There may be others.

    There are a couple of ways around this. If the cells contain functions which you wrote, then you can use the Application.Volitile method in the function to make it volitile and have the cells that use it recalculate when the workbook recalculates.

    A second way is to hold down Ctrl/Alt when you press F9. This should cause all cells to recalculate.

    I also see that you are using Excel 97. Excell 97 recalculate was riddled with bugs. If you don't have Service Release 2 applied to Excel 97, then you still have some or all of those bugs. Most of those bugs also caused cells to not recalculate when they should. If you have to apply that fix, then you will probably have to go through the sheet and use the F2/Enter on each cell containing a formula to insure that the cells recalculate correctly in the future.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does F9 not work all the time

    Legare:
    Thanks for the detailed response. I knew nothing about Volatile functions. HELP says " To make a function volatile, add an exclamation point (!) as the last character in the type_text argument." Does this apply only to CALL and REGISTER functions or does it apply to functions that I enter directly into my spreadsheet as well?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does F9 not work all the time

    I can't find the quote you gave from the help file in my Excel 2000 help, so I am not sure what it means. My comments were about the Volatile method used in user defined functions to make them volatile. Here is what the XL 2k help says about that:

    Volatile Method


    Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

    <pre>Volatile Method

    Syntax

    expression.Volatile(Volatile)

    expression Required. An expression that returns an Application object.

    Volatile Optional Variant. True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True
    </pre>


    Typically, you would add a line like the following as the frist line of code in your function:

    Application.volatile
    Legare Coleman

Posting Permissions

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