Calculation frequency on one worksheet

[Steph77]

Hi all

Browsing through a few threads to find a solution to one problem with our sheets at work, I had an idea about another one but I can't find anything through googling about it.

Lets say I have 2 visible sheets, "Data" and "Report"

"Report" has been built really badly by my predecessor, but I'm not allowed to change it (can I hear you say "frustrating!" I wonder? ) It has close to 50 million calculations (mostly COUNTIFS with 4-8 criteria referencing entire columns on the "Data" sheet against cells on a hidden third worksheet) which run every time the worksheet calculates.

I would like the sheet users to be able to use the Data sheet without the constant 20-30 second delays caused by the Report sheet recalculating, but I need to leave automatic calculations turned on because they do use some formulas on the data sheet as part of their work and when I tried recommending they switch to manual I was met with heavy resistance.

The only way I can think of doing this is to have a macro that each time any sheet other than the "Report" sheet is selected it replaces all the "=" on the "Report" sheet with "@" or "#" or something else harmless that breaks the formulas, and then every time the "Report" sheet itself gets selcted those "=" get put back in and it recalculates that sheet only at that point.

Is this an efficient way to do this or is there a better way? I'm happy enough to experiment with writing the code myself (it's how I learn so I don't want to be spoon-fed), I just want to check I'm not missing some easier way of achieving the same goal before I do so.

 

[jasonb75]

As you're not permitted to change the sheet, it's probably the most realistic approach, but maybe not the safest.

One thing that you need to be sure of is that your chosen 'harmless' character is not already in use anywhere in the report sheet, for example, if you used @ and your report sheet contains anything like an email address then that will be broken when you change @ back to =.

If your employer has an environment friendly policy then you could try to convince them that the excessive processing required by the existing formula is contributing heavily to global warming.

 

[Steph77]

Yeah I might use a longer string, so replace "=" with "####" as it's highly doubtful "####" is anywhere else on that report sheet nor would it be added there later

 

[jasonb75]

You could use Ctrl f to check the sheet for the character you're going to use. £ might be worth considering.

 

[Steph77]

I checked for "####" and it's nowhere so I think I'll use that

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveSheet.Name = "Report" Then
        Sheets("Report").Range("A1:BA500").Replace What:="####", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows
    Else
        Sheets("Report").Range("A1:BA500").Replace What:="=", Replacement:="####", LookAt:=xlPart, SearchOrder:=xlByRows
    End If
End Sub

I wrote that which from all I can see should check the worksheet name and run the find replace TO "=" if the report file is active and the find replace TO "####" when it's any other sheet, but nothing is happening when I click back and forth between sheets. I'm confused...

 

[jasonb75]

Worksheet change is used to run the code when the value of a cell is changed.

You need to use the Activate and Deactivate events.

 

[Steph77]

OK changed it to this:

Private Sub Worksheet_Activate()

Application.DisplayAlerts = False

    If ActiveSheet.Name = "Report" Then
        Sheets("Report").Range("A1:BA500").Replace What:="####", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows
        MsgBox "Report has been fixed"
    Else
        Sheets("Report").Range("A1:BA500").Replace What:="=", Replacement:="####", LookAt:=xlPart, SearchOrder:=xlByRows
        MsgBox "Report has been broken"
    End If

Application.DisplayAlerts = True

End Sub

Added the message boxes in in case it was working and just going too fast for me to tell it was working (doubtful but my home gaming rig is a lot more powerful than my crappy work laptop)

Still getting nothing happening - I've tried putting the code into the "ThisWorkbook" section and into the individual sheets code and nothing, nada, zilch

Nights like these are when I hate learning VBA - everything I can find online agrees with this syntax and setup....

 

[jasonb75]

It can be quick, but should be noticeable, that said, the sheet should recalculate when you 'fix' it anyway so you would still get the usual long delay.

The code needs to be entered in the worksheet module for the report sheet,

Private Sub Worksheet_Deactivate()
    Me.Range("A1:BA500").Replace What:="=", Replacement:="####", LookAt:=xlPart, SearchOrder:=xlByRows
    MsgBox "Report has been broken"
End Sub

Private Sub Worksheet_Activate()
    Me.Range("A1:BA500").Replace What:="####", Replacement:="=", LookAt:=xlPart, SearchOrder:=xlByRows
    MsgBox "Report has been fixed"
End Sub

If it has noticeable delay then there are things that might speed things up a bit.

 

[Steph77]

Found the problem

Private Sub Workbook_Open()

Application.EnableEvents = True

End Sub

I put that in the "ThisWorkbook", closed and reopened the file and it ran.

Not sure why it was set to EnableEvents = False to start with but it's now running fine (now just gotta convince the boss, who constantly resists adding macros to things just because they "don't like automation" - yet they're happy to allow the construction of spreadsheets with hundreds or thousands of formulas calculating report figures.... go figure....)

Thanks JasonB75 for your help with this

 

[jasonb75]

That sounds like an error, it might work one way, but I doubt it will both break and fix.

Workbook open is an event so if enableevents was false then it wouldn't have run when you opened the workbook.