EnableCalculation

rlslehigh

New Member
Joined
Jan 25, 2008
Messages
11
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,110
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It's not a bug I've heard of - which version of Excel are you using?
Regards,
Rory
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,097
One idea, does excecuting the line
Code:
Sheet.EnableCalculation = True
trigger a calculation?
Does replacing that with
Code:
Sheet.EnableCalculation = True
Calculate
help
 

rlslehigh

New Member
Joined
Jan 25, 2008
Messages
11
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

New Member
Joined
Jan 25, 2008
Messages
11

ADVERTISEMENT

sorry, Excel 2003. Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,110
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Active Member
Joined
Apr 16, 2003
Messages
469
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

New Member
Joined
Jan 25, 2008
Messages
11
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

New Member
Joined
Jan 25, 2008
Messages
11
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,462
Messages
5,770,219
Members
425,604
Latest member
futureexcelguru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top