Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to control recalculation in VBA?
I am having difficulty controlling worksheet recalculation within a
VBA macro when the worksheet contains a reference to a volatile function, namely RAND(). The following is a contrived example scaled down to the bare bones. It is not intended to be a practical example. It only serves to demonstrate my difficulty. Suppose F2 contains =RAND(). In a macro, I want to cause F2 (really the entire worksheet) to be recalculated. Then I want to copy the new F2 value to G2. That much is easy. But when I exit the macro, I want F2 and G2 to display the same value. Moreover, I want the worksheet to retain its calculation mode (automatic), whatever it was before executing the macro. I have tried various things. None leaves F2 and G2 displaying the same value. Here are couple of my failed attempts. sub testit1 state = application.calculation application.calculation = xlCalculationManual activesheet.calculate range("g2").value = range("f2") application.calculation = state end sub Problem: F2 is recalculated when I execute the last statement, if it restores xlCalculationAutomatic. sub testit2 activesheet.calculate activesheet.enableCalculate = false range("g2").value = range("f2") activesheet.enableCalculate = true end sub Problem: Again, F2 is recalculated when I execute the last statement. Any solutions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to control recalculation in VBA?
If Excel is in automatic mode then copying f2 to g2 will trigger a recalc
after the copy which will defeat your purpose. why not simply put a formula in G2 =F2 ? Then all you need to do is trigger a recalc Sub George() Range("F1").Calculate If Application.Calculation < xlCalculationAutomatic Then Application.Calculate End Sub Charles _________________________________________ FastExcel 2.3 Name Manager 4.0 http://www.DecisionModels.com wrote in message ... I am having difficulty controlling worksheet recalculation within a VBA macro when the worksheet contains a reference to a volatile function, namely RAND(). The following is a contrived example scaled down to the bare bones. It is not intended to be a practical example. It only serves to demonstrate my difficulty. Suppose F2 contains =RAND(). In a macro, I want to cause F2 (really the entire worksheet) to be recalculated. Then I want to copy the new F2 value to G2. That much is easy. But when I exit the macro, I want F2 and G2 to display the same value. Moreover, I want the worksheet to retain its calculation mode (automatic), whatever it was before executing the macro. I have tried various things. None leaves F2 and G2 displaying the same value. Here are couple of my failed attempts. sub testit1 state = application.calculation application.calculation = xlCalculationManual activesheet.calculate range("g2").value = range("f2") application.calculation = state end sub Problem: F2 is recalculated when I execute the last statement, if it restores xlCalculationAutomatic. sub testit2 activesheet.calculate activesheet.enableCalculate = false range("g2").value = range("f2") activesheet.enableCalculate = true end sub Problem: Again, F2 is recalculated when I execute the last statement. Any solutions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recalculation? | Excel Discussion (Misc queries) | |||
Recalculation | Excel Discussion (Misc queries) | |||
Recalculation | Excel Programming | |||
Too much recalculation | Excel Programming | |||
Recalculation | Excel Programming |