⚙️ VBA RUNE DIFFICULTY: Adept Structural

The Flyweight Pattern in VBA

Minimize the memory footprint of massive armies by sharing the intrinsic state of your ethereal constructs.

✧ GANG OF FOUR LINEAGE & ARCANUM ✧
GOF DESIGN PATTERN: Flyweight
ARCANUM SCHOOL: Transmutation // Spreadsheet Necromancy
SCRIBE / ARCHON: Joshua Edward McLaughlin Cox
CANONICAL REGISTRATION:
// VBA PATTERN //
' Class: SharedCellFormat (Class Module) - The Flyweight
Public FontColor As Long
Public BackgroundColor As Long

Public Sub ApplyTo(target As Range)
    target.Font.Color = FontColor
    target.Interior.Color = BackgroundColor
End Sub

' Class: FormatFactory (Class Module) - The Flyweight Factory
Private pCache As Object

Private Sub Class_Initialize()
    Set pCache = CreateObject("Scripting.Dictionary")
End Sub

Public Function GetFormat(key As String) As SharedCellFormat
    If Not pCache.Exists(key) Then
        Dim newFormat As New SharedCellFormat
        If key = "Cursed" Then
            newFormat.FontColor = RGB(255, 0, 0)
            newFormat.BackgroundColor = RGB(0, 0, 0)
        Else
            newFormat.FontColor = RGB(0, 0, 0)
            newFormat.BackgroundColor = RGB(255, 255, 255)
        End If
        pCache.Add key, newFormat
    End If
    Set GetFormat = pCache(key)
End Function

' Client
Public Sub PaintArmy()
    Dim factory As New FormatFactory
    Dim cursedFmt As SharedCellFormat
    Set cursedFmt = factory.GetFormat("Cursed")
    
    ' Apply the shared format to thousands of cells without duplicating the object
    cursedFmt.ApplyTo Sheet1.Range("A1")
    cursedFmt.ApplyTo Sheet1.Range("B2")
End Sub

The Flyweight: The Collective Unconscious

When raising an army of millions of rows, instantiating a full formatting object for each row will inevitably drain the available memory, crashing the Excel host in a spectacular out-of-memory death spiral.

The Flyweight pattern mitigates this by sharing common, intrinsic data. Instead of each cell holding its own color values, they reference a shared SharedCellFormat object served by a FormatFactory. It binds the horde to a collective unconscious, drastically reducing the ritual’s footprint and preventing catastrophic failure.

Runic Tags: #vba #flyweight #structural #spreadsheet-necromancy