Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Trying to turn off auto-calculation in a macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a sub routine that runs really slowly unless the calculation method is set to manual. I can run the sub from the VB window OK (the sub is in Module 1) but if I try to run it from a button on a worksheet I get a "Method 'calculation' of object 'application' failed" error message.

    The line of code I'm using is:

    Application.Calculation = xlCalculationManual

    This is really driving me mad. Can anyone help?

    JohnnyC

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It might be that your calculation is already set to manual. Might be worth trying this instead: -

    With Application
    If .Calculation <> xlCalculationManual Then
    .Calculation = xlCalculationManual
    End If
    End With

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...just tried that. Calculation was not already set to manual, it runs through to the next line and gives the same error message.


  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, this runs OK for me, whichever way I call it. Could you post the rest of your code, just to see if something else is causing it?

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, I've just put a line of code in front of the section you sent me which selects a different worksheet.

    That was the only change I made and the macro runs fine now...

    Thanks for your help anyway.

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-26 03:14, JohnnyC wrote:
    OK, I've just put a line of code in front of the section you sent me which selects a different worksheet.

    That was the only change I made and the macro runs fine now...

    Thanks for your help anyway.
    Sounds like you have excel97 ??
    If so then anothr way around this is to
    set your cmd button to;

    TakeFocus******* = False


    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Completely right. I am using Excel 97. That button focus thing must have caused me a LOT of problems!

    Thanks for the tip.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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