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.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion