Hi Garry. Question. Is this different than what I currently do? I
have two modules, one that turns off application settings and one that
turns them back on, and I sandwich my code between the two modules.
i.e., first line in my sub is TurnOff and last line before End Sub is
TurnOn.


'Turn off application settings
Sub TurnOff()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.DisplayStatusBar = False
End With
End Sub

'Turn on application settings
Sub TurnOn()
With Application
.ScreenUpdating = True
.EnableEvents = True
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.DisplayStatusBar = True
End With
End Sub


On Wed, 11 Apr 2018 10:46:25 -0400, GS wrote:

Since I do table data analysis in a userform I never did check to see what
effect my EnableFastCode routine has on VBA processing of tables. Give that
tables do a lot of automatic recalcs, this will suspend all background
processes until your process is finished. You could give it a shot to see if it
speeds things up!

'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a standard
module.
'--------------------------------------------------------------------------------------
'Type udtAppModes
' 'Default types
' Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID
As String
' 'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As udtAppModes'
declarations

'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub

You use it like this:
Sub DoThis()
Const sSrc$ = "DoThis" '//name of procedure

EnableFastCode sSrc
'Do some overhead intensive stuff
EnableFastCode sSrc, False
End Sub

How it Works:
This is a concept-based methodology where the control belongs to only 1 Caller
until that caller is done its task. That means other Callers using the settings
can't inadvertently toggle them while another operation is using them.