ChoiceFormula

Turns a CHOICE_FORMULA custom function into a CASE_WHEN one and reads the result through a CaseWhen. The first argument is the name of a choice list, and the arguments after it are condition and result pairs. ChoiceSpecifications takes the formula in, ChoiceName answers the list name, Valid says whether the formula can be read, FailureReason names what stopped it, ParsedFormula gives the nested Excel IF text, and Categories lists the result labels.

Depends on: CaseWhen, BetterArray

Version: 1.0 (2026-02-09)

Factory

Create #

create

Instantiate a ChoiceFormula parser bound to a formula

Signature:

Public Function Create(ByVal formula As String) As ChoiceFormula

Trims the incoming formula, stores it in a new instance, and returns the ChoiceFormula for fluent downstream operations. The token check, the argument split and the choice name are all resolved at assignment, because the formula cannot change after Seal. The CASE_WHEN wrapper stays lazy: SetupErrors builds one of these per dictionary row and reads the choice name alone.

Parameters:

  • formula: String. Representation of a CHOICE_FORMULA call.

Returns: ChoiceFormula. A ready-to-query parser instance.

Validation

Valid #

valid

Determine whether the stored formula is a CHOICE_FORMULA with arguments

Signature:

Public Property Get Valid() As Boolean

Reads the answers computed when the formula was stored.

Requires the CHOICE_FORMULA token, balanced parentheses, and at least three arguments (name plus one condition/result pair). A blank argument is dropped by the splitter, so it lowers the argument count: writing CHOICE_FORMULA(mylist, , "Yes") leaves two arguments and fails the count.

Returns: Boolean. True when the specification can be converted to CASE_WHEN.

FailureReason #

failure-reason

Explain why the specification was rejected

Signature:

Public Property Get FailureReason() As String

Holds the message for the check that failed, and an empty string when the specification 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". CaseWhen and ValueOfFormula carry the same property.

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

Conversion

ParsedFormula #

parsed-formula

Convert CHOICE_FORMULA into nested IF statements through CASE_WHEN

Signature:

Public Function ParsedFormula() As String

Core conversion, category extraction, and choice name resolution.

Builds the equivalent CASE_WHEN expression from the parsed arguments, then delegates to the CaseWhen class to produce the nested IF output. Returns an empty string when the formula is invalid or conversion fails.

Returns: String. Expression ready for worksheet consumption.

Categories #

categories

Extract the result labels from the CHOICE_FORMULA results

Signature:

Public Function Categories() As BetterArray

Delegates to the CaseWhen converter to reuse category parsing logic, and hands back what it returns. CaseWhen builds a fresh array on every call, so no copy is made here. Returns an empty BetterArray when the formula is invalid or the CaseWhen wrapper cannot be created.

Returns: BetterArray. Collection of plain category labels.

ChoiceName #

choice-name

Retrieve the name of the choice list referenced by the formula

Signature:

Public Function ChoiceName() As String

Returns the first argument of the CHOICE_FORMULA expression, which identifies the choice list that provides the valid options. It is resolved at creation, and it answers even when the specification is invalid: a formula that names a list and stops still names a list, and SetupErrors reads the name to collect the lists a dictionary uses.

Returns: String. Choice list identifier.

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:

PublicAccessors

ChoiceSpecifications #

choice-specifications

Retrieve the stored CHOICE_FORMULA expression

Signature:

Public Property Get ChoiceSpecifications() As String

Properties that expose internal state.

Returns the original formula text as stored after trimming during assignment. This is the full CHOICE_FORMULA(...) expression. It is the read half of the pair the factory writes through.

Returns: String. Original formula specification.

ChoiceSpecifications #

choice-specifications-set

Store the CHOICE_FORMULA expression and parse it once

Signature:

Public Property Let ChoiceSpecifications(ByVal formula As String)

Trims the incoming formula and stores it, then runs the token check, the argument split and the choice-name resolution right here. Every public answer below reads a field afterwards. The old class ran the sanitiser up to four times and the validity check once per public call, over a string that cannot change after Seal.

Parameters:

PrivateHelpers

EvaluateFormula #

evaluate-formula

Check the token, split the arguments and resolve the choice name

Signature:

Private Sub EvaluateFormula()

Internal utilities for parsing and CASE_WHEN conversion.

Runs once, from the formula setter. Sets the argument body, the argument list, the choice name, the validity answer and the failure message.

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.

SplitArguments #

split-arguments

Split the argument body into individual arguments

Signature:

Private Function SplitArguments(ByVal body As String) As BetterArray

Walks the body character by character, tracking quotation state and parenthesis depth. Splits on top-level commas only. Blank segments are dropped, so a blank argument lowers the argument count rather than being stored. CaseWhen.SplitFormulaSegments 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.

Parameters:

Returns: BetterArray. The choice name followed by condition/result entries.

CaseWhenFormula #

case-when-formula

Build the equivalent CASE_WHEN formula from parsed arguments

Signature:

Private Function CaseWhenFormula() As String

Skips the first argument (choice list name) and joins the remaining condition/result entries into a CASE_WHEN(...) expression string. Returns an empty string when nothing follows the name. The join uses ", ". CaseWhen splits on a top-level comma and trims each segment, so the extra space is dropped again. The two have to keep agreeing on the separator.

Returns: String. CASE_WHEN expression or empty when there is nothing to build.

CaseWhenWrapper #

case-when-wrapper

Provide a lazily created CaseWhen instance

Signature:

Private Property Get CaseWhenWrapper() As CaseWhen

Builds the CASE_WHEN expression from parsed arguments and delegates to CaseWhen.Create to obtain the parser. The instance is cached and the same one is handed to every caller: CaseWhen is read-only after Seal, so sharing it is safe. Making CaseWhen mutable would break this.

Returns: CaseWhen. The converted formula parser.

ThrowError #

throw-error

Raise a project-specific error

Signature:

Private Sub ThrowError(ByVal errNumber As Long, ByVal message As String)

Wrapper around Err.Raise that standardises the source to the class constant CLASSNAME for consistent stack traces. The code is taken as a Long: a named enum type in a parameter position is the macOS trap.

Parameters:

Throws:

Used in (6 file(s))