Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forcing workbook to recalc (2000 )

    We are using an Add-in that retrieves data from an external database though formulas defined by the addin. The formula takes parameters listed in other cells. Example - Cell d5 = Data(A1, B1, D3).

    The difficulty is the formula does not recalc buy hitting F9 or CalcSheet button. I assume it is because the parameters have not changed. We want to recalc because data in external database may have changed.

    Any way to force a complete recalc of all the workbook formulas even when no precedent data in workbook has changed?

  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: Forcing workbook to recalc (2000 )

    Try control - shift - F9 and see if that does anything.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forcing workbook to recalc (2000 )

    I think that Application.Calculate or just Calculate will do this. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing workbook to recalc (2000 )

    Thanks.

    Shift-Ctrl-F9 does not do anything.

    Is the 'Calculate' method in 2nd reply a VB construct? Or on UI somewhere?

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing workbook to recalc (2000 )

    Update -

    Ctrl-Alt-F9 seems to do the trick but I cannot find any doc on what it does.

    Any idea what this keystrock combo is for?

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

    Re: Forcing workbook to recalc (2000 )

    It forces a full calculation in situations where F9 doesn't work - there is a knowledgebase article on this somewhere but I couldn't find it - I'll keep hunting and edit this post if I find it.

    What Sammy gave you is VBA, so you'd need to put it in a code module or run it from the immediate window in the VBE. To do this (tell me if I'm teaching you to suck eggs) from excel press Alt-F11. you should see the VBE. on the left you'll see the project explorer - select your file and then select Insert:Module from the menu. Copy the following into the module:

    sub test()
    Application.CalculateFull
    end sub

    Now, back in Excel, go Tools:Macro:Run and select the macro you have just entered
    This should force the full calculation. Let us know if this doesn't make sense.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing workbook to recalc (2000 )

    The control-shift-F9 should do that. In VBA:

    Application.CalculateFull

    (Available from XL2K and Up)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing workbook to recalc (2000 )

    Thanks so much! I have also found old posts on this subject which talk about the Ctrl-Alt-F9 key where F9 fails.

    There are always more questions -
    Is the VBA command App.CalculateFull then equivalent to Ctrl-Alt-F9?

    How does one only fully recalc only the current open Workbook, not all open workbooks?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing workbook to recalc (2000 )

    I failed to spot it should have been control-alt-F9, NOT control-shift-F9.

    ctrl-alt-F9 is the same as Application.CalculateFull

    Since CalculateFull only applies to the application object you cannot do it for a single book, by e.g.:

    Activeworkbook.CalculateFull (gives a runtime error)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Forcing workbook to recalc (2000 )

    <hr>I failed to spot it should have been control-alt-F9, NOT control-shift-F9.<hr>
    Don't you just hate people who make deliberate mistakes? <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20> Q144508 is as close as I can get, though this shows a few more related issues. Most of them do seem to be 97 and not 2K though.

Posting Permissions

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