ApplicationState

Holds a copy of the Excel Application settings a long operation changes, and puts them back when it ends. Create binds an Application and takes the copy straight away. ApplyBusyState turns screen updating, alerts and automatic calculation off, and on request also silences events, forces the automation security level and sets the cursor. Restore writes every saved value back. IsBusy and HasSnapshot answer where the object stands.

THE COPY IS TAKEN ONCE

ApplyBusyState restores from the copy the object already holds, and takes no new one. RefreshSnapshot takes a fresh copy, which is what a caller that changed a setting between two busy blocks calls first. ApplyBusyState does nothing when the scope is already busy; a caller that needs the settings written again inside one scope passes force:=True, and the copy still holds the values the scope opened on.

THE SETTINGS THIS CLASS COVERS

ScreenUpdating, DisplayAlerts, Calculation, EnableAnimations, EnableEvents, AutomationSecurity, the mouse cursor, CalculateBeforeSave and FormatStaleValues. EnableAnimations and FormatStaleValues are missing on some hosts, and the object records whether each is there.

FORMATSTALEVALUES IS THE ONE SETTING NEVER PUT BACK

Stale value formatting greys out every cell a manual calculation has left behind, which in a linelist on manual calculation is most of the sheet. The workbooks want it off from the moment they open until the moment they close, so both ApplyBusyState and Restore write False and neither reads the snapshot back. It is an Application setting, so a second workbook closing in the same session turns it back on for the one still open; forcing it here means the next operation of that workbook puts it out again.

Depends on: ProjectError

Version: 1.0 (2026-02-09)

Factory

Create #

create

Factory returning an initialised interface

Signature:

Public Function Create(Optional ByVal excelApplication As Application) As ApplicationState

Captures the current application state immediately so callers can switch to busy mode without an additional call.

Parameters:

  • excelApplication: Application. Target application. Defaults to Excel.Application.

Returns: ApplicationState. Ready to use.

Core Logic

ApplyBusyState #

apply-busy-state

Apply the busy state to the target application

Signature:

Public Sub ApplyBusyState(Optional ByVal suppressEvents As Boolean = False, _
                               Optional ByVal calculateOnSave As Boolean = True, _
                               Optional ByVal busyCursor As Long = 0, _
                               Optional ByVal blockSecurity As Boolean = False, _
                               Optional ByVal force As Boolean = False)

Switches the target Excel Application into a locked-down mode for heavy operations: disables screen updating, alerts, automatic calculation, and optionally suppresses events and blocks automation security. Does nothing when the scope is already busy. On failure the busy flag is cleared and a ProjectError is raised so the caller can attempt recovery. The snapshot restored later is the one this object already holds; it belongs to the whole life of the object and is NOT re-taken here. A caller that changed a setting on purpose between two busy blocks must ask for a new snapshot with RefreshSnapshot, otherwise the next Restore puts the old value back.

Parameters:

  • suppressEvents: Optional Boolean. When True, disables Application.EnableEvents. Defaults to False.
  • calculateOnSave: Optional Boolean. Value assigned to CalculateBeforeSave. Defaults to True.
  • busyCursor: Optional Long. Cursor shown while busy. When 0 (default), leaves cursor unchanged.
  • blockSecurity: Optional Boolean. When True, forces msoAutomationSecurityForceDisable. Defaults to False.
  • force: Optional Boolean. When True, writes the settings again even though the scope is already busy. Defaults to False. Excel puts screen updating back on by itself on some hosts -- activating a worksheet is the known case -- and a caller that has to re-assert the busy state in the middle of one scope passes True. The snapshot is untouched, so Restore still answers the values the scope opened on.

Throws:

  • ProjectError.ErrorUnexpectedState When the application settings cannot be applied.

RefreshSnapshot #

refresh-snapshot

Re-capture the snapshot from the current application state

Signature:

Public Sub RefreshSnapshot()

Throws:

  • ProjectError.ErrorUnexpectedState When the scope is busy.

Restore #

restore

Restore all captured settings to the application

Signature:

Public Sub Restore(Optional ByVal silent As Boolean = False)

Parameters:

  • silent: Optional Boolean. When True, swallows errors instead of raising them. Defaults to False.
Internal members (not exported)

Introspection

IsBusy #

is-busy

Whether the busy state is currently active

Signature:

Public Property Get IsBusy() As Boolean

Returns: Boolean. True when ApplyBusyState has been invoked without a matching Restore.

HasSnapshot #

has-snapshot

Whether a snapshot exists and restoration is possible

Signature:

Public Property Get HasSnapshot() As Boolean

Returns: Boolean. True when a snapshot has been captured.

Internal Workflow

BindApplication #

bind-application

Bind a target application

Signature:

Friend Sub BindApplication(ByVal excelApplication As Application)

Parameters:

Core Logic

CaptureSnapshotCore #

capture-snapshot-core

Capture all application settings into the snapshot

Signature:

Private Sub CaptureSnapshotCore()

TargetApplication #

target-application

Resolve the Excel Application reference

Signature:

Private Function TargetApplication() As Application

Returns: Application. The guarded Application instance.

EnsureSnapshot #

ensure-snapshot

Guard that a snapshot exists before proceeding

Signature:

Private Sub EnsureSnapshot()

Takes the snapshot only when the object does not have one yet. The snapshot belongs to the whole life of the object: once taken it stays until a caller asks for a new one with RefreshSnapshot.

EnsureNotBusy #

ensure-not-busy

Guard that the scope is not busy before proceeding

Signature:

Private Sub EnsureNotBusy(ByVal operationName As String)

Parameters:

SafeSetEnableAnimations #

safe-set-enable-animations

Set EnableAnimations with graceful fallback on unsupported platforms

Signature:

Private Sub SafeSetEnableAnimations(ByVal target As Application, ByVal value As Boolean)

Parameters:

SafeSetFormatStaleValues #

safe-set-format-stale-values

Set FormatStaleValues where the host has it

Signature:

Private Sub SafeSetFormatStaleValues(ByVal target As Application, ByVal value As Boolean)

The property arrived with the calculation engine of the newer hosts, and an older Excel raises on the write. The first raise records the capability as missing so the following operations stop trying.

Parameters:

TryReadFormatStaleValues #

try-read-format-stale-values

Attempt to read FormatStaleValues from the target application

Signature:

Private Function TryReadFormatStaleValues(ByVal target As Application, _
                                          ByRef value As Boolean) As Boolean

Parameters:

Returns: Boolean. True when the property was read successfully.

TryReadEnableAnimations #

try-read-enable-animations

Attempt to read EnableAnimations from the target application

Signature:

Private Function TryReadEnableAnimations(ByVal target As Application, _
                                         ByRef value As Boolean) As Boolean

Parameters:

Returns: Boolean. True when the property was read successfully.

ThrowError #

throw-error

Raise a typed project error

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal message As String)

Parameters:

Used in (31 file(s))