Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax Error (2kSR1)

    In my macro, I am deleting some lines and get to the following code:
    rTemplateCCButton.Offset(-OS, 1).EntireRow.Delete
    Application.Calculate
    And I get the message "Cannot run Visual Basic Macro because of a Syntax Error". When I click on "Debug", the line highlighted is "Application.Calculate".
    What does the error message mean in this context?

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

    Re: Syntax Error (2kSR1)

    I don't see anything obviously wrong with the code you posted in your message. It is possible that there is some non-display character on that line. Try deleting the line and retyping it. It is also possible that the error is somewhere other than those two lines. If deleting and retyping does not fix the problem, could you post the rest of the macro?
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (2kSR1)

    OK, I deleted and retyped and that did nothing. Could it be that the syntax error is referring to some syntax error somewhere on the spreadsheet? Would a formula syntax error result in this message?
    Unfortunately, the code is long, but I've attached it.
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (2kSR1)

    I just tested something else. I commented out the "Calculate" and instead put :
    Application.Calculation = xlCalculationAutomatic
    just above the code where the error occured. Worked fine. So there must be some kind of difference between what happens with xlCalculationAutomatic and a hand coded "Calculate". Any ideas?

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

    Re: Syntax Error (2kSR1)

    I am afraid I am stumped as to what is happening here. I copied the code you posted and pasted it into my Excel. It compiles with no errors. I then searched the code for Calculate and discovered that there was no Application.Calculate in the code. I did find that there was a "Calculate" without the "Application." in front of it and the statement before it looked the same as the one you posted in your first message. I changed that to "Apllication.Calculate" and it still compiled with no problems. I can't actually run the code without the rest of your workbook. If you select Compile VBAproject from the Debug menu, do you get the error, or do you just get it when you run the code. Do you get the error when you have just "Calculate" without the "Application." (those should be the same)? I would not have thought that an error on a worksheet would cause this error, but if you do not get the error when you do Debug/Compile VBAProject, and do get it when you run the code, then I guess that could be a possibility. If it is not too big, you could post the entire workbook, and we could look at it further,
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Syntax Error (2kSR1)

    I still don't see why your code errors. Application.Calculate and Application.Calculation are different

    Application.Calculate tells the WB to recalc.
    Application.Calculation returns or sets one of the following XlCalculation constants: xlCalculationAutomatic, xlCalculationManual, or xlCalculationSemiautomatic.

    I can only imagine that Application.Calculate would fail if no WB is open.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Syntax Error (2kSR1)

    Are there any VBA user defined functions involved (functions created in VBA that are called from a worksheet cell)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Syntax Error (2kSR1)

    But it should not fail with a Syntax Error if no workbook was open.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Syntax Error (2kSR1)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> True. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (2kSR1)

    A workbook was open, and no, there are no VB functions involved. There is, however, a rather humungous workbook (5Meg) with lots of complicated inter-sheet calculations that no doubt are stretching the Excel engine into some uncharted territory. Anyway, it works with the other "automatic" command so I'm going to move on. But I bet there is a bug in there somewhere...

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

    Re: Syntax Error (2kSR1)

    Just a few more thoughts.

    I've seen irratic behaviour like this happen to modules with heavily edited code. You might benefit from a code cleaning job. Download and run Rob Bovey's codecleaning utility from the Excel mvp page below my signature.

    Also, if your module has too much lines (approximately 4000 reaches the limit), very strange compile errors may surface. If so, split it in two.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Syntax Error (2kSR1)

    My thoughts exactly! I had a case where Excel 'turned off' calc mode to manual all by itself. After using Bob'c code cleaner the problem went away.

    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
  •