CaseWhen
Parses CASE_WHEN custom formulas written in setup expressions and rewrites them as nested Excel IF statements. The class splits the formula body into alternating condition/result pairs while respecting quoted segments and nested parentheses, then builds the equivalent nested IF expression. It also extracts textual category labels for downstream analysis consumers.
Depends on: BetterArray
Version: 1.0 (2026-02-09)
Factory
create #
Instantiate a CaseWhen parser bound to a formula
Signature:
Public Function Create(ByVal formula As String) As ICaseWhen
Trims the incoming formula, stores it in a new instance, and returns the ICaseWhen interface reference for fluent downstream consumption. No eager parsing occurs; the formula is parsed lazily on first access to Valid, ParsedFormula, or Categories.
Parameters:
formula: String. The CASE_WHEN expression to parse.
Returns: ICaseWhen. A ready-to-query parser instance.
Validation
valid #
Determine whether the stored formula is a CASE_WHEN
Signature:
Private Property Get Valid() As Boolean
Checks whether the stored formula matches the CASE_WHEN pattern. Accepts formulas that begin with the CASE_WHEN( token (case-insensitive) and contain content beyond the header. Returns False for empty strings or formulas that do not match the token.
Returns: Boolean. True when the formula is parseable.
Parsing
parsed-formula #
Convert CASE_WHEN into nested IF statements
Signature:
Private Function parsedFormula() As String
Core conversion and category extraction logic. Iterates through the argument table in pairs (condition, result) and builds Excel-compatible nested IF fragments. When the argument count is odd the final entry becomes the default branch; when even an empty string literal is appended as the fallback. Excel supports up to 64 nested IF calls.
Returns: String. Nested IF expression ready for worksheet consumption.
categories #
Extract textual result labels from the CASE_WHEN arguments
Signature:
Private Function Categories() As BetterArray
Evaluates each result expression (even-indexed entries in the argument table) and returns the literal values that will appear to end-users. Quoted entries are stripped of their quotes; non-quoted entries are wrapped in quotes for consistent downstream handling.
Returns: BetterArray. Collection containing quoted category labels.
Internal members (not exported)
PublicAccessors
self #
Current object instance
Signature:
Public Property Get Self() As ICaseWhen
Properties that expose the internal state and interface pointer. Returns the current instance cast to its interface. Used internally by the factory method to return an ICaseWhen reference.
Returns: ICaseWhen. Reference to the current object.
formula #
Retrieve the raw CASE_WHEN formula
Signature:
Public Property Get casewhenFormula() As String
Returns the original formula text as stored after trimming during assignment. This is the full CASE_WHEN(...) expression.
Returns: String. Original formula text.
formula-set #
Store the CASE_WHEN formula and reset cached data
Signature:
Public Property Let casewhenFormula(ByVal formula As String)
Trims the incoming formula and stores it, then invalidates the cached parsing table so subsequent reads trigger a fresh parse.
Parameters:
formula: String. Expression to analyse.
ParsingInfrastructure
case-when-table #
Retrieve the cached list of CASE_WHEN arguments
Signature:
Private Property Get CaseWhenTable() As BetterArray
Lazily computed and cached argument table. Splits the formula into alternating condition/result entries while respecting quoted segments and nested parentheses. The result is cached so subsequent accesses avoid reparsing the string. Returns a clone to prevent callers from mutating the cache.
Returns: BetterArray. The ordered CASE_WHEN arguments.
PrivateHelpers
reset-cache #
Reset cached artefacts when the source formula changes
Signature:
Private Sub ResetCache()
Internal utilities for cache management and formula segmentation. Sets the cached parsing table to Nothing so the next access to CaseWhenTable triggers a fresh parse from the stored formula.
sanitised-formula #
Remove the CASE_WHEN header and closing parenthesis
Signature:
Private Function SanitisedFormula() As String
Strips the leading CASE_WHEN( token and the trailing closing parenthesis to expose the raw comma-delimited argument body.
Returns: String. Inner content of the CASE_WHEN call.
split-formula-segments #
Split the CASE_WHEN body into trimmed argument segments
Signature:
Private Function SplitFormulaSegments(ByVal formulaBody As String) As BetterArray
Walks the formula body character by character, tracking quotation state and parenthesis depth. Splits on top-level commas only, preserving nested expressions and quoted strings intact.
Parameters:
formulaBody: String. The text between CASE_WHEN( and the final ).
Returns: BetterArray. Alternating condition and result expressions.
Interface
ICaseWhen_Valid #
Signature:
Private Property Get ICaseWhen_Valid() As Boolean
Delegated members satisfying the ICaseWhen contract.