I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

SystemState

"Any sufficiently advanced technology is indistinguishable from magic." [Arthur C. Clarke]

My friend and former colleague Jon T. created the smallest reasonable class module I have seen so far: SystemState provides an easy way to save and to restore system state variables like Calculation, ScreenUpdating and others.

Normally you write at the beginning of a VBA macro
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

and at the end of a macro
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

With the class module SystemState you just write at the start
Dim state As SystemState
Set state = New SystemState

and at the end
Set state = Nothing 'Not even necessary - will be done automatically

[But remember: You cannot abbreviate this to "Dim state as New SystemState"!]

You can control a whole bunch of system state variables:

Variable

States

Comment (speed up with ...)

Calculation

xlCalculationAutomatic
xlCalculationManual
xlCalculationSemiautomatic

Decides whether a recalc is done after each change to any cell. Set to xlCalculationManual

Cursor

xlDefault
xlIBeam
xlNorthwestArrow
xlWait

This is just user info. Set to xlWait (hourglass) if you like - but only after having tested your code thoroughly - it is quite annoying to start debugging with an hourglass cursor

DisplayAlerts

True
False

If your macro knows what to do it is quite annoying if the system asks you whether you want to overwrite an existing file, for example. Set to False to switch off

EnableEvents

True
False

Set to False to prevent event procedures from being run

Interactive

True
False

If set to False all keyboard input will be blocked. Dangerous

ScreenUpdating

True
False

Set to False to prevent screen from updating during run

StatusBar

False
"Any user info you like"

Text will show up at status bar (bottom line). Cleared if set to False

SystemState_01_Code

If you are interested in downloading a 27 KB Excel 2007 sample file go to my Download page, please.

A simple and naive speedup approach which does not store the system states you can find here.

SystemState_02_Code
SystemState_03_Code
SystemState_04_Code
SystemState_05_Code
Sulprobil   Get it done   Contact   Disclaimer   Download