Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Toggle Calculation (2003)

    Hi,

    Is it possible to toggle between manual and automatic calculation with vba?

    Regards

    Nath

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Toggle Calculation (2003)

    Yes, you can use

    Application.Calculation = xlCalculationManual

    Application.Calculation = xlCalculationAutomatic

    and

    Application.Calculation = xlCalculationSemiAutomatic

    to set calculation to manual, automatic and automatic except tables, respectively. It can be very useful to set calculation to manual at the beginning of a macro that changes many values that will affect formulas, then set it to automatic again at the end (this will trigger a full recalculation).

  3. #3

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Toggle Calculation (2003)

    >Is it possible to toggle between manual and automatic calculation with vba?

    Thought-provoking question, thanks.

    Of greater interest (to me, at any rate), is how to hook the three "Application.Calculation" values into a deep and meaningful relationship with "Application.Volatile".

    My (novel) understanding of "Application.Volatile" is that when set to True it forces recalculation of the cell using the UDF whenever recalculation occurs anywhere in the worksheet (but Help doesn't make it clear whether they mean Workbook or Spreadsheet).

    Inhibiting automatic calculation (Application.Calculation = xlCalculationManual) means that calculation will take place only when I tap the F9 key.
    Then if "Application.Volatile=True" I'll see my UDF results updated, but if "Application.Volatile=False" I'll see my UDF remain unchanged, unless the input variables change.

    In short, one needs a multi-dimensional table to track through the combinations and their benefits.
    One dimension would be "testing/production", since when I'm testing UDFs I don't necessarily need to see every cell updated.
    One dimension would be "Volatile" settings
    One dimension would be "Calculation" settings.

    In today's version of XL2003/VBA help (v6.5) the Volatile setting is listed as a Method; I think it ought to be listed as a Property.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Toggle Calculation (2003)

    You should use:
    <code>Application.Volatile True</code>
    with no equals, hence it is a method not a property.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Toggle Calculation (2003)

    And where does the specific sheet property EnableCalculation fit in to all this???
    You can turn calc mode off for specific sheets, and on and off via VBA code.

    zeddy

Posting Permissions

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