+ Reply to Thread
Results 1 to 12 of 12

Application.Calculate causing Runtime Error

  1. #1
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Application.Calculate causing Runtime Error

    Hi All,

    Has anyone encountered any issue with Application.Calculate causing Syntax errors?
    The error message in particular is Run-time error '1004'. Cannot run visual basic macro because of a syntax error.

    I am very confident that the rest of the code is working. This error also happens when Application.Calculate is executed via the Immediate window.

    Due to the usual disclosure thingy, I can't provide the code that I am currently working on but I've done some research online and there are others who face these issues back in 2007 and was never resolved, one of which is http://www.officekb.com/Uwe/Forum.as...tion-Calculate

    I am just wondering if anyone ever encountered this kind of problem and what did you do to fix it, if any.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Application.Calculate causing Runtime Error

    Maybe you are writing formulae with VBA into cells, that encounter wrong values generated by your VBA-code.

    You can always put your VBA-code here.

  3. #3
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Application.Calculate causing Runtime Error

    Here's the code that I'm working on.

    With application.calculate commented, the code works fine. Otherwise, on occasions it generates the error.

    This is mind boggling.

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Application.Calculate causing Runtime Error

    Why are you using it?

  5. #5
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Application.Calculate causing Runtime Error

    Depending on user settings, some users like to set calculation to manual. In which case, the code will generate the wrong result. Application.calculate is my attempt at preventing this from happening.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Application.Calculate causing Runtime Error

    You can set the calculation to Manual or Automatic. Pressing F9 Calculates as well. Using your Macro recorder, press F9. You will see the code is just Calculate, it doesn't use Application.
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Application.Calculate causing Runtime Error

    Using Calculate causes the same error.
    Setting calculation to automatic seems to do the trick although I'm still curious as to why Calculate would cause this kind of error.

    Guess I'll have to leave it as one of life's mysteries.

    Thanks Dave.

  8. #8
    Registered User
    Join Date
    09-27-2012
    Location
    Manchester, GB
    MS-Off Ver
    365,2016,2013,2010,2007,2003
    Posts
    17

    Re: Application.Calculate causing Runtime Error

    Sorry if the usual thing is to start a new thread rather than bumping an old one, but I've had the same issue and am no closer to identifying how to fix it, nor how it happened, so I'm just providing my own evidence.

    Please Login or Register  to view this content.
    I've recently started trying to record (and later revert to) the original Application.Calculation setting, using both of the above variables at various points along my learning curve. (I've since realised I should only be using xlCalculation because xlCalculationState [is it boolean?] reports whether the application is currently calculating or not.)

    I may have corrupted an Excel library somehow as a result of this messing around. I'm guessing I might have inadvertently set Application.Calculation = xlCalculationState, which is probably a bad idea. Or maybe xlCalc is just one of those 'bad idea' variable names, or maybe I shouldn't be declaring my variables as custom types?

    Thankfully, this only occurs in one workbook, and restarting Excel fixes the issue in the application, so I remain hopeful that I can isolate and remove/bypass this error.

    When I exit Excel, it asks if I want to save changes to my personal macro workbook (and obviously, with errors occurring, I'm saying no...!) so I may have somehow managed to corrupt a setting in the Application, but I haven't figure out exactly how or what I've changed just yet. Still bug-testing. If I figure this out, I'll pop back here and update.

    Evidence:

    VBA > Debug > Compile brings up no errors.

    when I run Calculate or Application.Calculate (either Immediate or inline code):
    msgbox "run-time error 1004, cannot run Visual Basic macro because of a syntax error"

    pressing F9 to calculate manually:
    msgbox "cannot find project or library" then pops up the References dialog (!!)

    This is in Excel 2010 by the way, but I don't think that's particularly relevant, unless perhaps a bit of 2003 legacy code might have caused the settings corruption.
    Last edited by baldmosher; 05-18-2013 at 09:22 AM.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Application.Calculate causing Runtime Error

    baklmosher,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  10. #10
    Registered User
    Join Date
    09-27-2012
    Location
    Manchester, GB
    MS-Off Ver
    365,2016,2013,2010,2007,2003
    Posts
    17

    Re: Application.Calculate causing Runtime Error

    Thanks, but I'm not looking for a solution, I'm just playing around with the code to achieve nothing in particular and broke it. Effectively, I accidentally stumbled upon a possible explanation for the OP's issue. Obviously if this doesn't give someone a Eureka moment then it can be ignored

  11. #11
    Registered User
    Join Date
    09-27-2012
    Location
    Manchester, GB
    MS-Off Ver
    365,2016,2013,2010,2007,2003
    Posts
    17

    Re: Application.Calculate causing Runtime Error

    Quote Originally Posted by quekbc View Post
    Using Calculate causes the same error.
    Setting calculation to automatic seems to do the trick although I'm still curious as to why Calculate would cause this kind of error.

    Guess I'll have to leave it as one of life's mysteries.
    Don't forget that automatic calculation in Excel and VBA [Application.]Calculate are separate subroutines (although, maybe written in C++) so it can also be debugged. How to do that, I have no idea....

  12. #12
    Registered User
    Join Date
    09-27-2012
    Location
    Manchester, GB
    MS-Off Ver
    365,2016,2013,2010,2007,2003
    Posts
    17

    Re: Application.Calculate causing Runtime Error

    I've isolated and fixed my issue this morning, so I'll post it as a new problem in a sec.

+ 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