Calculations

[Guest]

I have a sheet with very heavy calculations. Is it possible to separate the
calculation in Excel, so that I can disable calculations in that sheet only.
Ie. I would like Excel to continue caluclating my sheets except for the one
with a lot of calculations in it.

 

[Bob Phillips]

Peter,

Not tried, but perhaps disable automatic calculate, and have a worksheet
change event that triggers calculate on other sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oWs As Worksheet

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each oWs In ThisWorkbook.Worksheets
If oWs.Name <> Me.Name Then
oWs.Calculate
End If
Next oWs

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

Peter,

You need to change all your formulas to something like

=IF($A$1,"",really complicated heavy formula)

Then, when you want to do the calcs on that sheet, enter FALSE into cell A1.

If you need to have the results available at all times, then you would need
to have a macro or event that stores the values somewhere else, and use

=IF($A$1, stored value cell, calculation)

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Hi Bernie,

Neat idea!

Biggest problem is that it makes the heavy calculations even heavier <G>).

Bob

Peter,

You need to change all your formulas to something like

=IF($A$1,"",really complicated heavy formula)

Then, when you want to do the calcs on that sheet, enter FALSE into cell A1.

If you need to have the results available at all times, then you would need
to have a macro or event that stores the values somewhere else, and use

=IF($A$1, stored value cell, calculation)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Bob,

Neat idea!

Thanks. I tried to get something to work with events, but couldn't figure
out how to do it. Of course, I had never even considered your method of
having calcs turned off and forcing calculations on _other_ sheets. Now, I'm
going to have to reconsider my design.

Biggest problem is that it makes the heavy calculations even heavier <G>).

That's why I no longer use my P133 machine ;-)

Bernie

Hi Bernie,



Bob