Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
In a post in ExcelForum
(http://www.excelforum.com/excel-prog...6-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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
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" wrote: In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
On Feb 11, 11:27 am, "Michael.Tarnowski" wrote:
In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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-prog...ithout-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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
Sub re_assert()
Range("D10").Select Application.SendKeys "{F2}" Application.SendKeys "{ENTER}" DoEvents End Sub -- Gary''s Student - gsnu2007L "Michael.Tarnowski" wrote: In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
On Feb 11, 11:49 am, Gary''s Student
wrote: Sub re_assert() Range("D10").Select Application.SendKeys "{F2}" Application.SendKeys "{ENTER}" DoEvents End Sub -- Gary''s Student - gsnu2007L "Michael.Tarnowski" wrote: In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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 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-prog...ithout-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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
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" wrote in message ... On Feb 11, 11:27 am, "Michael.Tarnowski" wrote: In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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-prog...ithout-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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
On Feb 11, 12:18 pm, "Charles Williams"
wrote: 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 "Michael.Tarnowski" wrote in message ... On Feb 11, 11:27 am, "Michael.Tarnowski" wrote: In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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-prog...tions-wont-cal... 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 Charles your right, but I have marked the Iteration option (automatically by VBA when worksheet is opening). Thanks for your amendment. Michael |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forcing to recalculate
On Feb 11, 1:25 pm, "Michael.Tarnowski" wrote:
On Feb 11, 12:18 pm, "Charles Williams" wrote: 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 "Michael.Tarnowski" wrote in message ... On Feb 11, 11:27 am, "Michael.Tarnowski" wrote: In a post in ExcelForum (http://www.excelforum.com/excel-prog...6-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-prog...tions-wont-cal... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts recalculate to 100% | Charts and Charting in Excel | |||
formulas won't recalculate | Excel Worksheet Functions | |||
Recalculate changes only | Excel Discussion (Misc queries) | |||
Won't recalculate | Excel Discussion (Misc queries) | |||
will not recalculate | Excel Worksheet Functions |