Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
I have a worksheet that calls some custom macros.
When I change the macro, the cells don't recalculate. How can I tell Excel to recalculate all of the cells in the worksheet? -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
are you turning calculation off with the macro or is it set to manual?
this will set it to automatic, add it to the end of your code and see if it helps application.Calculation = xlautomatic -- Gary "LurfysMa" wrote in message ... I have a worksheet that calls some custom macros. When I change the macro, the cells don't recalculate. How can I tell Excel to recalculate all of the cells in the worksheet? -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
You could always create another macro that recalculates everything
or even just show the immediate window in the VBE (ctrl-g). Then type this and hit enter: application.calculatefull There are different levels of recalculating. Look in VBA's help for more info. (And some have been added since xl97.) LurfysMa wrote: I have a worksheet that calls some custom macros. When I change the macro, the cells don't recalculate. How can I tell Excel to recalculate all of the cells in the worksheet? -- -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
On Fri, 22 Jun 2007 21:13:11 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote: are you turning calculation off with the macro The macro is 1 line of code to calculate an exponential function. Here it is: Public Function DSExp( _ ByVal x As Double, _ ByVal Xa As Double, _ ByVal Ya As Double, _ ByVal Yb As Double, _ ByVal h As Double) _ As Double DSExp = Yb + (Ya - Yb) * Exp((-Log(2) / h) * (x - Xa)) 'Note: In VBA, Log() is the natural log, Log10 is the base 10 log. ' In Excel, Ln() is the natural log and Log() is the base 10 log. End Function or is it set to manual? Calculation is set to Automatic. this will set it to automatic, add it to the end of your code and see if it helps application.Calculation = xlautomatic The macro isn't even getting called. I set a break point to see for sure. So I can't see how this will help. I added it, but no difference. Here's what's weird. If I click on the calculate now icon (this is Excel 2007), nothing happens. But if I click on the cell and press F2, then tab, the calculation is performed, the breakpoints in my macros happen, and the new values appear. How come "Calculate now" doesn't calculate now? -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
On Fri, 22 Jun 2007 20:17:58 -0500, Dave Peterson
wrote: You could always create another macro that recalculates everything or even just show the immediate window in the VBE (ctrl-g). Then type this and hit enter: application.calculatefull That did it. Now how come that works, but clicking the Calculate Now icon (Excel 2007) doesn't? I don't want to always be opening up the IDE to enter that in the Immediate window. I suppose I could write a recalculate macro, as you suggest, but isn't thay what Calculate Now is supposed to do? There are different levels of recalculating. Look in VBA's help for more info. (And some have been added since xl97.) LurfysMa wrote: I have a worksheet that calls some custom macros. When I change the macro, the cells don't recalculate. How can I tell Excel to recalculate all of the cells in the worksheet? -- -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
I figure that if you're in the VBE making changes to the the UDF (that's what
you meant in your original post, right???), then it's just as easy to force the calculation while you're there. But if you look at the different ways to force calculation in Excel's help, you'll see something like this (taken from xl2003's help): Press F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic calculation, you do not need to press F9 for calculation. Press SHIFT+F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet. Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not. Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not. ====== I didn't look in xl2007's help file, but you can. Then you can test to see which set of keystrokes recalculates for you. LurfysMa wrote: On Fri, 22 Jun 2007 20:17:58 -0500, Dave Peterson wrote: You could always create another macro that recalculates everything or even just show the immediate window in the VBE (ctrl-g). Then type this and hit enter: application.calculatefull That did it. Now how come that works, but clicking the Calculate Now icon (Excel 2007) doesn't? I don't want to always be opening up the IDE to enter that in the Immediate window. I suppose I could write a recalculate macro, as you suggest, but isn't thay what Calculate Now is supposed to do? There are different levels of recalculating. Look in VBA's help for more info. (And some have been added since xl97.) LurfysMa wrote: I have a worksheet that calls some custom macros. When I change the macro, the cells don't recalculate. How can I tell Excel to recalculate all of the cells in the worksheet? -- -- -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force all of the cells in a worksheet to recalculate?
On Fri, 22 Jun 2007 21:07:08 -0500, Dave Peterson
wrote: I figure that if you're in the VBE making changes to the the UDF (that's what you meant in your original post, right???), then it's just as easy to force the calculation while you're there. But if you look at the different ways to force calculation in Excel's help, you'll see something like this (taken from xl2003's help): Press F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic calculation, you do not need to press F9 for calculation. Press SHIFT+F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet. Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not. This one did it. Thanks. Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Force wait until recalculate | Excel Programming | |||
How to recalculate a worksheet when a selection is made | Excel Programming | |||
How do I force Excel to use fixed cells on a different worksheet? | New Users to Excel | |||
recalculate cells | Excel Worksheet Functions | |||
Can I only recalculate a part of a worksheet? | Excel Programming |