How to control recalculation in VBA?

[curiousgeorge408]

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?

 

[Charles Williams]

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