Something Gets Forced

Category: VBA Weirdness | [Item URL]

Apparently, the documentation writers weren't quite sure of what gets forced when you use the ForceFullCalculation property in your VBA code.

Here's a screen grab from an MSDN article that lists Excel 2007's New Members and Constants:

The same uncertainty is expressed in the Excel 2007 Help System.

When you click the link for an explanation, here's what you learn:

Forces a full calculation of a workbook.

You might think that ForceFullCalculation is a method. It's actually a property of a workbook. But, typical of many things in Excel 2007, Microsoft provides no useful information about it.

I set this property to True for a workbook, using this statement:

ActiveWorkbook.ForceFullCalculation = True

The only result I noticed is that the Calculate indicator in the status bar turns on and never goes away -- even when I set the ForceFullCalculation property to False. The only way to get rid of that indicator is to close Excel and restart it.

I did some Google searching and found a PDF document of the Excel 97-2007 Binary File Format Specification, where I found this:

If the workbook is in the forced calculation mode, in which case dependencies are ignored, and all worksheets are marked to calculate fully every time the calculation is triggered.

OK, that makes sense. This setting is also stored in the Office 2007 file format. I found this in the workbook.xml:

<calcPr calcId="125725" forceFullCalc="1"/>

Now the question is, why would anyone want this to be in effect? And why doesn't that Calculate indicator go away when it's no longer in effect?