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 #

create

Instantiate a CaseWhen parser bound to a formula

Signature:

Public Function Create(ByVal formula As String) As CaseWhen

Trims the incoming formula, stores it in a new instance, and returns the new instance 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: CaseWhen. A ready-to-query parser instance.

Validation

Valid #

valid

Determine whether the stored formula is a CASE_WHEN

Signature:

Public 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

parsedFormula #

parsed-formula

Convert CASE_WHEN into nested IF statements

Signature:

Public 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 #

categories

Extract textual result labels from the CASE_WHEN arguments

Signature:

Public 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)

Factory

Seal #

seal

Prevent further changes to setup-only properties

Signature:

Public Sub Seal()

Marks the instance as sealed so guarded setters raise when invoked after construction. Called by the factory before returning.

GuardNotSealed #

guard-not-sealed

Reject writes to setup-only properties after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Raises an error when a guarded setter is invoked on a sealed instance.

Parameters:

ThrowError #

throw-error

Raise a project-specific error

Signature:

Private Sub ThrowError(ByVal errNumb As Long, ByVal errorMessage As String)

Wrapper around Err.Raise that standardises the source to "CaseWhen".

Parameters:

Throws:

PublicAccessors

casewhenFormula #

formula

Retrieve the raw CASE_WHEN formula

Signature:

Public Property Get casewhenFormula() As String

Properties that expose the internal state and interface pointer. Returns the original formula text as stored after trimming during assignment. This is the full CASE_WHEN(...) expression.

Returns: String. Original formula text.

casewhenFormula #

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:

ParsingInfrastructure

CaseWhenTable #

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

ResetCache #

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.

SanitisedFormula #

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.

SplitFormulaSegments #

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:

Returns: BetterArray. Alternating condition and result expressions.

Used in (4 file(s))