EnableCalculation

[rlslehigh]

I have a macro that can individually turn on or off the EnableCalculation property of each worksheet in a workbook (that is, set the property to True or False). It worked very nicely for a while, and made some of my more formula-laden workbooks much more tractable. But after a while, when I would turn the EnableCalculation back to True for any worksheet, the formulas on that sheet were still frozen (would not recalculate at all), and could only be unfrozen by going to each cell and doing [F2] [Enter]. Just to rule out the obvious, Automatic calculation is turned on, and I have made sure that the macro is successfully returning the EnableCalculation property back to True.

It seems that if I physically go into the VB Editor and manually (through the GUI, as opposed to programmatically) switch the EnableCalculation property back to False, then back to True again, then it will revive the formulas. But this is silly, and I was hoping there was a better solution... maybe someone has encountered this before? If it's just another Microsoft bug, I don't know what's the trigger is, but I imagine it's when you save/close the workbook with some of the sheets' EnableCalculation properties still set to False. I know the property is not stored/saved with the workbook, i.e. all sheets will reset EnableCalculation to True when the workbook is re-opened, but maybe it's still causing a glitch.

Thanks in advance for any help!
Ryan

 

[RoryA]

It's not a bug I've heard of - which version of Excel are you using?
Regards,
Rory

 

[mikerickson]

One idea, does excecuting the line

Sheet.EnableCalculation = True

trigger a calculation?
Does replacing that with

Sheet.EnableCalculation = True
Calculate

help

 

[rlslehigh]

Thanks for your replies.

That's the problem, setting the sheet's EnableCalculation property to True, programmatically, does not trigger any calculation... not anymore, but it used to, when I first started using the macro. Adding the 'Calculate' line immediately afterwards probably won't help because hitting [F9] doesn't help... switching automatic calculation off, then turning it back on doesn't help... none of the variations on [F9] do the trick either. I will give it a try, though, just to make sure. It's so strange, all the formulas are frozen solid as if EnableCalculation is still False, but I go and check the property, and invariably it was successfully set to True just as I expected. The only thing that I've found to revive the formulas is to go and switch the property manually in VBE (to False, then back again to True).

Any other suggestions from any one?

Thanks Again,
Ryan

 

[rlslehigh]

sorry, Excel 2003. Thanks!

 

[rlslehigh]

sorry, Excel 2003 SP3. Thanks!

 

[RoryA]

A couple of thoughts - have you tried:
1. Always resetting each sheet to True before saving/closing the file?
2. Setting the property to True then False then True again to see if that forces the issue?
Regards,
Rory

 

[goblin]

My guess is that when you first did this, the dependency between sheets wasn't very complicated. So although you had a lot of formula, each sheet was pretty much independent.

Now, you have cross-dependencies all over the place and you have 'dirty' cells all over the place (dirty meaning Excel knows the cell isn't updated). When you turn on calculation in some sheet, the dependency tree in Excel sees that your current sheet depends on a dirty cell in some other sheet, sees that that sheet is on manual calculation and stops calculation.

I have NO IDEA if this is correct. Just voicing my suspicion.

Anyway, if you don't get this to work, you can always force a full calculation of the workbook by doing: Application.CalculateFull

regards,
Goblin

 

[rlslehigh]

I will give that a shot. I could use the BeforeClose event to set all sheets to True. If there's a BeforeSave event, I may have to use that too. I will post back when I figure out if that solves the problem. May take a while, because like I said, not sure what triggers the bug... it used to work, switching back and forth between True and False via macro with no issues. For some reason it just stopped working, leaving my formulas completely inert.

Thanks for the recommendations!
Ryan

 

[rlslehigh]

Hmmm, CalculateFull, never used that one before. I will also give that a try. Your suspicion regarding interdependencies might be a contributing factor. Although, I have tried switching all sheets back to True, then recalculating the whole workbook via the GUI, with no success. But maybe CalculateFull is strong enough to force my formulas back into action.

Thanks!