"Calculate" Won't Turn Off - INTERESTING

[jswhip]

Interesting scenario in Excel 2007 - "Calculate" always shows up even though autocalc is on and iteration is off; thus the usual suspects are eliminated.

The really interesting part is that there aren't any formulas in the workbook. If I hit F9, the message goes off. But if I do nothing except save the workbook, it comes back on. Any thoughts on what to look for?

 

[Colin Legg]

Hi,

Possible reasons for calculate showing in the status bar are listed here:
http://www.decisionmodels.com/calcsecretsf.htm

 

[jswhip]

Thanks for the reply.

I saw that article, but it applies to pre Excel 2007 versions. As I don't have any dependencies (I can recreate the error with only text in one cell), I don't believe that is the issue as well.

I've even tried repairing my install. Unless I can find something else, I'l probably have to resort to uninstalling and reinstalling Excel...

 

[Colin Legg]

Hi,

I saw that article, but it applies to pre Excel 2007 versions. As I don't have any dependencies (I can recreate the error with only text in one cell), I don't believe that is the issue as well.

The fifth item on the list is this:

You are using Excel 2007 and have set Workbook.ForceFullCalculation to True

so the article includes Excel 2007.

If you have another situation which is causing calculate to appear then it will be of great interest to us!

Does this occur in a specific workbook or all workbooks?

 

[jswhip]

Thanks again for the reply.

Unless I'm missing something, I'm fairly certain this situation doesn't apply to Excel 2007. From the article:

"According to Dave Gainer's Excel 12 blog, these limits have been removed in Excel version 2007. If you find that in Excel 2007 recalculation is taking longer than full calculation, or the time taken to update the dependency tree at each change is too large, you can force each calculation to be a full one using Workbook.ForceFullCalculation from VBA."

I'm not having long recalc times, but apparently my install thinks I need to continually recalc. I'm not sure if these are false positives, or if I really need to recalc.

I haven't tried the re-install yet, but that's my last try before appealing to higher authorities (my I.S. team...)

 

[xenou]

The really interesting part is that there aren't any formulas in the workbook. If I hit F9, the message goes off. But if I do nothing except save the workbook, it comes back on. Any thoughts on what to look for?

This is a workbook with no formulas? What in fact is in it then? Does the same occur if you create a new workbook that does have some formulas in them?

 

[Colin Legg]

Hi,

If you remove any sensitive data (but carefully so that the problem persists) and e-mail it to colin at colinlegg.co.uk I'll have a look to see if I get the same problem?

Unless I'm missing something, I'm fairly certain this situation doesn't apply to Excel 2007. From the article.... "According to Dave Gainer's Excel 12 blog, these limits have been removed in Excel version 2007...."

That refers to the dependency tree limitation. If you have Workbook.ForceFullCalculation set to true then Calculate will show in the status bar in 2007. However, in this particlar case, I think that ForceFullCalculation is not the cause of the problem.

 

[jswhip]

I think it's my install of Excel is goofed up. I thought it was due to something in a workbook that was doing some data manipulation, but it happens on EVERY workbook (hence, the example from above with only one cell containing text).

If the reinstall fixes things, then I'm at a loss as to what caused the anomaly as I've done far more data intensive queries without issue in the past.

 

[Colin Legg]

Hi,

I received your email. When I opened the workbook I didn't get Calculate in the status bar (even after saving). Did you reinstall Excel in the end?

 

[jswhip]

There is a 3rd party add in that when removed, the "calculate" message turns off. However, when I talked with the vendor and sent them my file, they were unable to replicate my results. I haven't tried downloading and reinstalling their add in, but since it's password protected, I'm not sure how I could have changed the add in (even by accident).

At this time, I'm going to ride it out as we're going to Windows 7 in the near future. I'm 99.9999% sure the message is a false positive as there aren't any calculations going on.

Very strange, but rather than trying to figure out what got "tripped" deep in the bowels of Excel, I'm moving on.