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?

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

<(E-Mail Removed)> wrote in message
news:fe9f2e40-c91d-4994-88c8-(E-Mail Removed)...
>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?