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