Forcing to recalculate

[Michael.Tarnowski]

In a post in ExcelForum
(http://www.excelforum.com/excel-programming/337036-application-
calculate-does-not-always-update-recalculate-the-for.html)
I found following information concering Recalculation:

Present documentation shows the following:
* F9 - recalculates all of the data in the open workbooks
(Application.Calculate)
* Shift+F9 - only calculates data in the specified worksheet
(ActiveSheet.Calculate)
* Ctrl+Alt+F9 - Forces a full calculation of the data in all of the
open
workbooks (Application.CalculateFull)
* Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of
the
open workbooks after checking the dependencies between formulas
(Application.CalculateFullRebuild)

In an application where circular references are use - like =IF
(D10=0;NOW();D10)) - I discovered the following behaviour: none of the
mentioned above techniques changed the cell (D10), only placing the
cursor in the formular and hitting <Return> afterwards (="Pseudo
Editing") changed the content of D10.

Any ideas how to achieve this with VBA?
Michael

 

[Mike H]

Michael.


The formula below isn't a circular reference and will recalculate by tapping
F9

=IF(D10=0,NOW(),D10)

Have a look here to understand Excel calculation

http://www.decisionmodels.com/calcsecretsi.htm

Mike

 

[Michael.Tarnowski]

In a post in ExcelForum
(http://www.excelforum.com/excel-programming/337036-application-
calculate-does-not-always-update-recalculate-the-for.html)
I found following information concering Recalculation:

Present documentation shows the following:
* F9 - recalculates all of the data in the open workbooks
(Application.Calculate)
* Shift+F9 - only calculates data in the specified worksheet
(ActiveSheet.Calculate)
* Ctrl+Alt+F9 - Forces a full calculation of the data in all of the
open
workbooks (Application.CalculateFull)
* Ctrl+Shift+Alt+F9 - Forces a full calculation of the data in all of
the
open workbooks after checking the dependencies between formulas
(Application.CalculateFullRebuild)

In an application where circular references are use - like =IF
(D10=0;NOW();D10)) - I discovered the following behaviour: none of the
mentioned above techniques changed the cell (D10), only placing the
cursor in the formular and hitting <Return> afterwards (="Pseudo
Editing") changed the content of D10.

Any ideas how to achieve this with VBA?
Michael

Problem solved!!
In ExcelForum again http://www.excelforum.com/excel-programming/390157-functions-wont-calculate-without-f2.html
I found a cheat to solve this: rebuilding the formula in question:

Sub RedoFormulae()
Range("HiddenTotal").FormulaR1C1 = "=sumhiddenrows(NumberRange)"
Range("VisibleTotal").FormulaR1C1 = "=sumvisiblerows(NumberRange)"
End Sub

That rocks! - Thus I have only to cycle through my cells in question
to rebuild the formula

Have a nice day

Michael

 

[Gary''s Student]

Sub re_assert()
Range("D10").Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
End Sub

 

[Michael.Tarnowski]

Sub re_assert()
Range("D10").Select
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents
End Sub

Gary, Mike thank you for answers.

@Mike: it is circular, since D10 is the cell in question.
Thank you for the web-link.

Here is my code I came up with, it cycles through all cells of row 10
and rebuilds formulas if cells have ones.

Public Sub RedoFormulae()
' Cycling through template row and rebuilding all of it's formula to
force hard recalculation
' Idea from ExcelFormu, http://www.excelforum.com/excel-programming/390157-functions-wont-calculate-without-f2.html

Dim sFormula As String
Dim NumCols As Long
Dim Rng1 As Range
Dim Rng2 As Range

Config.Range("A10").Select
Set Rng1 = Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count).End
(xlToLeft))
Set Rng2 = Config.Range("A10").Resize(, Rng1.Columns.Count)
NumCols = Rng2.Count

For j = 1 To NumCols
' rebuild only cells with formulas
If Rng1.Cells(j).HasFormula Then
sFormula = Rng1.Cells(j).FormulaR1C1
Rng1.Cells(j).FormulaR1C1 = sFormula
End If
Next j

End Sub

 

[Charles Williams]

To explain what's happening:

If you create a circular reference ( for example by putting the formulae
=IF(D10=0,NOW(),0) in cell D10) then you need to tell Excel to solve for
circular references by ticking the Iteration checkbox (Tools-->Options) and
setting Iterations to an appropriate number (like 1 to single-step the
iterations, so that every time you press F9 the cell flipflops between 0 and
the current time).

If you don't tick the Iteration checkbox then the cell will not recalculate
(because Excel knows it is circular) unless you re-enter the formula, which
resets the cell to zero and then forces Excel to re-evaluate the cell, thus
giving you the time you re-entered the formula.

The standard warning on circular references applies: using a deliberate
circular reference hides unintentional circular refs.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Michael.Tarnowski]

To explain what's happening:

If you create a circular reference ( for example by putting the formulae
=IF(D10=0,NOW(),0) in cell D10) then you need to tell Excel to solve for
circular references by ticking the Iteration checkbox (Tools-->Options) and
setting Iterations to an appropriate number (like 1 to single-step the
iterations, so that every time you press F9 the cell flipflops between 0 and
the current time).

If you don't tick the Iteration checkbox then the cell will not recalculate
(because Excel knows it is circular) unless you re-enter the formula, which
resets the cell to zero and then forces Excel to re-evaluate the cell, thus
giving you the time you re-entered the formula.

The standard warning on circular references applies: using a deliberate
circular reference hides unintentional circular refs.

Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

Charles your right, but I have marked the Iteration option
(automatically by VBA when worksheet is opening).
Thanks for your amendment.
Michael

 

[Michael.Tarnowski]

Charles your right, but I have marked the Iteration option
(automatically by VBA when worksheet is opening).
Thanks for your amendment.
Michael

I made an odd observation:

when using the code I presented, the application behaved on a PC with
english Win/Excel installation and on a PC with german Win/Excel
installation as intended; but when using Gary's "SendKeys"-technique,
the application on the german PC got weird: it scrolls down numerous
lines and do not updated the formulas.
Any ideas?

Nevertheless, using
sFormula = Rng1.Cells(j).FormulaR1C1
Rng1.Cells(j).FormulaR1C1 = sFormula
works.

Michael