ChoiceFormula
Converts CHOICE_FORMULA custom functions into CASE_WHEN representations and exposes helper metadata for downstream validation. The first argument of CHOICE_FORMULA is the choice list name; the remaining arguments form condition/result pairs that are delegated to a CaseWhen instance for nested IF conversion and category extraction.
Depends on: CaseWhen, BetterArray
Version: 1.0 (2026-02-09)
Factory
create #
Instantiate a ChoiceFormula parser bound to a formula
Signature:
Public Function Create(ByVal formula As String) As IChoiceFormula
Trims the incoming formula, stores it in a new instance, and returns the IChoiceFormula interface for fluent downstream operations. No eager parsing occurs; the formula is parsed lazily on first access.
Parameters:
formula: String. Representation of a CHOICE_FORMULA call.
Returns: IChoiceFormula. A ready-to-query parser instance.
Validation
valid #
Determine whether the stored formula is a CHOICE_FORMULA with arguments
Signature:
Private Property Get Valid() As Boolean
Checks whether the stored formula matches the CHOICE_FORMULA pattern. Requires the CHOICE_FORMULA token, balanced parentheses, and at least three arguments (name plus one condition/result pair). Returns False when any argument is blank.
Returns: Boolean. True when the specification can be converted to CASE_WHEN.
Conversion
parsed-formula #
Convert CHOICE_FORMULA into nested IF statements through CASE_WHEN
Signature:
Private 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 #
Extract textual labels from the CHOICE_FORMULA results
Signature:
Private Function Categories() As BetterArray
Delegates to the CaseWhen converter to reuse category parsing logic. Returns an empty BetterArray when the formula is invalid or the CaseWhen wrapper cannot be created.
Returns: BetterArray. Collection containing quoted category labels.
choice-name #
Retrieve the name of the choice list referenced by the formula
Signature:
Private Function choiceName() As String
Returns the first argument of the CHOICE_FORMULA expression, which identifies the choice list that provides the valid options. The value is memoised after the first resolution.
Returns: String. Choice list identifier.
Internal members (not exported)
PublicAccessors
self #
Current object instance
Signature:
Public Property Get Self() As IChoiceFormula
Properties that expose internal state and the interface pointer. Returns the current instance cast to its interface. Used internally by the factory method to return an IChoiceFormula reference.
Returns: IChoiceFormula. Reference to the current object.
choice-specifications #
Retrieve the stored CHOICE_FORMULA expression
Signature:
Public Property Get ChoiceSpecifications() As String
Returns the original formula text as stored after trimming during assignment. This is the full CHOICE_FORMULA(...) expression.
Returns: String. Original formula specification.
choice-specifications-set #
Store the CHOICE_FORMULA expression and reset caches
Signature:
Public Property Let ChoiceSpecifications(ByVal formula As String)
Trims the incoming formula and stores it, then invalidates all cached artefacts so subsequent reads trigger a fresh parse.
Parameters:
formula: String. Expression to parse.
PrivateHelpers
reset-caches #
Reset cached artefacts when the formula changes
Signature:
Private Sub ResetCaches()
Internal utilities for cache management, argument parsing, and CASE_WHEN conversion. Clears the argument cache, the CaseWhen wrapper, and the memoised choice name so the next access triggers a full reparse.
sanitised-formula #
Resolve the portion of the formula between parentheses
Signature:
Private Function SanitisedFormula() As String
Strips the CHOICE_FORMULA token, the opening parenthesis, and the closing parenthesis to expose the raw comma-delimited argument body. Returns an empty string when the formula does not match the expected pattern.
Returns: String. The comma-delimited arguments.
choice-arguments #
Split the sanitised formula into individual arguments
Signature:
Private Function ChoiceArguments() As BetterArray
Walks the sanitised body character by character, tracking quotation state and parenthesis depth. Splits on top-level commas only. The result is cached so subsequent calls avoid reparsing.
Returns: BetterArray. The choice name followed by condition/result entries.
contains-empty-argument #
Determine whether the arguments contain empty entries
Signature:
Private Function ContainsEmptyArgument(ByVal arguments As BetterArray) As Boolean
Scans the BetterArray for any blank entry. Returns True on the first empty string encountered.
Parameters:
arguments: BetterArray. List of parsed arguments.
Returns: Boolean. True when an argument is blank.
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 fewer than two arguments exist beyond the name.
Returns: String. CASE_WHEN expression or empty when invalid.
case-when-wrapper #
Provide a lazily created CaseWhen instance
Signature:
Private Property Get CaseWhenWrapper() As ICaseWhen
Builds the CASE_WHEN expression from parsed arguments and delegates to CaseWhen.Create to obtain the parser. The result is cached for subsequent accesses. Raises an error when conversion fails.
Returns: ICaseWhen. The converted formula parser.
Throws:
- ProjectError.ErrorUnexpectedState When conversion to CASE_WHEN fails.
resolved-choice-name #
Resolve and cache the choice list name
Signature:
Private Function ResolvedChoiceName() As String
Extracts the first argument from the parsed argument list, which is the choice list identifier. The value is memoised so subsequent calls avoid re-parsing.
Returns: String. Identifier extracted from the first argument.
throw-error #
Raise a project-specific error
Signature:
Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal message As String)
Wrapper around Err.Raise that standardises the source to the class constant CLASSNAME for consistent stack traces.
Parameters:
errNumber: ProjectError. The error code describing the failure.message: String. Error message to propagate.
Throws:
- ProjectError.
Always raises the specified error.
Interface
IChoiceFormula_parsedFormula #
Signature:
Private Property Get IChoiceFormula_parsedFormula() As String
Delegated members satisfying the IChoiceFormula contract.