CaseWhen

Turns a CASE_WHEN custom formula into nested Excel IF calls. The body of the formula is split into condition and result pairs, with quoted text and nested parentheses kept whole, and the pairs are folded into one nested IF expression. CaseWhenFormula takes the formula in, Valid says whether it can be read, FailureReason names what stopped it, ParsedFormula answers the Excel text, and Categories lists the plain result labels.

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. The header check runs once, at assignment. The argument table is parsed lazily on first access to 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

Reads the answers computed when the formula was stored.

True when the formula begins with the CASE_WHEN( token (case-insensitive) and carries content beyond the header. A bare "CASE_WHEN(" is rejected: it produces an empty body, and an empty parsed formula reaches the caller as "the formula is empty" for an expression the user did write.

Returns: Boolean. True when the formula is parseable.

FailureReason #

failure-reason

Explain why the formula was rejected

Signature:

Public Property Get FailureReason() As String

Holds the message for the check that failed, and an empty string when the formula is valid. ParsedFormula answers with an empty string for a bad expression, and an empty string on its own reads as "no formula was written". The caller reads this to tell the user what really happened. ValueOfFormula carries the same property.

Returns: String. The failure message, or an empty string.

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 and nothing here counts them.

Returns: String. Nested IF expression ready for worksheet consumption.

Categories #

categories

Extract the result labels from the CASE_WHEN arguments

Signature:

Public Function Categories() As BetterArray

Reads each result expression (even-indexed entries in the argument table) and returns the label as plain text, with no quote characters around it. That is the shape both consumers want: SetupErrors compares the labels against the choice sheet, which holds plain text, and EventSetup draws them as dropdown and chart labels.

Returns: BetterArray. Collection of plain 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.

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 check it once

Signature:

Public Property Let CaseWhenFormula(ByVal formula As String)

Trims the incoming formula, stores it, and runs the header check and the body extraction right here. Both answers are held, so Valid, ParsedFormula and Categories each read a field. The formula cannot change after Seal, so the answers cannot go stale.

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 body into alternating condition/result entries while respecting quoted segments and nested parentheses. The result is cached so later reads avoid reparsing the string. The cached array itself is handed back: both readers, ParsedFormula and Categories, only ask for Length and Item and neither writes to it.

Returns: BetterArray. The ordered CASE_WHEN arguments.

PrivateHelpers

EvaluateFormula #

evaluate-formula

Check the header and pull out the argument body

Signature:

Private Sub EvaluateFormula()

Internal utilities for validation, labels and segmentation.

Runs once, from the formula setter. Sets the validity answer, the failure message and the argument body, and drops any parsed table. The old code asked this question four times per use — from Valid, from CaseWhenTable, from ParsedFormula and from Categories — over a string that cannot change after Seal.

NewTable #

new-table

Build an empty one-based BetterArray

Signature:

Private Function NewTable() As BetterArray

Every array this class hands out or holds starts at index 1.

Returns: BetterArray. An empty array with LowerBound 1.

PlainLabel #

plain-label

Return one result expression as plain text

Signature:

Private Function PlainLabel(ByVal levelText As String) As String

A quoted result loses its outer pair of quotes, and a doubled inner quote becomes one quote — a result written "He said ""hi""" comes back as He said "hi". A result with no quotes around it is returned as written.

Parameters:

Returns: String. The label as the user will read it.

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. One trailing bracket is removed without checking the balance, so CASE_WHEN(a, b)) keeps one of them. The segment walker below survives that, so it is left alone.

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. Blank segments are dropped. ChoiceFormula.ChoiceArguments and ValueOfFormula.ExtractArguments hold the same walk; ValueOfFormula keeps its blank segments, because it counts them. The three stay separate: a class cannot reach a standard module, so a shared copy has nowhere to live. The walk reads one character at a time. A CASE_WHEN body is a short string and this runs once per instance, so the reading cost sits under the cost of the string joins around it.

Parameters:

Returns: BetterArray. Alternating condition and result expressions.

Used in (5 file(s))