The Facade Pattern in VBA
Construct a simplified monolithic gateway to hide the horrifying complexity of the underlying subsystem.
✧ GANG OF FOUR LINEAGE & ARCANUM ✧
GOF DESIGN PATTERN: Facade
ARCANUM SCHOOL: Abjuration // Spreadsheet Necromancy
SCRIBE / ARCHON: Joshua Edward McLaughlin Cox
CANONICAL REGISTRATION:
// VBA PATTERN //
' Subsystem 1: ScreenUpdatingDaemon (Class Module)
Public Sub Freeze()
Application.ScreenUpdating = False
End Sub
Public Sub Unfreeze()
Application.ScreenUpdating = True
End Sub
' Subsystem 2: CalculationWraith (Class Module)
Public Sub Sleep()
Application.Calculation = xlCalculationManual
End Sub
Public Sub Awaken()
Application.Calculation = xlCalculationAutomatic
End Sub
' Facade: EnvironmentRitual (Class Module)
Private pScreen As New ScreenUpdatingDaemon
Private pCalc As New CalculationWraith
Public Sub EnterDarkTrance()
pScreen.Freeze
pCalc.Sleep
Application.EnableEvents = False
End Sub
Public Sub ExitDarkTrance()
Application.EnableEvents = True
pCalc.Awaken
pScreen.Unfreeze
End Sub
' Client
Public Sub PerformHeavySorcery()
Dim env As New EnvironmentRitual
env.EnterDarkTrance
' Perform massive grid mutations here
env.ExitDarkTrance
End Sub
The Facade: The Monolithic Gateway
The Excel Object Model is a chaotic labyrinth. Modifying screen updating, calculation modes, and event handling directly in every macro is a recipe for leaving the application in a permanently cursed state if an error occurs.
The Facade pattern provides a simplified, unified interface to this complexity. The EnvironmentRitual class hides the low-level application state management. The necromancer simply invokes EnterDarkTrance to optimize performance before a massive data mutation, and ExitDarkTrance to restore the natural order. It is a protective ward against chaos.
Runic Tags:
#vba
#facade
#structural
#spreadsheet-necromancy