CODEWITHSUNDEEP
excelvba
moodymudskipper
moodymudskipper

Reputation: 47350

Excel Automatic Calculation at the sheet level only

I have an excel sheet that became very slow. For some reason, wherever I am in the document, if I set Calculation to manual and refresh the current sheet after any change, it is fast enough and serves my purpose.

This is not very comfortable however.

I would like the current sheet (and not the whole document) to be refreshed whenever a cell is changed. This should be done whatever sheet I'm on. How can I do that?

EDIT: let it be clear that I'm not asking for clues on how to make my workbook faster, it was just contextual info. I'm interested in autorefresh only.

Upvotes: 0

Views: 9206

Answers (3)

moodymudskipper
moodymudskipper

Reputation: 47350

As the provided answers don't fully satisfy me here is my own, borrowing from their appreciated help. It's possible that I've missed some nuance from teylyn's answer. I'll gladly edit if it's relevant.

There doesn't seem to be a way to handle change events at the APPLICATION level.

So all changes must be handled at the SHEET level.

This can be done by copying and pasting this bit of code in EVERY Sheet object of the workbook (and remember to copy and paste it it whenever you add a new sheet):

Private Sub worksheet_change(ByVal Target As Range)
    Me.Calculate
End Sub

However because of the tedious copy pastes we had to do, we may want to turn this feature off easily without going back to each sheet object.

We may also want to avoid entering a loop of hell if we do mass change on one sheet (say through a macro, a drag & drop, or a ctrl+H).

To do this we add a new sheet called WS_refresh where we set 3 values :

  • in A2: Yes/No to enable/disable our new feature
  • in A4: time of latest refresh
  • in A6: min interval to allow refresh, in sec (I've set it to 1)

Now when we change values in several cells in a short amount on time, autorefresh will only work on the first change, avoiding previously mentioned loops of hell. If you like to live dangerously set A6 to 0.

This is what should be copied and pasted in every sheet object:

Private Sub worksheet_change(ByVal Target As Range)
auto_refresh = ThisWorkbook.Worksheets("WS_refresh").Range("A2")
If auto_refresh = "Yes" Then
    last_refresh = ThisWorkbook.Worksheets("WS_refresh").Range("A4")
    refresh_interval_sec = ThisWorkbook.Worksheets("WS_refresh").Range("A6")
    refresh_interval_tv = TimeValue("0:00:" & refresh_interval_sec)
    If Application.Calculation = xlCalculationManual And Now() > last_refresh + refresh_interval_tv Then
        ThisWorkbook.Worksheets("WS_refresh").Range("A4") = Now()
        Me.Calculate
    End If
End If
End Sub

Upvotes: 0

HedgePig
HedgePig

Reputation: 468

I believe the Calculation event can happen at the Application, Worksheet or Range level (but not the Workbook level.)

If you have calculation mode set to manual but want the current worksheet (only) updated when you enter something, you could add a worksheet change event (putting the code in the relevant sheet.)

Private Sub Worksheet_Change(ByVal ChangedRng As Range)
    ChangedRng.Worksheet.Calculate
End Sub

This will recalculate just the worksheet and not the rest of the Workbook or any other workbooks.

Upvotes: 0

teylyn
teylyn

Reputation: 35990

Charles Williams has extensive information, techniques and code on calculation on his website http://decisionmodels.com. Quoting from this page:

Another method uses the worksheet.enablecalculation property. When this property is changed all the formulae on the worksheet are flagged as uncalculated, so toggling the property to false and then back to true for all sheets in all open workbooks will cause the next calculation to be a full calculation.

> Dim oSht as worksheet Application.Calculation=xlCalculationManual
> 
> for each oSht in Worksheets oSht.enablecalculation=false
> osht.enablecalculation=true next osht
> 
> Application.calculate

You can also use this method for a single worksheet to do a full sheet calculate.

You can easily work this technique into the Worsheet_Change event, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.Calculation = xlCalculationManual

Me.EnableCalculation = False
Me.EnableCalculation = True
Me.Calculate

End Sub

This will do a full calculation of the current sheet only and leave all other sheets uncalculated.

Upvotes: 1

Related Questions