[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

Excel 2007 - Calculation option set to Automatic but Status Bar still says Calculate

Hi Experts,

I'm using Excel 2007 and in an application that uses 3 different XLAM programs, I have a routine in the main Add-in program that executes a macro run from a button on my custom Ribbon.  That macro toggles between opening/closing one of the two other Add-ins.  These other two Add-ins contain no VBA, only XML code that serves to manipulate Excel's Ribbon.  My problem is that when I run the code to toggle these other two XLAM's on/off, the Status Bar then turns on the 'Calculate' message even though the Calculation Options are set to 'Automatic'.  Further, pressing F9 does not turn 'Calculate' off.  

The code that runs to toggle the two XLAM's on/off follows:

Public fullInterface As Boolean

Sub toggleCustomUIOnly(Optional control As Object)
S1 = Application.PathSeparator
T1 = Application.International(xlTimeSeparator)
Dim appPath As String
Dim wBook As Workbook
appPath = GetSetting(myAppName, "InstallPath", "Path")

On Error Resume Next
If fullInterface = True Then

    Application.ScreenUpdating = False
    SaveCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual

    Application.StatusBar = "Restoring User Interface. Please wait ..."
    Workbooks("Custom Only.xlam").Close savechanges = False
    Do While dseKeyPressed(VK_SHIFT)
        DoEvents
    Loop
    Workbooks.Open "Custom and Microsoft Default UI.xlam"

    fullInterface = False

    Application.Calculation = SaveCalcMode
    Application.ScreenUpdating = True
    Application.StatusBar = False

Else

    Application.ScreenUpdating = False
    SaveCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual

    Application.StatusBar = "Restoring User Interface. Please wait ..."
    Workbooks("Custom and Microsoft Default UI.xlam").Close savechanges = False
    Do While dseKeyPressed(VK_SHIFT)
        DoEvents
    Loop
    Workbooks.Open "Custom Only.xlam"

    fullInterface = True
    Application.Calculation = SaveCalcMode
    Application.ScreenUpdating = True
    Application.StatusBar = False

End If
Err.Clear
On Error GoTo 0
End Sub

Open in new window


BTW, there are no formulas (or even content) in the worksheets of the two XLAM files, and the main XLAM file contains only about 122 formula cells so this can't be an Excel dependency issue (which is supposed to have been resolved in Excel 2007 according to the page at: http://www.decisionmodels.com/calcsecretsf.htm.  I don't believe any of the other reasons for the 'Calculate' message discussed at this page apply either.

Why is this happening and what can I do to fix it? I appreciate any insights.

Jeff
0
jeffreywsmith
Asked:
jeffreywsmith
  • 15
  • 12
1 Solution
 
TommySzalapskiCommented:
The status bar saying 'calculate' means it has made some changes that would require a calculate.
0
 
TommySzalapskiCommented:
It got lit when calculate was manual but when calculation got set back to automatic, screen updating was off so it didn't unlight.
0
 
TommySzalapskiCommented:
If you swap them

    Application.Calculation = SaveCalcMode
    Application.ScreenUpdating = True


change to


    Application.ScreenUpdating = True
    Application.Calculation = SaveCalcMode

It should fix it.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jeffreywsmithAuthor Commented:
Hi Tommy and thanks for posting.

I tried your suggestion but it did not clear the 'Calculate' message.

BTW, I have to be away from my computer until about 3 pm EST so I won't be able to respond further until then.

Jeff

0
 
TommySzalapskiCommented:
Hmm. I can't replicate the error. Comment out the On Error Resume Next line and see if there are errors getting thrown that might be messing things up.
You are checking the options and it is automatic you said? It has to just be stuck on then.
0
 
jeffreywsmithAuthor Commented:
As you suggested, I Commented out the On Error Resume Next line, and also turned my VBE Error Handling Options to 'Break on all errors' but the only error I got was on the code like:

Workbooks("Custom and Microsoft Default UI.xlam").Close savechanges = False

... where I was doing a just-in-case closing of an XLAM file, should it be open.

Unfortunately, I still have the same issue with 'Calculate'.

Jeff

0
 
TommySzalapskiCommented:
I guess you changed the order in both places?
You could try doing this
    Application.ScreenUpdating = True
    Calculate
    Application.Calculation = SaveCalcMode
    Application.StatusBar = False

Open in new window

Again, I can't make it happen on mine so it's hard to test.
0
 
jeffreywsmithAuthor Commented:
Yes, I changed it in both places ... and tried the latest code above, too.  Still no joy.  

I will try to put together a test set of files to see if I can reproduce this.

Jeff
0
 
jeffreywsmithAuthor Commented:
Sorry this is taking me so long to keep this question going, but I am having a lot of strange issues in Excel.  At any rate, to the case in point, I have uploaded a zipped file that contains a folder ("TestThis") with 3 files:

CallXLAM.xlsm
Custom and Microsoft Default UI.xlam
Custom Only.xlam


TestThis.zip
0
 
jeffreywsmithAuthor Commented:
Sorry - Didn't mean to submit that yet. Comments to follow.
0
 
jeffreywsmithAuthor Commented:
I have uploaded (in my preceding post at 11/19/10 02:14 PM, ID: 34175677) a zipped file that contains a folder ("TestThis") with 3 files:

CallXLAM.xlsm
Custom and Microsoft Default UI.xlam
Custom Only.xlam

The CallXLAM.xlsm file will toggle between loading one or the other of the XLAM files, whose only function is to load the XML code contained therein to modify the Ribbon or reset it.

After clicking the Button to run the macro, 'Calculate' will be displayed in the Status Bar.  I've tried a number of things to get this to work, and the macro code will reflect that ... you'll see a number of Debug.Print statements, too, trying to isolate when/where the CalculationState changes.  You should see in the Immediate Window that the CalculationState changes to a "2" which supposedly means:

"xlPending - Changes that trigger calculation have been made, but a recalculation has not yet been performed"

This CalculationState gets changed from '0' to '2' in the preceding "Workbooks.Open" code (that opens the XLAM files) and never gets changed back despite all the subsequent code.

Hopefully, the attached code will be sufficient for others to reproduce this behavior.

Complicating this matter further, when I copy my actual main Add-in file (that houses the code simulated in the CallXLAM.xlsm file) to another PC, the 'Calculate' message does NOT display when the code runs (but the code in the attached TestThis package DOES display 'Calculate' on the alternate PC).

Along the way, I've started encountering messages when saving both the main XLAM program and this example file, that look like this:

 screen shot
Help is non-specific; Answering 'No' and trying to save generates another error like this:

 2nd screen shot
Answering 'Yes' allows the file to save, but I'm still nervous about what is going on here ...

I'm stumped.  Hope someone can assist with this.

Thanks,

Jeff
0
 
TommySzalapskiCommented:
Okay. I'll need to login to my Excel 2007 computer at the lab. I'll give it a go.
0
 
jeffreywsmithAuthor Commented:
Ok, thanks, Tommy. Fingers crossed.

Jeff
0
 
TommySzalapskiCommented:
Okay. If I open your .xlam files, they say Calculate too. So it must be some lingering thing in the custom ribbon or something. Saving and reopening maintains the Calculate stuck on.
0
 
jeffreywsmithAuthor Commented:
Got to believe there must be a way to avoid or correct this ... these files have nothing to do with 'Calculation' of anything ... just toggling the hiding/displaying of Microsoft's Default Ribbon Tabs.  And again, there's no VBA in those other two files.

Jeff
0
 
TommySzalapskiCommented:
Hmm... This article describes a similar phenomenon, but it doesn't look like it applies here.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243495
0
 
TommySzalapskiCommented:
This article is good to but again... maybe not applicable
http://www.decisionmodels.com/calcsecretsf.htm
0
 
jeffreywsmithAuthor Commented:
Yeah. The MS link (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243495) is for XL2003 & prior (The 65K dependency limit has purportedly been removed in XL2007 and shouldn't have been invoked by the ~ 130 calculated cells in my file.

The 2nd link is the same one I mentioned in my original post - I don't see that it applies here either.

Jeff
0
 
jeffreywsmithAuthor Commented:
Well, Tommy, it seems like you and I are hitting a wall here ... with the things we know to try not seeming to provide resolution here.  Given that Excel 2007 (& 2010, as well) introduce an entirely different User Interface, not to mention reliance on XML file formats, I've often wished EE had a Zone just for Office 2007+ (or at least Excel 2007+) as it definitely introduces some new wrinkles from time to time (like now), and it's sometimes hard to find Experts with the expertise in this version's idiosyncrasies.  I don't know if you know of any other EE Experts with such Excel-2007-specific expertise you might invite to join this topic ... I will contact the moderator to ask that, too.

Thanks for your help, Tommy ... and don't mean to suggest you might not find a solution here but it seems like we are at a dead end at present.
0
 
TommySzalapskiCommented:
That's bizarre if it works, but this is Microsoft and odd things happen often. No problem Jeffery, I know I don't always have all the answers. I ask questions here too.
0
 
jeffreywsmithAuthor Commented:
Thanks for the suggestion, ssaqibh. However, I don't believe this is the issue.  I tried using both a Forms Control button and an ActiveX Control button, and they both leave the 'Calculate' message on the Status Bar.  Further, while the example file I uploaded to EE uses a button to launch the macro, the original problem exists in my XLAM file, where the macro code resides behind a button on my Custom Ribbon, which I think avoids that previous issue with the ActiveX Control button anyway.

Jeff
0
 
jeffreywsmithAuthor Commented:
Well, I guess I need to apologize for leading everyone on a wild goose chase here.  What I have found out is that I apparently had inadvertently 'locked' the two minor XLAM files using a pretty exotic Excel protection package (LockXLS) for my application, and the "locking" mechanism they use was causing this "Calculate" message somehow.  At any rate, I didn't need these two files locked anyway, so I reverted back to an earlier version of the two XLAM files and all is well again.  

Sorry, I didn't realize that I had done this ... and, of course, no one at EE could have figured this out without knowing about it, so my bad.

I expect the thing to do here is to ask that this question be deleted but I do appreciate everyone's efforts.

Jeff
0
 
TommySzalapskiCommented:
Actually, what you would normally do is accept your own post (the last one) as the soluton. This way just in case someone has the same error some time in the future, they can find your post and save themselves hours of stress.
0
 
jeffreywsmithAuthor Commented:
Hi Tommy,

I don't mind doing this one way or another but I thought the possibility of someone else running into this bizarre behavior again was pretty slim.  But I have no objections to how ever the moderators handle it.  Thanks again, Tommy.

Jeff
0
 
TommySzalapskiCommented:
>>I thought the possibility of someone else running into this bizarre behavior again was pretty slim

Yes, but for that one guy...
I've had some pretty hairbrained issues that were resolved by old forum posts.
0
 
jeffreywsmithAuthor Commented:
Ok, Tommy, I made the change you suggested.

Jeff
0
 
jeffreywsmithAuthor Commented:
Found the answer myself (but EE Experts weren't fully informed on the file characteristics).
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 15
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now