Logical Test to determine sheet calculation

**AndyGW** · 05-13-2017, 10:30 AM

Is there a way to test if Excel has recalculated when using Manual Calculation?

I have a fairly large, and somewhat slow sheet which requires a large amount of data entry that is slowed down by excel recalculating after every entry. I've switched to manual calc on F9, which makes things much faster and far more user friendly, but I'd like to add a flag which notifies the user when the sheet has not been recalculated.

I wrote the thing and even I often look at a value thinking there's an error when in fact the sheet just requires recalculating.

So is there something like =IF(CALC=0,"Sheet Requires Recalculation","Sheet is calculated!")?

**Richard Buttrey** · 05-13-2017, 12:19 PM

Hi,

One way would be a Sheet Change macro that updated a cell - say A1.
The cell could be conditionally formatted with the font colour the same as the background unless the cell says "Manual"

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Calculation = xlCalculationManual Then

        Application.EnableEvents = False
        Range("G1") = "MANUAL"
    Else
        Application.EnableEvents = False
        Range("G1") = ""
    End If
    Application.EnableEvents = True
End Sub

However rather than trying to work around a slow worksheet are you sure you know why it's calculating slowly and have you taken other steps to avoid this? What sort of stuff is in your sheet? i.e. how many formulae or cond. formats and how complex are any formula. Is filtering involved anywhere?

**Jacc** · 05-13-2017, 03:37 PM

How about this cumbersome and fail-prone spectacle?
Try to enter values in any cell and then switch between manual and automatic calculation and enter some values again. Test it a bit back and forth.