Is there a way to force recalc of all cells, not just dependent ones?

[TomCon]

I thought Ctl-Alt-Shift-F9 or Ctl-Alt-F9 would force "recalc of every cell regardless of dependency". But neither of those seem to work now. I have a big workboook such that i usually see Calculating...xx% in a while at the bottom before it finishes, but do not see that now with either of those keystrokes. I see some conflicting comments about this by googling it. Does anybody know the real story? Should these work?

Most important, if this does not work, is there ANY way to force "recalc all, not just dependents"?

I have some formulas where dependencies are within UDFs written in VBA, and so Excel is not able to see them as dependencies. Hence if some of those dependencies are changed in the workbook, i need to be able to tell Excel to "recalc every cell, regardless of what it thinks dependencies are".

I have found that the following work SOMETIMES but not reliably.

• Just hit the "Calculate Now" in the ribbon - works sometimes to recalc the cell but cannot be relied upon
• Save the workbook - After hitting Save, SOMETIMES (irregularly) the cell value with the call to the UDF is updated. Sometimes not.
• Close/Open the workbook. This used to be my fallback, seemed to work MOSTLY, but now have some cases where the workbook reopens and the cell value with the UDF call is still not updated.

So, the fallback is to edit the individual cell, any bogus edit, and then the value is correctly updated. But, i do not want to depend upon myself to be the "Dependency machine" I may not always know every single cell that should be updated based on a change made in the workbook.

Any suggestions for how i can assure that all cells are recalculated regardless of any concept that Excel has for dependencies?

Thanks!

 

[jasonb75]

I have some formulas where dependencies are within UDFs written in VBA, and so Excel is not able to see them as dependencies.

It sounds like you need to make these UDFs volatile so that they recalculate when a change is made to cells which are not precedent.

Application.Volatile method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[TomCon]

It sounds like you need to make these UDFs volatile so that they recalculate when a change is made to cells which are not precedent.

Application.Volatile method (Excel)

Office VBA reference topic

learn.microsoft.com

Thank you, yes, i suppose that would do it. The trouble is that i have 96,000 rows on the sheet so these UDFs would be called hundreds of thousands of times and really make things cumbersome. But that may be the only solution. So, there is no known way in VBA or in the UI to simply force "calculate all regardless of precedent" to happen? I thought that used to work years ago but it does not seem to work now.

 

[jasonb75]

Yes, I see what you mean. With that number of calls I can understand why you want control over the process.

Ctrl Alt f9 is working fine for me with a similar test function.

The vba alternative that might be more useful is Application.CalculateFullRebuild

Application.CalculateFullRebuild method (Excel)

Office VBA reference topic

learn.microsoft.com