stopping a range from calculating

[Xt]

Hi folks.
I'm in Excel 2007. I have written a macro which accesses values from
a sheet, does things and pastes results back onto that same sheet.
This sheet gets data from another sheet "Data". Column A in Data is
generating random numbers. What I would like to do is be able to stop
and start the calculations in column A in the Data sheet from the
macro at suitable times.
This sounds simple. Can it be done?
Thanks
xt

 

[Vacuum Sealed]

Hi xt

not to sure if you can halt autocalcs on specific rows, columns or
individual cells,.

you can control wheter the woorkbook itself does or does not autocalc.

you could have let's say 2 cmdbtns, one = off, the other = on

Sub Calc_Off()
With Application
.Calculation = xlManual
End With
End Sub

Sub Calc_On()
With Application
.Calculation = xlAutomatic
End With
End Sub

You could also place something in the

Private Sub Workbook_Open()

With Application
.Calculation = xlManual
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.Calculation = xlAutomatic
End With
End Sub

HTH
Mick.

 

[GS]

Xt submitted this idea :

Hi folks.
I'm in Excel 2007. I have written a macro which accesses values from
a sheet, does things and pastes results back onto that same sheet.
This sheet gets data from another sheet "Data". Column A in Data is
generating random numbers. What I would like to do is be able to stop
and start the calculations in column A in the Data sheet from the
macro at suitable times.
This sounds simple. Can it be done?
Thanks
xt

Mick's suggestion would be the first approach, but turning calc off
also prevents all other cells from updating. If you need those to calc
as your macro returns values, you can turn calc OFF and just update
$A:$A when needed.

Example:

Range("$A:$A").Calculate

OR, if you need calc turned ON you might be able to use a workaround...

Put the ColA formulas in a hidden column as Text (ie: format the cells
as 'Text').

In your macro, ClearContents of $A:$A prior to running the code that
changes values. Afterwards, update $A:$A with the hidden formulas.

Example:

With Range("$A:$A")
.ClearContents

'//code to return values here

.Formula = Range("HiddenFormulas").Value
End With 'Range("$A:$A")


Alternatively, you could load the data into an array and process the
array, then 'dump' the data back into the worksheet in one shot. Not
sure this scenario will work for you but it will allow you to process
the data without affecting any worksheet data during the process. It
would also be considerably faster than reading/writing to the worksheet
for each piece of data being processed.

 

[GS]

Typo...

Mick's suggestion would be the first approach, but turning calc off also

prevents all other cells from updating. If you don't need those to calc
as your

 

[joeu2004]

I have written a macro which accesses values from
a sheet, does things and pastes results back onto that same
sheet. This sheet gets data from another sheet "Data".
 Column A in Data is generating random numbers.  What I would
like to do is be able to stop and start the calculations in
column A in the Data sheet from the macro at suitable times.
This sounds simple.  Can it be done?

Yes, if I understand you correctly. First, you should bracket your
code with the following:

Dim oldCalc
oldCalc = Application.Calculation
Application.Calculation = xlCaculationManual
.... your code...
Application.Calculation = oldCalc

Now, within your code, when you want to generate a new set of random
values in Data!A:A, you can write:

Sheets("data").Range("a:a").Calculate

 

[Xt]

Yes, if I understand you correctly.  First, you should bracket your
code with the following:

Dim oldCalc
oldCalc = Application.Calculation
Application.Calculation = xlCaculationManual
... your code...
Application.Calculation = oldCalc

Now, within your code, when you want to generate a new set of random
values in Data!A:A, you can write:

Sheets("data").Range("a:a").Calculate

Thanks a lot for all that advice. With your help I've got things
going as I want.

Cheers, xt