Sometimes you want to hide what you’re processing in VBA from the user. This code will help – call the SetUp module before your other code and CloseOut when you’re done.


Sub setUp()

‘Get current state of various Excel settings; put this at the beginning of your code
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks ‘note this is a sheet-level setting

‘turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
‘Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False ‘note this is a sheet-level setting

End Sub

… 


Sub closeOut()
'end your code

‘after your code runs, restore state; put this at the end of your code
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState ‘note this is a sheet-level setting
End Sub

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s