Application.Calculate causing Runtime Error

[quekbc]

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.

[snb]

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.

[quekbc]

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.

Sub T3EditStart_T3Edit()
Dim iInitialCalc As Integer
Dim ws As Worksheet
Dim iActiveProject As Integer
Dim iGenInputs As Integer, iCostBenefit As Integer, iSIC As Integer, iPCT As Integer, iRA As Integer
Dim iHeightImpact As Integer, iWidthImpact As Integer, iHeightPCT As Integer, iHeightRisk As Integer, iHeightCrit As Integer
Dim iHeightC As Integer
Dim rngDummy1 As Range, rngDummy2 As Range

    Application.Calculate
    
    If Range("T3Start_ProjNum").Value = 0 Or Not IsNumeric(Range("T3Start_ProjNum").Value) Then
        MsgBox "Please select a project", vbOKOnly, "Select Project"
    Else
    
    Call Disable(iInitialCalc)
        
    iActiveProject = Range("T3Start_ProjNum").Value
    
    With Tier3Edit
        .Visible = xlSheetVisible
        .Activate
        .Cells(1, 1).Value = iActiveProject
    End With
    
    iGenInputs = Range("T3General_Inputs").Count
    iCostBenefit = Range("T3CostBenefit").Count
    iSIC = Range("T3Score_SIC").Count
    iPCT = Range("T3Score_PCTSum").Count
    iRA = Range("T3Score_RASum").Count
    
    Tier3PCT.Visible = xlSheetVisible: iHeightPCT = Range("T3Score_PCT").Count
    Tier3Risk.Visible = xlSheetVisible: iHeightRisk = Range("T3Score_Risk").Count
    Tier3Impact.Visible = xlSheetVisible
        iHeightImpact = Range("T3Score_Impact").Rows.Count
        iWidthImpact = Range("T3Score_Impact").Columns.Count
        iHeightCrit = Range("T3Score_Criticality").Count
        iHeightC = Range("DB1_End").Row - Range("DB1_Start").Row
        
    'Import General Inputs
    Set rngDummy1 = Range("DB1AssociatedPortfolio").Offset(iActiveProject)
    Set rngDummy2 = rngDummy1.Offset(0, iGenInputs - 1)
    Range(rngDummy1, rngDummy2).Copy
    Range("T3General_Inputs").Item(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    'Import Cost & Benefits
    Set rngDummy1 = Range("DB1Benefits").Offset(iActiveProject)
    Set rngDummy2 = rngDummy1.Offset(0, iCostBenefit - 1)
    Range(rngDummy1, rngDummy2).Copy
    Range("T3CostBenefit").Item(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    
    'Import SIC
    Set rngDummy1 = Range("DB1SIC").Offset(iActiveProject)
    Set rngDummy2 = rngDummy1.Offset(0, iSIC - 1)
    Range(rngDummy1, rngDummy2).Copy
    Range("T3Score_SIC").Item(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        
    'Import PCT
    Set rngDummy1 = Range("DB2Score_PCT").Offset(1, iActiveProject - 1)
    Set rngDummy2 = rngDummy1.Offset(iHeightPCT - 1)
    Range(rngDummy1, rngDummy2).Copy
    Range("T3Score_PCT").Item(1).PasteSpecial Paste:=xlPasteFormulas
    
    'Import Capacity
    Range("DB1_Impact").Offset((iHeightC + 1) * (iActiveProject - 1)).Copy
    Range("T3Score_Impact").PasteSpecial Paste:=xlPasteValues
    
    Tier3Start.Visible = xlSheetHidden
    
    Application.CutCopyMode = False
    
    Call Enable(iInitialCalc)

    End If
    
End Sub

[davesexcel]

Why are you using it?

[quekbc]

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.

[davesexcel]

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.

Application.Calculation = xlCalculationAutomatic
'or
Application.Calculation = xlCalculationManual
'or
Calculate

[quekbc]

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.

[baldmosher]

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.

Dim xlCalc as xlCalculation
Dim xlCalc as xlCalculationState

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.

[baldmosher]

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....

[arlu1201]

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.

[baldmosher]

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

[baldmosher]

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