A function to replace all the application.screenupdating references

[jeffreybrown]

Is there a method which could replace all of the numerous places in code in-which the code below is used? Maybe some type of function which is called instead of repeating the same code over and over or maybe this is the best method.

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With

Edit: Sorry posted in the wrong forum

[Paul]

Jeff,

You should be able to create a standard module and in it create a Public Sub with that code inside. You can then call that Sub from any worksheet within the workbook.

[jeffreybrown]

Hi Paul,

Since I am still learning the ropes of VBA, is this a good method or should you stick within each procedure or is it just preference?

[Paul]

Generally if you repeat steps multiple times it is cleaner to have one instance of it, then call it when necessary. It does save a little memory. Normally it would be used for repetitive functions or more drawn-out code, but it will work in this instance just as in any other.

[shg]

Maybe you could use somthing like this, Jeff. It maintains a stack of states so subs can push their desired state and then pop to return to the state when they were called. You can skip arguments that you don't care about.

Minimally tested.

Option Explicit

Public Enum AppMode
    Pop
    Reset
    Push
End Enum

Sub demo()
    ' set calculation to manual and screen updating to false
    AppOnOff Push, iCalc:=xlCalculationManual, tsScreen:=vbFalse
    ' do stuff
    AppOnOff Pop
End Sub

Sub AppOnOff(iMode As AppMode, _
             Optional iCalc As XlCalculation = 0, _
             Optional tsAlerts As VbTriState = vbUseDefault, _
             Optional tsEvents As VbTriState = vbUseDefault, _
             Optional tsScreen As VbTriState = vbUseDefault)
    ' iMode     Action
    '   -1      Pop to last state
    '    0      Reset state, everything on
    '    1      Push current state, set new states

    Static col      As Collection
    Dim vSta        As Variant

    If col Is Nothing Then Set col = New Collection

    With Application
        Select Case iMode
            Case Pop
                If col.Count Then
                    vSta = col.Item(col.Count)
                    col.Remove col.Count
                    
                    .Calculation = vSta(0)
                    .DisplayAlerts = vSta(1)
                    .EnableEvents = vSta(2)
                    .ScreenUpdating = vSta(3)
                End If

            Case Reset
                Set col = New Collection
                .Calculation = xlCalculationAutomatic
                .DisplayAlerts = True
                .EnableEvents = True
                .ScreenUpdating = True

            Case Push
                col.Add VBA.Array(.Calculation, .DisplayAlerts, .EnableEvents, .ScreenUpdating)
                
                If iCalc <> 0 Then .Calculation = iCalc
                If tsAlerts <> vbUseDefault Then .DisplayAlerts = tsAlerts
                If tsEvents <> vbUseDefault Then .EnableEvents = tsEvents
                If tsScreen <> vbUseDefault Then .ScreenUpdating = tsScreen
        End Select
    End With
End Sub

[jeffreybrown]

Thanks Paul/shg,

@shg...I couldn't quite get your code to cooperate but I will keep trying. In the meantime I have run across this...

Option Explicit
    Public booScrnUpdt As Boolean
    Public booEnabEvnt As Boolean
    Public booDispAlrt As Boolean
'    Public lngCalcMode As XlCalculation

Sub DoingStuff()
    Call AppStateFalse
    Sheets("Sheet2").Range("A1").Copy Destination:=Sheets("Sheet2").Range("A2")
    Call AppStateRestore
End Sub

Sub AppStateFalse()
    With Application
        Let booScrnUpdt = .ScreenUpdating
        Let booEnabEvnt = .EnableEvents
        Let booDispAlrt = .DisplayAlerts
'        Let lngCalcMode = .Calculation
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
'        .Calculation = xlCalculationManual
    End With
 End Sub
 
 Sub AppStateRestore()
    With Application
        .ScreenUpdating = booScrnUpdt
        .EnableEvents = booEnabEvnt
        .DisplayAlerts = booDispAlrt
'        .Calculation = lngCalcMode
    End With
End Sub

So far with my testing I have not seen any problems but from your experience do you see this as a sound method?

I got the idea from here

[DonkeyOte]

col.Add VBA.Array(.Calculation, .DisplayAlerts, .EnableEvents, .ScreenUpdating)

@shg, can you outline the use of VBA qualifier in the above ?

I've seen Colin using the VBA qualifier of late and presume this is for good reason but have found little info. on it.

[shg]

@ DO: VBA.Array always generates a zero-based array, even if the user has Option Base 1.

@ Jeffrey: The potential problem is that (a) AppStateFalse always sets each of the properties "off" when that may not be exactly what you want, and (b) you must alternately call AppStateFalse and AppStateRestore or else you lose the state. Mine was intended to solve the case where nested subs may want to set their environment during execution and then restore it on return to the calling routine.

Sub bob()
    AppOnOff Push, iCalc:=xlCalculationManual, tsScreen:=vbFalse
    ' do stuff
    call joe
    AppOnOff Pop
End Sub

Sub joe()
    AppOnOff Push, ...
    ' do stuff
    AppOnOff Pop
end sub

What problems are you having? As I said, it got only a two-minute test from me.

[jeffreybrown]

Hi again shg,

No matter what combination I run for the AppOnOff Push, I get the MsgBox which says "Oops!". Any ideas why, but in the end everything is restored to the previous state.

[shg]

I trust you mean for Pop.

Sorry, corrected in prior post.

[jeffreybrown]

Works grand