⚙️ VBA RUNE DIFFICULTY: Adept Behavioral

The Command Pattern in VBA

Encapsulate a macro execution as a solitary object, enabling queues, logs, and undo functionality.

✧ GANG OF FOUR LINEAGE & ARCANUM ✧
GOF DESIGN PATTERN: Command
ARCANUM SCHOOL: Enchantment // Spreadsheet Necromancy
SCRIBE / ARCHON: Joshua Edward McLaughlin Cox
CANONICAL REGISTRATION:
// VBA PATTERN //
' Interface: ICommand (Class Module)
Public Sub Execute()
End Sub
Public Sub Undo()
End Sub

' Class: CursedWriteCommand (Class Module)
Implements ICommand
Private pTarget As Range
Private pPrevValue As Variant
Private pNewValue As Variant

Public Sub Init(target As Range, newValue As Variant)
    Set pTarget = target
    pPrevValue = target.Value
    pNewValue = newValue
End Sub

Private Sub ICommand_Execute()
    pTarget.Value = pNewValue
    pTarget.Interior.Color = RGB(255, 0, 0)
End Sub

Private Sub ICommand_Undo()
    pTarget.Value = pPrevValue
    pTarget.Interior.Pattern = xlNone
End Sub

' Client
Public Sub CommandInvocation()
    Dim cmd As New CursedWriteCommand
    cmd.Init Sheet1.Range("A1"), "Torment"
    
    ' The Invoker
    cmd.Execute
    
    ' Regret
    cmd.Undo
End Sub

The Command: The Spell Scroll

In VBA, executing a macro typically instantly mutates the Grid, destroying the built-in undo stack. This is a terrifying prospect for the uninitiated user who accidentally invokes a spreadsheet wipe.

The Command pattern turns an action into a standalone object. By implementing both Execute and Undo methods, and storing the previous state (the cell’s prior value and format) within the object itself, you create a Spell Scroll. These scrolls can be queued, logged, or reversed, granting you the ability to turn back time and undo the dark rituals cast upon the Grid.

Runic Tags: #vba #command #behavioral #spreadsheet-necromancy